Home » SQL & PL/SQL » SQL & PL/SQL » Bulk insert into file (Oracle 11g, Windows xp)
Bulk insert into file [message #572647] Fri, 14 December 2012 09:21 Go to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Hi,
We have requirement to create INSERT SCRIPT from the table having thousands of records and load that into flat file.
Please suggest, if there are any better option other than using UTL_FILE package which process record by record.

Thanks & Regards,
Lokesh
Re: Bulk insert into file [message #572648 is a reply to message #572647] Fri, 14 December 2012 09:31 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 440
Registered: July 2003
Location: WPB, FL
Senior Member
Code a program in 'C' language to use the 'bulk collect' functionality.

PS: How many "Thousands" are your thousands?

[Updated on: Fri, 14 December 2012 10:16] by Moderator

Report message to a moderator

Re: Bulk insert into file [message #572650 is a reply to message #572647] Fri, 14 December 2012 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html

Regards
Michel
Re: Bulk insert into file [message #572735 is a reply to message #572648] Mon, 17 December 2012 01:27 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks LKBrwn_DBA for your response.
There are around 25 tables in which around 15 tables are having more than 50 thousands records and remaining around 1.5 lacs.
It is not possible to use C program as these has to be executed in production server.

Regards,
Lokesh
Re: Bulk insert into file [message #572738 is a reply to message #572650] Mon, 17 December 2012 01:34 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Michel for the link.
The requirement is to automate the process and so I need insert scripts of 25 tables in one single file using programming.
Also can you please suggest whether UTL_FILE package is capable of writing around 5 lacs records into file (i.e limitation of UTL_FILE)

Regards,
Lokesh
Re: Bulk insert into file [message #572740 is a reply to message #572738] Mon, 17 December 2012 02:44 Go to previous message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no such limit with UTL_FILE.

Regards
Michel
Previous Topic: Please help me to write a SQL
Next Topic: regexp_replace with clob
Goto Forum:
  


Current Time: Fri Oct 24 05:22:32 CDT 2014

Total time taken to generate the page: 0.11780 seconds