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: Copy long raw data from a remote database

RE: Copy long raw data from a remote database

From: Shawn Ferris <shawn_at_virtualsmf.net>
Date: Tue, 18 May 2004 14:47:05 -0600 (MDT)
Message-ID: <38811.168.215.22.23.1084913225.squirrel@mail.virtualsmf.net> 





Here ya go.. I'll likely send out a new version too.. This isn't
documented at all. I'll provide pod style docs tonight/ tomorrow.
The command line options are:



  -dat=filename      The output data filename

  -ctl=filename      The output control filename




  -table=tablename   The table to unload

    or


  -sql='select stmt' The select stmt to unload




  -[no]genctl        Generate the control file [or not] (-nogenctl)

  -[no]unload        Unload the data [or not] (-unload)




  -progress=#        Number of rows to report progress on (10)




  -output del='str'  The string to use as the field delimiter (,)

  -output bof='str'  The string to use for beginning of a field (<bof>)

  -output eof='str'  The string to use for the end of a field (<eof>)

  -output eor='str'  The string to use for the end of a record (<eorecord>)




  -from database='SID'   The Oracle sid to connect to

  -from username='USER'  The Username to connect as ($ORAUSER)

  -from password='PASS'  The Password for the user ($ORAPASS)

  -from LongReadLen=#    The size of the LONG buffer (1M)




I pretty much re-wrote it to clean it up.. It works for me: Your Milage
May Vary!



Of course, I take no responsibility for it, or you, trashing your system.
I have disabled the '-sql' option because it has no tainted stmt checks..
You could pass it ANY SQL statement you care to, and it will try to
execute it!!!! (Including 'delete * from seg$')



YOU'VE BEEN WARNED!


Enabling the -sql feature is up to the reader. You can also create a view
and specify the view name in place of the table name for the same affect.



By default, this will log into the database using an OPS$ acct.. or you
can specify the user/pass explicitly. It should work fine over sql*net by
specifing the database name.



Hope it helps!



Shawn






#!/usr/bin/env perl



use DBI;



use Getopt::Long;


use Data::Dumper;



use strict;



$|=1;



my $starttime=time();


my($version)=sprintf("%s", q$Revision: 0.1$ =~ /([\d\.]+)/);


#
## Configuration data (assign defaults)
#




my($cfg)={};



$cfg={


  # Command line options

  'verbose'    => 3,
  'genctl'     => 0,
  'unload'     => 1,
  'progress'   => 10000,

  'sql'        => undef,
  'table'      => undef,




  'dat_file'   => undef,


  'ctl_file'   => undef,


  # Sample output:
  #   (This should handle embedded quotes and newlines, etc)
  #
  #   <bof>Row1<eof>,<bof>Field2<eof>,<eorecord>
  #   <bof>Row2<eof>,<bof>Field2<eof>,

  # Output format
  'output'     => {
     'del'        => ",",
     'bof'        => "<bof>",
     'eof'        => "<eof>",
     'eor'        => "<eorecord>",




  },



  # DB Options


  'from'       => {

    'database'    => '',
    'username'    => $ENV{ORAUSER},
    'password'    => $ENV{ORAPASS},
    'LongReadLen' => 1048576, # 1MB




  },


};


#
## Parse the commandline options
#




Getopt::Long::Configure("pass_through");
Getopt::Long::Configure("noauto_abbrev");
GetOptions(


  $cfg,



  'genctl!',


  'unload!',



  'verbose=s',


  'progress=s',



  'table|t=s',


#  'sql|s=s',



  'dat_file|dat=s',


  'ctl_file|ctl=s',


);



# Check arguments for completeness


if ( ! $cfg->{dat_file} ) {


  die "No output file specified: use -dat option\n";
} elsif ( $cfg->{genctl} && ! $cfg->{ctl_file} ) {
  warn "No control file specified: use -ctl option\n";
  $cfg->{genctl} = 0;


} elsif ( ! $cfg->{sql} && ! $cfg->{table} ) {
  die "No source specified: use -table or -sql option\n";
} elsif ( $cfg->{sql} && $cfg->{table} ) {
  die "Only one source allowed: remove -sql or -table option\n";
}


#
## Modify section options via command line
## EG: 
##   -from database=ORACLE_SID
##   -from username=USER
##   -from password=PASS
##
##   -output del=","
##   -output bof="<bof>"
##   -output eof="<eof>"
##   -output eor="<eorecord>"
#




my @sections;


while (my($key,$val)=each %$cfg) {


  next unless (ref $val eq 'HASH' );


  push(@sections,$key);


}



Getopt::Long::Configure("no_pass_through");
GetOptions(


  $cfg,


  map(


   sprintf("%s=s%%",$_),


   @sections,


  ),


);


#
## Make a database connection
#




my $dsn=


  sprintf('dbi:Oracle:%s',$cfg->{from}->{database});




my $dbh=DBI->connect(


  $dsn,



  $cfg->{from}->{username},


  $cfg->{from}->{password},



  {


    LongReadLen => $cfg->{from}->{LongReadLen},
  }



) or die $DBI::errstr;



# Set the default date format to include time
$dbh->do("alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'");


#
## Built in sub routines
#





# Determine the elapsed time


sub elapsed {


  my($start,$end)=(@_,time);


  return sprintf(


    "%2.2d:%2.2d:%2.2d",

    (($end - $start) / 60) / 60,
    (($end - $start) / 60) % 60,
    (($end - $start) % 60),




  );


}



sub unload {


  my(%opts)=@_;



  my(

    $dbh,
    $table,
    $sql,
    $ofn,
    $ctlfn,

    $del,
    $bof,




    $eof,


    $eor,



    $table_start,


  ) = (


    @opts{

      'database',
      'table',
      'sql',
      'dat_file',
      'control_file',




    },
  

    $cfg->{output}->{del},
    $cfg->{output}->{bof},
    $cfg->{output}->{eof},
    $cfg->{output}->{eor},





    time(),


  );



  $sql=sprintf(


    'select * from %s',


    $table,


  ) unless $sql;



  my($csr);



  PREPARE: {



    last if $csr=$dbh->prepare($sql);


    warn sprintf(

      "Unable to prepare stmt: %s: %s\n",
      $sql,
      $dbh->errstr,




    );


    return undef;


  }



  EXECUTE: {



    last if $csr->execute();


    warn sprintf(

      "Unable to execute stmt: %s: %s\n",
      $sql,
      $dbh->errstr,




    );


    return undef;


  }



  # Get column labels


  my @names=@{$csr->{NAME}};



  # Format string


  my $fmt=join($del,map("${bof}%s${eof}",@names),undef);



  GENCTL: {



    last GENCTL unless $cfg->{genctl};



    print "Writing control file: $ctlfn\n"
     if $cfg->{verbose} & 1;



    if ( ! open(CONTROL,sprintf(">%s",$ctlfn)) ) {

     warn "Unable to write control file: $!\n";
     last GENCTL;




    }



    printf CONTROL (

      "options (direct=true,errors=1000)\n".
      "unrecoverable load data\n".
      "infile '%s' \"str '%s'\"\n".
      "into table %s truncate\n".
      "fields terminated by '%s' optionally enclosed by '%s' and '%s'\n".
      "trailing nullcols\n".
      "(\n".
      "   %s\n".
      ")",

      $ofn,
      $eor,

      'SCHEMA.TABLE_NAME',

      $del,
      $bof,
      $eof,

      join("\n  ,",@names),





    );



    close CONTROL;


  }



  UNLOAD: {



    last UNLOAD unless $cfg->{unload};



    my $rec_count=0;



    printf(

      "Writing to data file: %s\n",
      $ofn




    ) if ($cfg->{verbose} & 1);



    if ( ! open(OUTPUT,sprintf(">%s",$ofn)) ) {

     warn "Unable to write data file: $!\n";
     last UNLOAD;




    }



    while(my @row=$csr->fetchrow_array) {


      print OUTPUT "${eor}\n" if $rec_count > 0;
      printf OUTPUT ("${fmt}",@row);
  
      $rec_count++;

      printf("  %s records unloaded\n",$rec_count)
        if (
             $cfg->{verbose} & 2 &&
             $rec_count % $cfg->{progress} == 0
           );




    }



    close OUTPUT;



    printf(

      "Data unloaded in: %s\n",
      elapsed($table_start),




    ) if ($cfg->{verbose} & 1);


  }



  $csr->finish;


}


#
## Unload the data
#

unload(
  database     => $dbh,
  table        => $cfg->{table},
  dat_file     => $cfg->{dat_file},




  control_file => $cfg->{ctl_file},


) if $cfg->{table};



unload(

  database     => $dbh,
  sql          => $cfg->{sql},
  dat_file     => $cfg->{dat_file},
  control_file => $cfg->{ctl_file},




) if $cfg->{sql};



$dbh->disconnect;





Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Tue May 18 2004 - 15:35:48 CDT












Original text of this message













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