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 <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).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.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).
>

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