Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query results to .csv

RE: Query results to .csv

From: <Jared.Still_at_radisys.com>
Date: Mon, 25 Aug 2003 09:59:40 -0800
Message-ID: <F001.005CD0D4.20030825095940@fatcity.com> 





Here's a Perl script that is quite a bit faster than SQLPLUS.



Jared



#!/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@",
                "bincol=s" => \%bincol,
                "sysdba!",
                "sysoper!",
                "z","h","help"
        )




) { Usage(1); }



for my $table ( keys %bincol ) {

        my @bincols = split(/\,/,$bincol{$table});


        $hexcols{uc($table)} = \@bincols;



}


#print Dumper(\%optctl);
#print Dumper(\%hexcols);
#for my $hexdumpcol ( @{$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 @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(',',@columns),"\n";
        }



        $sth->execute;


        # create the ctl and par files
        Tables->createCtl(
                TABLE => $table,
                COLUMNS => \@columns,
                DUMPFILE => $tableHash->{$table},
                DIRECTORY => $optctl{directory},
                SCHEMA => $optctl{owner},
                HEXCOLS => \@{$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 ( @{$hexcols{$table}} ) {
                                $ary->[$colOrder{uc($hexdumpcol)}] = 
uc(unpack("H*",$ary->[$colOrder{uc($hexdumpcol)}]));
                        }
                }
                print DUMP q{"} . join(q{","},@{$ary}) . qq{"\n};
                #print "ROW: " . q{'} . join(q{','},@{$ary}) . qq{'\n};
        }
        use warnings;
        close DUMP;




}




$sth->finish;


$dbh->disconnect;



sub Usage {



        my ($exitCode) = @_;



        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 ) = @_;

        my $tableHash;
        if ( grep(/^SCHEMADUMP$/, @{$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 @tableArray;
                while( my $ary = $sth->fetchrow_arrayref ) {
                        push(@tableArray, $ary->[0]);
                }
                $tableHash = setTables(\@tableArray);
        } else {
                $tableHash = setTables(\@{$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(':',@{$tableArray}))
                        )
                )
        );

        # uncomment these lines to see it
        #use Data::Dumper;
        #print Dumper(\%tables);
        #exit;

        my $hashRef = \%tables;
        return $hashRef;




}




sub createCtl {


        my($self,%args) = @_;


        my @columns = @{$args{COLUMNS}};
        my %colOrder = %{$args{COLORDER}};

        if ( $args{HEXCOLS} ) {
                for my $hexdumpcol ( @{$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",@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;






}






DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Sent by: ml-errors_at_fatcity.com


 08/25/2003 09:24 AM


 Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Query results to .csv






Jared - Thanks for posting this. At the moment, we are preparing to move
large database to a new server. Based on the advice you posted several
months ago, we have been testing SQL*Loader and as you predicted, it is
indeed fast. But also as you predicted, using SQL*Plus to create a CSV 
isn't


very fast. Am I correct in assuming the dump.sql will not be the best 
choice


for large tables? We are installing perl since you mentioned that would
probably be much faster.



Dennis Williams


DBA, 80%OCP, 100% DBA



Lifetouch, Inc.


dwilliams_at_lifetouch.com 




-----Original Message-----


Sent: Monday, August 25, 2003 9:40 AM


To: Multiple recipients of list ORACLE-L




http://www.cybcon.com/~jkstill/util/dump/dump.html






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).


Received on Mon Aug 25 2003 - 12:59:40 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US