Home » SQL & PL/SQL » SQL & PL/SQL » write to the file from PL SQL (oracle 11)
write to the file from PL SQL [message #676153] Fri, 17 May 2019 11:02 Go to next message
ykozhevnikov
Messages: 59
Registered: November 2008
Location: USA
Member
Hello , I have very short question.
there is procedure that takes long time to run. This procedure makes multiple inserts and commits during run.
Does it make any sense instead of inserting into table to open file from inside procedure and write to the file?
should it speed up execution ?

Thanks

[Updated on: Fri, 17 May 2019 11:04]

Report message to a moderator

Re: write to the file from PL SQL [message #676154 is a reply to message #676153] Fri, 17 May 2019 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: write to the file from PL SQL [message #676157 is a reply to message #676153] Fri, 17 May 2019 11:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you asking if it is possible to bypass the SQL interface and instead write directly to a disc based structure? If so, the answer is categorically NO. Ted Codd said, back in 1970, that a relational database cannot permit that.
Re: write to the file from PL SQL [message #676159 is a reply to message #676157] Fri, 17 May 2019 14:00 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Using the SYS.UTL_FILE package you can write flat files to the database server instead to a table, however it will not be faster then writing into an oracle table. Some questions.

Is this a mass load with no other activity during the load. How many rows are going in and how many rows in the table that is being inserted. Pure sql insert will always be faster then a procedure that is looping through a cursor.
How many indexes and triggers are on the table(s) being inserted?
Previous Topic: month as columns
Next Topic: Disable FND_FILE.PUT_LINE(FND_FILE.LOG
Goto Forum:
  


Current Time: Thu Mar 28 17:59:03 CDT 2024