Re:fetch entire table

[charlie cs]

The following perl script will dump entire table to a
flat file, much faster than sqlplus spool.

Please test it in your case and let me know your
result.

Thanks

Charlie cs

#!/usr/bin/perl

 use strict;
 use DBI;
 use DBD::Oracle qw(:ora_types);

my $instance = ('dev');
my $username = ('test');
my $passwd = ('test');
my $dbh;
#declare variable for DBI use
$dbh = DBI->connect ( "dbi:Oracle:$instance",
                        "$username",
                        "$passwd",
                        {
                                PrintError => 1,
                                RaiseError => 1,
                                AutoCommit => 1
                        }
                        ) || die "Database Connection
not made", $dbh->errstr;

$dbh->{LongReadLen} = 80000;
$dbh->{LongTruncOk} = 1;

my $startAll = time;
my $row;
my @tables;
my $sth = $dbh->prepare("select table_name from
user_tables");
$sth->execute();
while($row = $sth->fetchrow_array){
  @tables = (@tables,$row);
}
 my $startTable;

 LINE: foreach my $table (@tables){
 $startTable = time;
  $sth = $dbh->prepare("select * from $table");
 $sth->execute();
 my $s;
 open TABLE, ">>$table.txt";
 while($row = $sth->fetchrow_arrayref){
 print TABLE join "\t", (map {($s = $_) =~
s/["\\]/\\$&/g;
 qq("$s")} @$row), "\n";
 }
 close TABLE;
 $sth->finish();
 print $table, " took " , (time-$startTable),  "
seconds\n";
 }
 print "total time : ", (time-$startAll), "
seconds\n";



                
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail