Oracle::Loader - Perl extension for creating Oracle PL/SQL and control file.


Oracle::Loader - Perl extension for creating Oracle PL/SQL and control file.


  use Oracle::Loader;
  $ldr = Oracle::Loader->new;
  $ldr->init;                     # only sets vbm(N),direct(N),reset(Y)
  $ldr->init(%args);              # set variables based on hash array
  $ldr->sync;                     # syncronize variables 
  $ldr->cols_ref($arf_ref);       # column definition array ref
  $ldr->param->dat_fn($fn);       # assign $fn to dat_fn
  $ldr->conn->Oracle($i, $v);     # assign $v to the connection array
  $ldr->disp_param;               # display parameters 
  $ldr->crt_sql;                  # create PL/SQL file 
  $ldr->crt_ctl;                  # create control file 
  $rv      = $ldr->param->sql_fn; # get sql file name
  $rv      = $ldr->param->dat_fn; # get data file name
  $rv      = $ldr->param->vbm;    # the same as the above
  $ary_ref = $ldr->cols_ref;      # get column def array ref
  %ary     = $ldr->get_param;     # get all the parameters
Notation and Conventions
   $ldr    a display object
   $crf    column definition array reference
   $fh     a file handler
   $fn     an output file name 
   $apd    N/Y, append to output file or not
   $tab    table name
   $dat    input data file name 
   $rst    Y/N, whether to reset the corresponding variables
   $typ    database type: Oracle, MSSQL, CSV, etc
   $cns    connection string: usr/pwd@db
   $sfn    sql program file name
   $ctl    sqldr control file name
   $sdr    source directory where definition files stored
   $phm    program home directory
   $log    sqlldr log file name
   $ext    definiton file extension such as '.def', '.var', etc.
   $sub    calling sub: result (report_results) or 
           error (report_errors)
   $drh    Driver handle object (rarely seen or used in applications)
   $h      Any of the $??h handle types above
   $rc     General Return Code  (boolean: true=ok, false=error)
   $rv     General Return Value (typically an integer)
   @ary    List of values returned from the database, typically a row 
           of data
   $rows   Number of rows processed (if available, else -1)
   $fh     A filehandle
   undef   NULL values are represented by undefined values in perl
   \%attr  Reference to a hash of attribute values passed to methods


This is my seocnd object-oriented Perl program. The Loader module creates data definition language (DDL) codes for creating tables and control file to be used to load data into the tables. It creates DDL codes based on column definitons contained in an array or read from a definition file. It also has reporting functions to generate SQL*Load error reports and load result reports.

The column definition array could be built from Data::Describe module. It is actually an array with hash members and contains these hash elements ('col', 'typ', 'wid', 'max', 'min', 'dec', 'dft', 'req', and 'dsp') for each column. The subscripts in the array are in the format of $ary[$col_seq]{$hash_ele}. The hash elements are:

  col - column name
  typ - column type, 'N' for numeric, 'C' for characters, 
        'D' for date
  max - maximum length of the record in the column
  wid - column width. It is the max of the column length. If 
        'wid' presents, the max and min are not needed.
  min - minimum length of the record in the column
  dec - maximun decimal length of the record in the column
  dft - date format string, e.g., YYYY/MM/DD, 
        MON/DD/YYYY HH24:MI:SS
  req - whether there is null or zero length records in the 
        column only 'NOT NULL' is shown
  dsp - column description

The module will use column definitons to create DDL codes and control file using crt_sql and crt_ctl methods.



What are the parameters?

            CSV = []                            
         DirSep = /                             
         Oracle = [DBI:Oracle:orcl,usrid,userpwd]
     add_center =                               
         append = N                             
         bad_fn = /dlb/data/S083/load/s083p001.bad
       cols_ref = ARRAY(0x1787a4)               
         commit = N                             
           conn = ConnType::CSV,ConnType::Oracle
         ctl_fn = /dlb/data/S083/load/s083p001.ctl
         dat_fn = /dlb/data/S083/load/s083p001.dat
        db_type = Oracle                        
         dbconn = usrid/userpwd@orcl          
         dbhome = /export/home/oracle7          
          dbpwd = userpwd                       
          dbsid = orcl                          
          dbtab = p083p001                      
           dbts = data_ts                       
          dbusr = userid                       
         def_fn = /dlb/data/S083/load/s083p001.def
         direct = N                             
         dis_fn = /dlb/data/S083/load/s083p001.dis
         log_fn = /dlb/data/S083/load/s083p001.log
         out_fh =                               
      overwrite = Y                             
      relax_req = Y                             
          reset = Y                             
          spool = /tmp/xx_tst.lst               
         sql_fn = /tmp/xx_tst.sql               
        src_dir =                               
   study_number =                               
        ts_iext = 21k                           
        ts_next = 2k                            
            vbm = Y

How to create a Loader object?

You can create an empty Loader object using the following methods:

  $ldr = Oracle::Loader->new();
  $ldr = new Oracle::Loader;

If you have an hash array %p containing all the parameters, you use the array to initialize the object:


You can create your hash array to define your object attributes as the following:

  %p = (
    'vbm'       => 'Y',    # use verbose mode 
     'cols_ref' => \@C,    # array_ref for col defs
  $ldr = Oracle::Loader->new(%attr);

How to change the array references in the display object

You can pass data and column definition array references to display objects using the object constructor new or using the set methods:

  $ldr = Oracle::Loader->new($arf, $crf); 

How to access the object?

You can get the information from the object through all the methods described above without providing a value for the parameters.

Future Implementation

Although it seems a simple task, it requires a lot of thinking to get it working in an object-oriented frame. Intented future implementation includes


SEE ALSO (some of docs that I check often)

Data::Describe, perltoot(1), perlobj(1), perlbot(1), perlsub(1), perldata(1), perlsub(1), perlmod(1), perlmodlib(1), perlref(1), perlreftut(1).


Copyright (c) 2000-2001 Hanming Tu. All rights reserved.

This package is free software and is provided ``as is'' without express or implied warranty. It may be used, redistributed and/or modified under the terms of the Perl Artistic License (see

 Oracle::Loader - Perl extension for creating Oracle PL/SQL and control file.