At 8:29 -0500 26/4/01, Randy Norwood wrote:
> >From what I've been able to find on this list's archives,
>DBD:Oracle doesn't support this capability, or maybe it's that
>Oracle sqlload is an external utility.
>
>Can someone shed some light on this, and suggest options for speedy
>loading of data via DBI into Oracle?
SQL*Loader is very very fast. In direct mode, it is far faster than
you could ever achieve with iterative inserts even if you could array
insert.
I have used perl to 'feed' a sqlldr process, and perhaps you could
adapt that to extract rows from mysql and feed them to a named pipe
for sqlldr, which will happily read a named pipe but ignored all
STDIN.
part of an example (we were loading dummy data, but the idea is there):
if (! -p $pipename ) { system("mknod $pipename p") }
if ( $pid = fork ) {
$result = system("sqlldr $username/$password control=$control
$options" );
} else {
die "Can't fork\n" unless defined $pid;
open ( OUTPIPE, ">$pipename" ) or die "Can't open $pipename
for writing\n";
while ( $n++ < 5810041 ) {
print OUTPIPE
'"',$n,'","aaaaaaaaaaaaaaaaaaaaaa","12042001","01012001","12","34","Why
am I doing this?"',"\n";
}
exit;
}
wait;
The sqlldr control file looks like this:
load data
INFILE lam.pipe
append into table multi_contig
fields terminated by ',' optionally enclosed by '"'
(c1,c2,c3 DATE(8) "DDMMYYYY" ,c4 DATE(8) "DDMMYYYY" ,c5,c6,c7 )
I hope this solution helps.
Regards
Paul Miller
--
-
Carib Data Limited
<mailto:[EMAIL PROTECTED]>
<http://www.caribdata.co.uk>
