I believe the scheduled date is August.
Of this year. :)
Jared
"Kimberly Smith" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
04/25/2002 07:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Is sqlplus too slow to unload data?
Hey Jared,
I got quite a few folks waiting for your book now. I am really pushing
Perl at the office:-) When is the scheduled release again?
-----Original Message-----
Sent: Thursday, April 25, 2002 12:03 AM
To: Multiple recipients of list ORACLE-L
On Tuesday 23 April 2002 21:53, Bin Wang wrote:
> Hi,
> Our application uses sqlplus + sqlloader to transfer data between
> databases. It takes nearly four hours to unload to data to flat
> files(1G), which is far too slow. In the application, the query looks
> like the following. All those &3,&4,&5 are for sqlldr format.
How about using Perl?
Below is a script I just used to unload a table. Not very big, but this
is just from one of my test databases at home on a Linux box.
It unloaded about 12,000 rows in less than 2 seconds. This include
writing them to a file. This script creates a file of <TABLENAME>.dmp.
$> time ul.pl -database ts01 -username orades -password orades \
-table 'I$RM_PROPERTY_MAPS'
1.22s real 1.07s user 0.04s system
Doing the same thing with SQL*plus took 4.46 seconds.
You must have DBI and DBD::Oracle installed to use this.
Jared
====================================================
#!/usr/bin/perl
# ul.pl - unload a table
use warnings;
use FileHandle;
use DBI;
use strict;
use Getopt::Long;
my %optctl = ();
Getopt::Long::GetOptions(
\%optctl,
"database=s",
"username=s",
"password=s",
"table=s",
"sysdba!",
"sysoper!",
"z","h","help");
#setup environment - homegrown package
my($db, $username, $password, $connectionMode);
$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }
if ( ! defined($optctl{database}) ) {
Usage();
die "database required\n";
}
$db=$optctl{database};
if ( ! defined($optctl{username}) ) {
Usage();
die "username required\n";
}
if ( ! defined($optctl{table}) ) {
Usage();
die "table required\n";
}
$username=$optctl{username};
$password = $optctl{password};
my $dbh = DBI->connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError => 1,
AutoCommit => 0,
ora_session_mode => $connectionMode
}
);
die "Connect to $db failed \n" unless $dbh;
# time and adjust row cache size
$dbh->{RowCacheSize} = 5000;
my $MySql="select * from $optctl{table}";
my $sth = $dbh->prepare($MySql);
$sth->execute;
open(OUT,">$optctl{table}.dmp") || die "cannot create $optctl{table}.dmp -
$!\n";
my $delimiter = '~';
no warnings; # don't raise warnings on null columns
while( my $ary = $sth->fetchrow_arrayref ) {
print OUT join($delimiter,@{$ary}), "\n";
}
use warnings;
$sth->finish;
$dbh->disconnect;
sub Usage {
print "\n";
print "usage: ul.pl\n";
print " ul.pl -database dv07 -username scott -password
tiger -table emp
[-sysdba || -sysoper]\n";
print "\n";
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kimberly Smith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
