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 <gmei_at_incyte.com> 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: gmei_at_incyte.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Sat Jan 10 2004 - 21:39:25 CST