Re: What is the fastest way to dump oracle data into a human
A quick and dirty hack to the existing PL/SQL code which typically adds good performance improvement is to fetch from your cursor in bulk collect (say 200-500 records at a time). Otherwise its Perl/C/etc/etc time. hth connor --- Tim Gorman [EMAIL PROTECTED] wrote: Options #1 (Perl) and #2 (PRO*C) would be fastest and easiest. The PRO*C demo programs provide a decent start, for option #2. Option #3 (OCI) would be not faster than PRO*C and, due to the increased complexity of OCI, a more problematic approach. SQL*Plus is the easiest method to implement by far, but the SQL*Plus program introduces a surprising amount of overhead processing, making it much slower than Perl or PRO*C, but much faster than UTL_FILE. If you are not in a big time crunch but want decent performance, use SQL*Plus. If you are in a time crunch and need the best performance, use Perl or PRO*C. on 1/10/04 6:09 PM, Guang Mei at [EMAIL PROTECTED] wrote: Hi: I have a program (running on oracle 8173 server) that writes 48 Millions lines of data into various text files . The selected data is from various tables and I have the query pretty much optimized. Now I am trying to find the fastest way to dump the selected data into a text file on the same oracle server. The program (written as a pl/sql package) now works something like this: str varchar2(32767) := ''; NL char(1) := chr(10); -- new line character begin fpn := utl_file.fopen(directory, filename, 'w', 32767); for x in cur1 loop str := str || x.str || NL; -- keep building the str if (length (str) 31000 ) then str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); str := ''; end if; end loop; -- dump the last part: str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); utl_file.fflush(fpn); utl_file.fclose(fpn); end ; The above code works perfect fine now. But I am wondering if there is another way that could increase the writing siginificantly faster. This porgram does not have to be in pl/sql. I can think of a couple of potential approaches: 1. Write a perl program, basically using perl's DBI/DBD to select the data from the database, then calling perl's print to write data into a file. I have not tested this and don't know if it is faster that utl_file.put_line. 2. Write a C program, using ProC to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. 3. Write a C program,using OCI to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. I don't have direct experience with ProC and OCI, so I don't know how faster (or any) it would be by doing Option 2 or 3 above. Does anyone know if I would see siginificant performance boost by using C? Any other suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is the fastest way to dump oracle data into a human
I also have one in Perl that I use to dump an entire schema to flat files, generating sqlldr parameter and control files as is goes. sqlunldr.pl is part of the PDBA toolkit: http://www.oreilly.com/catalog/oracleperl - click on 'Toolkit' Jared On Sat, 2004-01-10 at 19:39, Rachel Carmichael wrote: Jared has a utility to dump tables to flat files http://www.cybcon.com/~jkstill/util/ on the lefthand menu, under Utilities click on Dump Tables to Flat Files --- Guang Mei [EMAIL PROTECTED] wrote: Hi: I have a program (running on oracle 8173 server) that writes 48 Millions lines of data into various text files . The selected data is from various tables and I have the query pretty much optimized. Now I am trying to find the fastest way to dump the selected data into a text file on the same oracle server. The program (written as a pl/sql package) now works something like this: str varchar2(32767) := ''; NL char(1) := chr(10); -- new line character begin fpn := utl_file.fopen(directory, filename, 'w', 32767); for x in cur1 loop str := str || x.str || NL; -- keep building the str if (length (str) 31000 ) then str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); str := ''; end if; end loop; -- dump the last part: str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); utl_file.fflush(fpn); utl_file.fclose(fpn); end ; The above code works perfect fine now. But I am wondering if there is another way that could increase the writing siginificantly faster. This porgram does not have to be in pl/sql. I can think of a couple of potential approaches: 1. Write a perl program, basically using perl's DBI/DBD to select the data from the database, then calling perl's print to write data into a file. I have not tested this and don't know if it is faster that utl_file.put_line. 2. Write a C program, using ProC to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. 3. Write a C program,using OCI to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. I don't have direct experience with ProC and OCI, so I don't know how faster (or any) it would be by doing Option 2 or 3 above. Does anyone know if I would see siginificant performance boost by using C? Any other suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is the fastest way to dump oracle data into a human
Is there a place that we can see the code without installing the PDBA toolkit? I don't need to dump any table or schema, I just need to dump a selected query result (many many rows) into a text file. Do you just use perl's print to write data? Guang On Sun, 11 Jan 2004, Jared Still wrote: I also have one in Perl that I use to dump an entire schema to flat files, generating sqlldr parameter and control files as is goes. sqlunldr.pl is part of the PDBA toolkit: http://www.oreilly.com/catalog/oracleperl - click on 'Toolkit' Jared On Sat, 2004-01-10 at 19:39, Rachel Carmichael wrote: Jared has a utility to dump tables to flat files http://www.cybcon.com/~jkstill/util/ on the lefthand menu, under Utilities click on Dump Tables to Flat Files --- Guang Mei [EMAIL PROTECTED] wrote: Hi: I have a program (running on oracle 8173 server) that writes 48 Millions lines of data into various text files . The selected data is from various tables and I have the query pretty much optimized. Now I am trying to find the fastest way to dump the selected data into a text file on the same oracle server. The program (written as a pl/sql package) now works something like this: str varchar2(32767) := ''; NL char(1) := chr(10); -- new line character begin fpn := utl_file.fopen(directory, filename, 'w', 32767); for x in cur1 loop str := str || x.str || NL; -- keep building the str if (length (str) 31000 ) then str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); str := ''; end if; end loop; -- dump the last part: str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); utl_file.fflush(fpn); utl_file.fclose(fpn); end ; The above code works perfect fine now. But I am wondering if there is another way that could increase the writing siginificantly faster. This porgram does not have to be in pl/sql. I can think of a couple of potential approaches: 1. Write a perl program, basically using perl's DBI/DBD to select the data from the database, then calling perl's print to write data into a file. I have not tested this and don't know if it is faster that utl_file.put_line. 2. Write a C program, using ProC to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. 3. Write a C program,using OCI to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. I don't have direct experience with ProC and OCI, so I don't know how faster (or any) it would be by doing Option 2 or 3 above. Does anyone know if I would see siginificant performance boost by using C? Any other suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
What is the fastest way to dump oracle data into a human readable
Hi: I have a program (running on oracle 8173 server) that writes 48 Millions lines of data into various text files . The selected data is from various tables and I have the query pretty much optimized. Now I am trying to find the fastest way to dump the selected data into a text file on the same oracle server. The program (written as a pl/sql package) now works something like this: str varchar2(32767) := ''; NL char(1) := chr(10); -- new line character begin fpn := utl_file.fopen(directory, filename, 'w', 32767); for x in cur1 loop str := str || x.str || NL; -- keep building the str if (length (str) 31000 ) then str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); str := ''; end if; end loop; -- dump the last part: str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); utl_file.fflush(fpn); utl_file.fclose(fpn); end ; The above code works perfect fine now. But I am wondering if there is another way that could increase the writing siginificantly faster. This porgram does not have to be in pl/sql. I can think of a couple of potential approaches: 1. Write a perl program, basically using perl's DBI/DBD to select the data from the database, then calling perl's print to write data into a file. I have not tested this and don't know if it is faster that utl_file.put_line. 2. Write a C program, using ProC to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. 3. Write a C program,using OCI to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. I don't have direct experience with ProC and OCI, so I don't know how faster (or any) it would be by doing Option 2 or 3 above. Does anyone know if I would see siginificant performance boost by using C? Any other suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is the fastest way to dump oracle data into a human readable
Jared has a utility to dump tables to flat files http://www.cybcon.com/~jkstill/util/ on the lefthand menu, under Utilities click on Dump Tables to Flat Files --- Guang Mei [EMAIL PROTECTED] wrote: Hi: I have a program (running on oracle 8173 server) that writes 48 Millions lines of data into various text files . The selected data is from various tables and I have the query pretty much optimized. Now I am trying to find the fastest way to dump the selected data into a text file on the same oracle server. The program (written as a pl/sql package) now works something like this: str varchar2(32767) := ''; NL char(1) := chr(10); -- new line character begin fpn := utl_file.fopen(directory, filename, 'w', 32767); for x in cur1 loop str := str || x.str || NL; -- keep building the str if (length (str) 31000 ) then str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); str := ''; end if; end loop; -- dump the last part: str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); utl_file.fflush(fpn); utl_file.fclose(fpn); end ; The above code works perfect fine now. But I am wondering if there is another way that could increase the writing siginificantly faster. This porgram does not have to be in pl/sql. I can think of a couple of potential approaches: 1. Write a perl program, basically using perl's DBI/DBD to select the data from the database, then calling perl's print to write data into a file. I have not tested this and don't know if it is faster that utl_file.put_line. 2. Write a C program, using ProC to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. 3. Write a C program,using OCI to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. I don't have direct experience with ProC and OCI, so I don't know how faster (or any) it would be by doing Option 2 or 3 above. Does anyone know if I would see siginificant performance boost by using C? Any other suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is the fastest way to dump oracle data into a human
Options #1 (Perl) and #2 (PRO*C) would be fastest and easiest. The PRO*C demo programs provide a decent start, for option #2. Option #3 (OCI) would be not faster than PRO*C and, due to the increased complexity of OCI, a more problematic approach. SQL*Plus is the easiest method to implement by far, but the SQL*Plus program introduces a surprising amount of overhead processing, making it much slower than Perl or PRO*C, but much faster than UTL_FILE. If you are not in a big time crunch but want decent performance, use SQL*Plus. If you are in a time crunch and need the best performance, use Perl or PRO*C. on 1/10/04 6:09 PM, Guang Mei at [EMAIL PROTECTED] wrote: Hi: I have a program (running on oracle 8173 server) that writes 48 Millions lines of data into various text files . The selected data is from various tables and I have the query pretty much optimized. Now I am trying to find the fastest way to dump the selected data into a text file on the same oracle server. The program (written as a pl/sql package) now works something like this: str varchar2(32767) := ''; NL char(1) := chr(10); -- new line character begin fpn := utl_file.fopen(directory, filename, 'w', 32767); for x in cur1 loop str := str || x.str || NL; -- keep building the str if (length (str) 31000 ) then str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); str := ''; end if; end loop; -- dump the last part: str := substr (str,1, length(str) -1 ); utl_file.put_line(fpn, str); utl_file.fflush(fpn); utl_file.fclose(fpn); end ; The above code works perfect fine now. But I am wondering if there is another way that could increase the writing siginificantly faster. This porgram does not have to be in pl/sql. I can think of a couple of potential approaches: 1. Write a perl program, basically using perl's DBI/DBD to select the data from the database, then calling perl's print to write data into a file. I have not tested this and don't know if it is faster that utl_file.put_line. 2. Write a C program, using ProC to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. 3. Write a C program,using OCI to talk to DB, then use C's fopen and fwrite(?) to dump data into text file. I don't have direct experience with ProC and OCI, so I don't know how faster (or any) it would be by doing Option 2 or 3 above. Does anyone know if I would see siginificant performance boost by using C? Any other suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
