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).
