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