Andrew McGregor > Oracle-Sqlldr-0.13 > Oracle::Sqlldr

Download:
Oracle-Sqlldr-0.13.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.13   Source  

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.

syntax highlighting: