SQL & PL/SQL

Hi.

I'm trying to unload a fair amount of table data via PL/SQL. Historically, a Pro*C routine, or even SQL*Plus SPOOL would have been the way to go. With my arm twisted, I gave UTL_FILE a chance and it's performing quite well - better than Spooling, and about the same as a hastily put together Perl DBI export.

Does anyone have any recent experience of exporting (ascii data, not expdp) from Oracle, and any advice on the best method? I'd like to avoid an external routine like Pro*C unless the performance gain is compelling.

Did UTL_FILE magically get better at some point?!



My test cases: both exported 2.6 million user_source rows (~178mb) in approx the same amount of time (20 secs or so on this env).

1. UTL_FILE

declare
lFile utl_file.file_type;
lBuff varchar2(32767);
begin
lFile := Utl_File.fOpen(Location => 'EXP_DIR'
,FileName => 'exptest_utlfile.dat'
,Open_Mode => 'w'
,Max_LineSize => 32767
);
for r1 in
(select name||chr(9)||type||chr(9)||line||chr(9)||text myData
from user_source)
loop
if length(lBuff) + length(r1.myData+) <= 32765 then
lBuff := lBuff || chr(10) || r1.mydata;
else
if lBuff is not null then
Utl_File.Put_Line(File => lFile, Buffer => lBuff);
end if;
lBuff := r1.myData;
end if;
end loop;
Utl_File.Put_Line(File => lFile, Buffer => lBuff);
Utl_File.fClose(lFile);
end;


2. Perl DBI

use DBI;
my $dbh = DBI->connect("DBI:Oracle:dbname","user","pwd") || die $DBI::errstr;

my $sth = $dbh->prepare('select name||chr(9)||type||chr(9)||line||chr(9)||text myData
from user_source');
$sth->execute;
open(my $outputFile, '>', 'exp/exptest_perl.dat');

while (my $rowS = $sth->fetchall_arrayref(undef, 10000) ) {
foreach my $row ( @$rowS ) {
print $outputFile $row->[0] . "\n";
}
}
close $outputFile;
$sth->finish;
$dbh->disconnect();