Re: exporting a table to a flat file in .csv format
Date: Tue, 26 Aug 2003 17:13:17 -0700
Message-ID: <wIS2b.65830$kP.46061_at_fed1read03>
Jon wrote:
[Quoted] [Quoted] > I am trying to dump data from a table to a flat file in .csv format. I have > read documentation for sqlplus utility but I have not seen any information > about this apart from defining specific format for columns. Do you know if > this is posible from sqplus?
For LARGE tables it's faster to use Perl's DBI/DBD interface; and not SQL*Plus
The author of the code below is Jared Still;
who also is the author of the book:
Perl for the Oracle DBA.
#!/home/oracle/perl/bin/perl
=head1 dunldr
unload data from an oracle database
use 'dunldr -help' for help on usage
jared still
10/24/2001
=cut
use warnings;
use FileHandle;
use DBI;
use strict;
use File::Path;
use IO::File;
use Data::Dumper;
use Getopt::Long;
our %optctl = ();
our %bincol = ();
our %hexcols = ();
unless (
Getopt::Long::GetOptions( \%optctl,
"database=s",
"username=s",
"password=s",
"owner=s",
"directory=s",
"dateformat=s",
"header!",
"schemadump!",
"longlen=i",
"rowlimit=i",
"table=s_at_",
"bincol=s" => \%bincol,
"sysdba!",
"sysoper!",
"z","h","help"
)
) { Usage(1); }
for my $table ( keys %bincol ) {
my _at_bincols = split(/\,/,$bincol{$table});
$hexcols{uc($table)} = \_at_bincols;
}
#print Dumper(\%optctl);
#print Dumper(\%hexcols);
#for my $hexdumpcol ( _at_{$hexcols{XML_DATA}} ) {
#print "hexdumpcol: $hexdumpcol\n";
#}
#exit;
our($db, $username, $password, $connectionMode);
$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }
Usage(1) unless $optctl{database};
Usage(1) unless $optctl{username};
Usage(1) unless $optctl{password};
Usage(1) unless $optctl{owner};
$optctl{longlen} = 65535 unless $optctl{longlen};
if ( $optctl{h} || $optctl{z} || $optctl{help} ) {
Usage(0);
}
if ( $optctl{schemadump} ) {
$optctl{table} = ['SCHEMADUMP'];
} else {
Usage(1) unless $optctl{table}; }
# default hdr to off
$optctl{header} ||= 0;
#if ( $optctl{bincol} ) {
#}
$username=$optctl{username};
$password = $optctl{password};
$db = $optctl{database};
# create the working directory
unless ( $optctl{directory} ) {
$optctl{directory} = qq{$optctl{owner}.dump};
}
# create directory path if it doesn't exist -d $optctl{directory} || File::Path::mkpath([$optctl{directory}]);
our $dbh = DBI->connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError => 1,
AutoCommit => 0,
ora_session_mode => $connectionMode
}
);
die "Connect to $db failed \n" unless $dbh;
$dbh->{LongReadLen} = $optctl{longlen};
# set Oracle NLS date format
if ( $optctl{dateformat} ) {
$dbh->do(qq{alter session set nls_date_format
= '$optctl{dateformat}'} );
}
my $tableHash = new Tables($dbh, \%optctl);
#print "tables: ", join(':', keys %{$tableHash}), "\n"; #for my $table ( keys %{$tableHash} ){
#print "TABLE: $table FILE: $tableHash->{$table}\n"; #}
# print console info immediately
autoflush STDOUT 1;
my $sth;
# take a dump
for my $table ( keys %{$tableHash} ){
print "Table: $table\n";
my $sql = qq{select * from $optctl{owner}\.$table};
if ( $optctl{rowlimit}){
$sql .= qq{ where rownum <= $optctl{rowlimit}};
}
$sth = $dbh->prepare($sql);
my _at_columns = @{$sth->{NAME_uc}};
my %colOrder = ();
for my $el ( 0 ..$#columns ) {
$colOrder{$columns[$el]} = $el;
}
my $dumpFile = $optctl{directory} . '/' . $tableHash->{$table};
open(DUMP, "+> $dumpFile") || die "could not create file $dumpFile
- $!\n";
if ( $optctl{header} ) {
print DUMP join(',',_at_columns),"\n";
}
$sth->execute;
# create the ctl and par files
Tables->createCtl(
TABLE => $table,
COLUMNS => \_at_columns,
DUMPFILE => $tableHash->{$table},
DIRECTORY => $optctl{directory},
SCHEMA => $optctl{owner},
HEXCOLS => \_at_{$hexcols{$table}},
COLORDER => \%colOrder
);
# turn warnings off here so that warnings are not
# reported for null columns when printed
# comment it out to see what I mean
no warnings;
while ( my $ary = $sth->fetchrow_arrayref ) {
# change column to hex if specified as binary via -bincol
arg
if ( exists $hexcols{$table} ) {
for my $hexdumpcol ( _at_{$hexcols{$table}} ) {
$ary->[$colOrder{uc($hexdumpcol)}] =
uc(unpack("H*",$ary->[$colOrder{uc($hexdumpcol)}]));
}
}
print DUMP q{"} . join(q{","},_at_{$ary}) . qq{"\n};
#print "ROW: " . q{'} . join(q{','},_at_{$ary}) . qq{'\n};
}
use warnings;
close DUMP;
}
$sth->finish;
$dbh->disconnect;
sub Usage {
my ($exitCode) = _at__;
print q{
dunldr - data unloader for Oracle
usage:
dunldr -database <database> -username <userid> -password <password> \
-directory <data unload directory> \
-header|noheader \
-owner <schema owner> \
-table <table1,table2,table3,...)
-database database name
-username user to login as
-password password for login user
-owner owner of tables to dump
-directory directory to unload data into
will default to <owner>.dump
-dateformat Oracle NLS date format - optional
-header|noheader should first line include column names?
-table table to dump. may be repeated as many
times as necessary.
-schemadump dump entire schema of <owner>
will ignore -table settings
-rowlimit limit number of rows returned
-longlen if longs are in the table, set this
to the maximum length you want.
defaults to 65535
-bincol use to specify columns that should be dumped
in hex format. columns with binary data tend
to cause problems in text dumps.
e.g. -bincol <table_name>=<column_name,column_name,...>
dunldr -database orcl -username system -password manager \
-owner scott -directory scott.tables \
-header \
-table emp \
-table dept \
-table sales
dunldr -database orcl -username system -password manager \
-owner scott \
-dateformat 'mm/dd/yyyy' \
-header \
-schemadump \
-bincol xml_data=payload,header,authorization \
-bincol app_notes=text
};
exit $exitCode ? $exitCode : 0; }
package Tables;
sub new {
my $pkg = shift;
my $class = ref($pkg) || $pkg;
my ( $dbh, $optionHash ) = _at__;
my $tableHash;
if ( grep(/^SCHEMADUMP$/, _at_{$optionHash->{table}} ) ) {
# get all tables of owner
my $sql = q{
select table_name
from all_tables
where owner = ?
};
my $sth = $dbh->prepare($sql);
$sth->execute(uc($optionHash->{owner}));
my _at_tableArray;
while( my $ary = $sth->fetchrow_arrayref ) {
push(_at_tableArray, $ary->[0]);
}
$tableHash = setTables(\_at_tableArray);
} else {
$tableHash = setTables(\_at_{$optionHash->{table}});
}
bless $tableHash, $class;
return $tableHash;
}
=head1 setTables
make a neat hash of the form TABLE_NAME => 'table_name.dump' all table names upper case, all file names lower case for dump file names - Perl is awesome
=cut
sub setTables {
my ($tableArray) = shift;
my %tables = map(
split(/:/, $_),
map(
$_.':'.lc($_).'.txt',
split(
/:/,
uc(join(':',_at_{$tableArray}))
)
)
);
# uncomment these lines to see it
#use Data::Dumper;
#print Dumper(\%tables);
#exit;
my $hashRef = \%tables;
return $hashRef;
}
sub createCtl {
my($self,%args) = _at__;
my _at_columns = @{$args{COLUMNS}};
my %colOrder = %{$args{COLORDER}};
if ( $args{HEXCOLS} ) {
for my $hexdumpcol ( _at_{$args{HEXCOLS}} ) {
$columns[$colOrder{uc($hexdumpcol)}] =
$columns[$colOrder{uc($hexdumpcol)}] .
qq{ "hex_to_raw(:$columns[$colOrder{uc
($hexdumpcol)}])"};
}
}
my $ctlFile = $args{DIRECTORY}. '/' . lc($args{TABLE}) . '.ctl';
my $ctlFh = new IO::File();
$ctlFh->open("> $ctlFile") || die "cannot create file $ctlFile - $!
\n";
$ctlFh->print("load data\n");
$ctlFh->print("infile '$args{DUMPFILE}'\n");
$ctlFh->print("into table $args{TABLE}\n");
$ctlFh->print(q{fields terminated by ',' optionally enclosed by
'"'}. "\n");
$ctlFh->print("(\n");
$ctlFh->print( "\t" . join(",\n\t",_at_columns) . "\n");
$ctlFh->print(")\n");
$ctlFh->close;
my $parFile = $args{DIRECTORY}. '/' . lc($args{TABLE}) . '.par';
my $parFh = new IO::File();
$parFh->open("> $parFile") || die "cannot create file $parFile - $!
\n";
$parFh->print("userid = $args{SCHEMA}\n");
$parFh->print("control = " . lc($args{TABLE}) . ".ctl\n");
$parFh->print("log = " . lc($args{TABLE}) . ".log\n");
$parFh->print("bad = " . lc($args{TABLE}) . ".bad\n");
$parFh->close;
} Received on Wed Aug 27 2003 - 02:13:17 CEST