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, sid => $sid ); $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>
John Huckelba - fix for single record files scenario
Craig Pearlman <cpearlman@healthmarketscience.com> - added various fixes, most importantly initializing and generating the options clause correctly.
The Oracle::SQLLoader module is Copyright (c) 2006 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.
Modifications to Oracle::SQLLoader are Copyright (c) 2006 Health Market Science, though this code remains free under the terms of the original license.
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. returns 1 if sqlldr ran successfully, 0 if there were errors or warnings
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
returns a listref of any SQL*Loader-specific error codes and messages that were reported in the load logs, e.g the instance is down (SQL*Loader-128), the table does not exist (SQL*Loader-941), or the username/password was wrong (SQL*Loader-101). see the 'SQL*Loader Messages' section of your Oracle docs
returns the last known SQL*Loader error code and message, or an empty string
based on the current configuration options, generate a control file
searches ORACLE_HOME and PATH environment variables for an executable program. returns the full path and file name of the first match, or undef if not found. can be invoked as a class or instance method.
Oracle::SQLLoader->findProgram('sqlldr') or $ldr->findProgram('sqlldr.exe')
ensure that ORACLE_HOME is set and that the sqlldr binary is present and executable. can be invoked as a class or instance method.
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
the loader defaults are almost 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.