NAME

Oracle::Sqlldr - Perl wrapper around Oracle's sqlldr utility.

SYNOPSIS

  use Oracle::Sqlldr;
  my $sqlldr = Oracle::Sqlldr->new(); # get new sqlldr object

DESCRIPTION

Oracle::Sqlldr is an object-oriented class that provides a convenient Perl wrapper around Oracle's sqlldr utility.

SQL*Loader (sqlldr) is the utility to use for high performance data loading from a text file into a an Oracle database.

LIMITATIONS

No WIN32 support

No fixed format record support

Assumes table owner and user to load data as are the same

No support for parameter file

CAUTION

Whilst you are calling the method execute(), Oracle::Sqlldr is calling sqlldr and displaying your user/pass to the world, or at least readable within `ps -deaf`.

PERFORMANCE

Bulk uploads will be faster if indexes are disabled and built after loading.

Disable Archiving, only do this if the DBA is at lunch.

Use fixed width data - unsupported.

EXAMPLE

use strict; use warnings; use Oracle::Sqlldr;

my $sqlldr = Oracle::Sqlldr->new(-db=>'thedb');

$sqlldr->warnings(-status=>'on'); $sqlldr->table(-name=>'animals'); $sqlldr->user(-name=>'scott'); $sqlldr->pass(-word=>'tiger'); $sqlldr->fieldsterminatedby(-symbol=>','); $sqlldr->datafile(-file=>'animals.dat'); $sqlldr->controlfile(-file=>'animals.ctr'); $sqlldr->logfile(-file=>'animals.log'); $sqlldr->badfile(-file=>'animals.bad'); $sqlldr->discardfile(-file=>'animals.dis');

$sqlldr->create_controlfile() or die "cannot create the controlfile"; $sqlldr->write_controlfile() or die "cannot write controlfile";

my $r = $sqlldr->execute() or die "cannot execute sqlldr";

print "output from Oracle::Sqlldr: $r\n";

AUTHOR

Andrew McGregor, <mcgregor@cpan.org>

SEE ALSO

Oracle::SQLLoader

METHODS

new

constructor to create a new instance of the Sqlldr.

my $sqlldr = Oracle-Sqlldr;>

my $sqlldr = Oracle-Sqlldr(-warnings=>'on');>

my $sqlldr = Oracle-Sqlldr(-warnings => 'off',> -parameterfile = 'parameterfile.par',> -datafile = 'datafile.csv',> -controlfile = 'controlfile.ctr',> -logfile = 'logfile.log',> -discardfile = 'discardfile.dis',> -badfile = 'badfile.bad',> -table = 'table_name',> -fieldsterminatedby = ',',> -user = 'scott',> -pass = 'tiger',> -db = 'foo',> );

warnings

turn warnings on or or off return status, 1 = on, 0 = off

my $status = $sqlldr-warnings(-status=>'on');> my $status = $sqlldr-warnings(-status=>'off');> $sqlldr-warnings(-status=>1);> my $status = $sqlldr-warnings(-status=>0);> my $status = $sqlldr-warnings();>

warnings_on

turn warnings on

$sqlldr-warnings_on();>

warnings_off

turn warnings off

$sqlldr-warnings_off();>

logfile

set the logfile to load.

$sqlldr-logfile(-file=>'load.log');>

SQL*Loader writes messages to this log file during loading.

discardfile

set the discardfile to load.

$sqlldr-discardfile(-file=>'load.dis');>

SQL*Loader writes discarded rows to this discard file during loading.

badfile

set the badfile to load.

$sqlldr-badfile(-file=>'load.bad');>

SQL*Loader writes bad rows to this bad file during loading.

controlfile

set the controlfile to load.

$sqlldr-controlfile(-file=>'controlfile.ctr');>

datafile

set the datafile to load.

$sqlldr-datafile(-file=>'datafile.ctr');>

parameterfile

set the parameterfile to load.

$sqlldr-parameterfile(-file=>'parameterfile.dat');>

table

set the table to load into.

$sqlldr-table(-name=>'table_name');>

fieldsterminatedby

set the field to terminate the datafile.

$sqlldr-fieldsterminatedby(-symbol=>',');> my $t = $sqlldr-fieldsterminatedby;>

if you don't set this or pass null assumes records are fixed format .. unsupported :(

pass

set or get the password

$sqlldr-pass(-word=>'tiger');> my $pass = $sqlldr-pass;>

user

set or get the username

$sqlldr-user(-name=>'scott');> my $user = $sqlldr-user;>

cstr

get or set the connection string used

my $user = $sqlldr-cstr;>

db

set or get the db

$sqlldr-db(-name=>'foo:');> my $user = $sqlldr-db;>

create_controlfile

creates the controlfile from DB

write_controlfile

writes the control file to disk

execute

call and execute the sqlldr utility.

discardmax -- Number of discards to allow (Default all) skip -- Number of logical records to skip (Default 0) load -- Number of logical records to load (Default all) errors -- Number of errors to allow (Default 50) rows -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- Size of conventional path bind array in bytes (Default 65536) silent -- Suppress messages during run (header,feedback,errors,discards,partitions) direct -- use direct path (Default FALSE) parallel -- do parallel load (Default FALSE) file -- File to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- Size of Read buffer (Default 1048576)

cleanup

delete parameter, control, bad, discard and log files.

DESTROY

cleanup this instance

SQL*Loader Options

SQL*Loader provides the following options, which can be specified either on the command line or within a parameter file:

· bad . A file that is created when at least one record from the input file is rejected. The rejected data records are placed in this file. A record could be rejected for many reasons, including a non-unique key or a required column being null.

· bindsize . [256000] The size of the bind array in bytes.

· columnarrayrows . [5000] Specifies the number of rows to allocate for direct path column arrays.

· control . The name of the control file. This file specifies the format of the data to be loaded.

· data . The name of the file that contains the data to load.

· direct . [FALSE] Specifies whether or not to use a direct path load or conventional.

· discard . The name of the file that contains the discarded rows. Discarded rows are those that fail the WHEN clause condition when selectively loading records.

· discardmax . [ALL] The maximum number of discards to allow.

· errors . [50] The number of errors to allow on the load.

· external_table . [NOT_USED] Determines whether or not any data will be loaded using external tables. The other valid options include GENERATE_ONLY and EXECUTE.

· file . Used only with parallel loads, this parameter specifies the file to allocate extents from.

· load . [ALL] The number of logical records to load.

· log . The name of the file used by SQL*Loader to log results.

· multithreading . The default is TRUE on multiple CPU systems and FALSE on single CPU systems.

· parfile . [Y] The name of the file that contains the parameter options for SQL*Loader.

· parallel . [FALSE] Specifies a filename that contains index creation statements.

· readsize . The size of the buffer used by SQL*Loader when reading data from the input file. This value should match that of bindsize.

· resumable . [N] Enables and disables resumable space allocation. When .Y., the parameters resumable_name and resumable_timeout are utilized.

· resumable_name . User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.

· resumable_timeout . [7200 seconds] The time period in which an error must be fixed. This parameter is ignored unless resumable = Y.

· rows . [64] The number of rows to load before a commit is issued (conventional path only). For direct path loads, rows are the number of rows to read from the data file before saving the data in the datafiles.

· silent . Suppress errors during data load. A value of ALL will suppress all load messages. Other options include DISCARDS, ERRORS, FEEDBACK, HEADER, and PARTITIONS.

· skip . [0] Allows the skipping of the specified number of logical records.

· skip_unusable_indexes . [FALSE] Determines whether SQL*Loader skips the building of indexes that are in an unusable state.

· skip_index_maintenance . [FALSE] Stops index maintenance for direct path loads only.

· streamsize . [256000] Specifies the size of direct path streams in bytes.

· userid . The Oracle username and password.

cpanm Oracle::Sqlldr

perl -MCPAN -e shell
install Oracle::Sqlldr