NAME

Oracle::SQLLoader - object interface to Oracle's sqlldr

DESCRIPTION

Oracle::SQLLoader provides an object wrapper to the most commonly used functionality of Oracle's SQL*Loader bulk loader utility. It tries to dwim as best as possible by using defaults to all of the various sqlldr options.

The module currently supports the loading of a single table from a single file. The file can be either fixed width or delimited. For a delimited file load, just add the names of the destination columns in the order the fields appears in the data file and optionally supply a data type. For a fixed width load, supply the destination column name; the combination of the field starting offset and field length, or the field start and end offsets in the data file; and an optional data type.

Besides letting you skip the Oracle docs, the module provides a lot of useful stats and return codes by parsing the sqlldr output.

SYNOPSIS

  use Oracle::SQLLoader qw/$CHAR $INT $FLOAT $DATE/;

  ### load a simple comma-delimited file to a single table
  $ldr = new Oracle::SQLLoader(
                                infile => '/tmp/test.dat',
                                terminated_by => ',',
                                userid => $user,
                                password => $pass,
                               );

  $ldr->addTable(table_name => 'test_table');
  $ldr->addColumn(column_name => 'first_col');
  $ldr->addColumn(column_name => 'second_col');
  $ldr->addColumn(column_name => 'third_col');
  $ldr->executeSqlldr() || warn "Problem executing sqlldr: $@\n";

  # stats
  $skipped = $ldr->getNumberSkipped();
  $read = $ldr->getNumberRead();
  $rejects = $ldr->getNumberRejected();
  $discards = $ldr->getNumberDiscarded();
  $loads = $ldr->getNumberLoaded();



  #### a fixed width example
  $fwldr = new Oracle::SQLLoader(
                                 infile => '/tmp/test.fixed',
                                 userid => $user,
                                 password => $pass,
                                 );
  $fwldr->addTable(table_name => 'test_table');

  $fwldr->addColumn(column_name => 'first_col',
                    field_offset => 0,
                    field_length => 4,
                    column_type => $INT);

  $fwldr->addColumn(column_name => 'second_col',
                    field_offset => 4,
                    field_end => 9);

  $fwldr->addColumn(column_name => 'third_col',
                    field_offset => 9,
                    field_end => 14,
                    column_type => $CHAR);

  $fwldr->addColumn(column_name => 'timestamp',
                    field_offset => 9,
                    field_length => 13,
                    column_type => $DATE,
                    date_format => "YYYYMMDD HH24:MI");

  $fwldr->executeSqlldr() || warn "Problem executing sqlldr: $@\n";

  # stats
  $skipped = $fwldr->getNumberSkipped();
  $read = $fwldr->getNumberRead();
  $rejects = $fwldr->getNumberRejected();
  $discards = $fwldr->getNumberDiscarded();
  $loads = $fwldr->getNumberLoaded();

AUTHOR

Ezra Pagel <ezra@austinlogistics.com>

COPYRIGHT

The Oracle::SQLLoader module is Copyright (c) 2004 Ezra Pagel.

The Oracle::SQLLoader module is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.4 or, at your option, any later version of Perl 5 you may have available.

METHODS

new()

create a new Oracle::SQLLoader object

mandatory arguments

infile - the name and full filesystem path to the input file

optional arguments

userid - the username and password for this load

addTable()

identify a table to be loaded. multiple adds with the same table name clobbers any old definition.

mandatory arguments

table name - the name of the table to load

optional arguments

when_clauses

continue_clauses

terminated_clauses

enclosed_clauses

nullcols

addColumn()

add a column to be loaded

mandatory arguments for all load types

column_name - the name of the column to be loaded

mandatory arguments for fixed width load types

field_offset - starting position of the data in the input file

and one of:

field_end - the ending position of the data in the input file

field_length - the length of the field, measured from field_offset

optional arguments for all load types

table_name - the name of the table that this column belongs to. if no table name is specified, default is the last known table name. if no previous table name exists, croak.

column_type - $CHAR, $INT, $FLOAT, or $DATE; defaults to $CHAR

date_format - the TO_DATE format for a $DATE column; defaults to "DD-MON-YY"

executeSqlldr()

kick off an sqlldr job

checkLogfile()

parse an sqlldr logfile and be store results in object status

optional arguments

$logfile - the file to parse; defaults to the object's current logfile

STATUS METHODS

getNumberSkipped()

returns the number of records skipped , or undef if no stats are known

getNumberRead()

returns the number of read from all input files, or undef if no stats are known

getNumberRejected()

returns the number of records rejected, or undef if no stats are known

getNumberDiscarded()

returns the number of records discarded, or undef if no stats are known

getNumberLoaded()

returns the number of records successfully loaded, or undef if no stats are known

getLastRejectMessage()

returns the last known rejection message, if any

Content Generation Functions

generateControlfile()

based on the current configuration options, generate a parameter file. the generated text is retrievable by calling getParfileText

generateSessionClause()

generateTablesClause()

generateDataClause()

generateParfile()

based on the current configuration options, generate a parameter file. the generated text is retrievable by calling getParfileText

initDescriptions()

this stuff is almost *all* directly from the sqlldr usage dumps

findProgram()

searches ORACLE_HOME and PATH environment variables for an executable program

mandatory arguments

$executable - the name of the program to search for

checkEnvironment()

ensure that ORACLE_HOME is set and that the sqlldr binary is present and executable

cpanm Oracle::SQLLoader

perl -MCPAN -e shell
install Oracle::SQLLoader