Oracle::Sqlldr - Perl wrapper around Oracle's sqlldr utility.
use Oracle::Sqlldr; my $sqlldr = Oracle::Sqlldr->new(); # get new sqlldr object
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.
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`.
execute()
sqlldr
`ps -deaf`
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";
Andrew McGregor, <mcgregor@cpan.org>
Oracle::SQLLoader
constructor to create a new instance of the Sqlldr.
my $sqlldr = Oracle-Sqlldr;>
my $sqlldr = Oracle-
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',> );
-parameterfile =
-datafile =
-controlfile =
-logfile =
-discardfile =
-badfile =
-table =
-fieldsterminatedby =
-user =
-pass =
-db =
);
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();>
my $status = $sqlldr-
$sqlldr-
turn warnings on
$sqlldr-warnings_on();>
turn warnings off
$sqlldr-warnings_off();>
set the logfile to load.
$sqlldr-logfile(-file=>'load.log');>
SQL*Loader writes messages to this log file during loading.
set the discardfile to load.
$sqlldr-discardfile(-file=>'load.dis');>
SQL*Loader writes discarded rows to this discard file during loading.
set the badfile to load.
$sqlldr-badfile(-file=>'load.bad');>
SQL*Loader writes bad rows to this bad file during loading.
set the controlfile to load.
$sqlldr-controlfile(-file=>'controlfile.ctr');>
set the datafile to load.
$sqlldr-datafile(-file=>'datafile.ctr');>
set the parameterfile to load.
$sqlldr-parameterfile(-file=>'parameterfile.dat');>
set the table to load into.
$sqlldr-table(-name=>'table_name');>
set the field to terminate the datafile.
$sqlldr-fieldsterminatedby(-symbol=>',');> my $t = $sqlldr-fieldsterminatedby;>
my $t = $sqlldr-
if you don't set this or pass null assumes records are fixed format .. unsupported :(
set or get the password
$sqlldr-pass(-word=>'tiger');> my $pass = $sqlldr-pass;>
my $pass = $sqlldr-
set or get the username
$sqlldr-user(-name=>'scott');> my $user = $sqlldr-user;>
my $user = $sqlldr-
get or set the connection string used
my $user = $sqlldr-cstr;>
set or get the db
$sqlldr-db(-name=>'foo:');> my $user = $sqlldr-db;>
creates the controlfile from DB
writes the control file to disk
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)
delete parameter, control, bad, discard and log files.
cleanup this instance
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.
5 POD Errors
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head1'
'=item' outside of any '=over'
Non-ASCII character seen before =encoding in '·'. Assuming CP1252
To install Oracle::Sqlldr, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Oracle::Sqlldr
CPAN shell
perl -MCPAN -e shell install Oracle::Sqlldr
For more information on module installation, please visit the detailed CPAN module installation guide.