RE: Copy long raw data from a remote database

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

-- Attached file included as plaintext by Ecartis --
-- File: ora_unload.pl

#!/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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: