Oracle::SQLLoader - object interface to Oracle's sqlldr
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 length 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 length 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.
use Oracle::SQLLoader qw/$CHAR $INT $DECIMAL $DATE/; ### load a simple comma-delimited file to a single table $ldr = new Oracle::SQLLoader( infile => '/tmp/test.dat', terminated_by => ',', username => $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->executeLoader() || warn "Problem executing sqlldr: $@\n"; # stats $skipped = $ldr->getNumberSkipped(); $read = $ldr->getNumberRead(); $rejects = $ldr->getNumberRejected(); $discards = $ldr->getNumberDiscarded(); $loads = $ldr->getNumberLoaded(); $beginTS = $ldr->getLoadBegin(); $endTS = $ldr->getLoadEnd(); $runtimeSecs = $ldr->getElapsedSeconds(); $secsOnCpu = $ldr->getCpuSeconds(); #### a fixed length example $flldr = new Oracle::SQLLoader( infile => '/tmp/test.fixed', username => $user, password => $pass, ); $flldr->addTable(table_name => 'test_table'); $flldr->addColumn(column_name => 'first_col', field_offset => 0, field_length => 4, column_type => $INT); $flldr->addColumn(column_name => 'second_col', field_offset => 4, field_end => 9); $flldr->addColumn(column_name => 'third_col', field_offset => 9, field_end => 14, column_type => $CHAR); $flldr->addColumn(column_name => 'timestamp', field_offset => 9, field_length => 13, column_type => $DATE, date_format => "YYYYMMDD HH24:MI"); $flldr->executeLoader() || warn "Problem executing sqlldr: $@\n"; # stats $skipped = $ldr->getNumberSkipped(); $read = $ldr->getNumberRead(); $rejects = $ldr->getNumberRejected(); $discards = $ldr->getNumberDiscarded(); $loads = $ldr->getNumberLoaded(); $beginTS = $ldr->getLoadBegin(); $endTS = $ldr->getLoadEnd(); $runtimeSecs = $ldr->getElapsedSeconds(); $secsOnCpu = $ldr->getCpuSeconds();
Ezra Pagel <ezra@cpan.org>
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.
SQL*Loader is Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
create a new Oracle::SQLLoader object
identify a table to be loaded. multiple adds with the same table name clobbers any old definition.
add a column to be loaded
generate a control file and execute an sqlldr job. this is a blocking call. if you don't care about load statistics, you can always fork it off.
parse an sqlldr logfile and be store results in object status
returns the number of records skipped , or undef if no stats are known
returns the number of read from all input files, or undef if no stats are known
returns the number of records rejected, or undef if no stats are known
returns the number of records discarded, or undef if no stats are known
returns the number of records successfully loaded, or undef if no stats are known
returns the last known rejection message, if any
the time that the job began represented as epoch timestamp
the time that the job finished represented as epoch timestamp
returns the number if seconds elapsed during load
returns the number if seconds on cpu during load
based on the current configuration options, generate a control file
searches ORACLE_HOME and PATH environment variables for an executable program
ensure that ORACLE_HOME is set and that the sqlldr binary is present and executable
generate the session-wide information for a control file
generate table and column information for a control file
generate any input data for a control file
this stuff is almost *all* directly from the sqlldr usage dumps
To install Oracle::SQLLoader, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Oracle::SQLLoader
CPAN shell
perl -MCPAN -e shell install Oracle::SQLLoader
For more information on module installation, please visit the detailed CPAN module installation guide.