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