Home » SQL & PL/SQL » SQL & PL/SQL » How to do Bulk copy in PL/SQL
How to do Bulk copy in PL/SQL [message #273942] Fri, 12 October 2007 05:32 Go to next message
manoop
Messages: 8
Registered: October 2007
Junior Member
Hi Friends


I want to do bulk copy into a text file.
How can i achieve this in PL/SQL?

Thanks in advance.........


Regards

Manoop
Re: How to do Bulk copy in PL/SQL [message #273944 is a reply to message #273942] Fri, 12 October 2007 05:41 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

search for UTL_FILE.


regards,
Re: How to do Bulk copy in PL/SQL [message #273947 is a reply to message #273942] Fri, 12 October 2007 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why not in SQL and use spool?

Regards
Michel
Re: How to do Bulk copy in PL/SQL [message #273952 is a reply to message #273947] Fri, 12 October 2007 06:00 Go to previous messageGo to next message
manoop
Messages: 8
Registered: October 2007
Junior Member
Hi

We are using oracle10g as back end and we need to copy a set of records matching a criteria to a text file.


Regards

Manoop
Re: How to do Bulk copy in PL/SQL [message #273953 is a reply to message #273952] Fri, 12 October 2007 06:04 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

yes.you can use spool.just happend to miss out on spool.

Quote:

We are using oracle10g as back end and we need to copy a set of records matching a criteria to a text file.


spool d:\file_name.txt
select col_name .... where_condition
/
spool off


regards,
Re: How to do Bulk copy in PL/SQL [message #273955 is a reply to message #273953] Fri, 12 October 2007 06:18 Go to previous messageGo to next message
manoop
Messages: 8
Registered: October 2007
Junior Member
Thanks a lot

We are using a Delphi application and from that the query need to be executed and the values need to be in comma seperated form.


Regards
Manoop
Re: How to do Bulk copy in PL/SQL [message #273988 is a reply to message #273955] Fri, 12 October 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So why don't create the file with your Delphi program?
You are able to query the table and to write the data in a file, aren't you?

Regards
Michel
Re: How to do Bulk copy in PL/SQL [message #274237 is a reply to message #273988] Mon, 15 October 2007 03:19 Go to previous messageGo to next message
manoop
Messages: 8
Registered: October 2007
Junior Member
Yeah we are doing like that only
Reading row by row and writing in the text file.
Its taking long time.
Re: How to do Bulk copy in PL/SQL [message #274240 is a reply to message #274237] Mon, 15 October 2007 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't fetch row per row, fetch array per array.
Of course, this depends if you want the result file on server or client.

Regards
Michel

[Updated on: Mon, 15 October 2007 03:24]

Report message to a moderator

Re: How to do Bulk copy in PL/SQL [message #274344 is a reply to message #273942] Mon, 15 October 2007 12:59 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
as Michel is alluding to, most tools are tunable in this regard.

For example: Sqlplus lets you set the number of rows fetched via "set arraysize ?". Sqlplus is just a tool fetching rows. Your Delphi is just a tool fetching rows. Look for the tunable settings that you can make in Delphi to determine number of rows to fetch and number of rows to write. You will be looking most likely for one of two things in each case: a number of rows, or a buffer cache size which coupled with an expected maximum rowlength computes to a number or rows.

Find the paramaters and set them and watch it go fast.

Good luck, Kevin
Previous Topic: projected value calculation
Next Topic: Declaration of an associative array
Goto Forum:
  


Current Time: Sat Feb 08 14:21:25 CST 2025