NAME

Oracle::DDL - Perl class for Oracle batch DML

SYNOPSIS

  use Oracle::DDL;

  my %cfg = ('conn_string'=>'usr/pwd@db', 'table_name'=>'my_ora_tab');
  my $ot = Oracle::DDL->new;
  # or combine the two together
  my $ot = Oracle::DDL->new(%cfg);
  my $sql= $ot->prepare(%cfg); 
  $ot->execute();    # actually create the audit table and trigger

DESCRIPTION

This class contains methods to create audit tables and triggers for Oracle tables.

Object Constructor

new (%arg)

Input variables:

  $cs  - Oracle connection string in usr/pwd@db
  $tn  - Oracle table name without schema

Variables used or routines called:

  None

How to use:

   my $obj = new Oracle::DDL;      # or
   my $obj = Oracle::DDL->new;     # or
   my $cs  = 'usr/pwd@db';
   my $tn  = 'my_table'; 
   my $obj = Oracle::DDL->new(cs=>$cs,tn=>$tn); # or
   my $obj = Oracle::DDL->new('cs',$cs, 'tn',$tn); 

Return: new empty or initialized Oracle::DDL object.

METHODS

The following are the common methods, routines, and functions defined in this class.

Exported Tag: All

The :all tag includes all the methods or sub-rountines defined in this class.

  use Oracle::DDL qw(:all);

add_primary_key($dbh, $tab, $pk, $exe)

Input variables:

  $dbh - database handler
  $tab - table name
  $pk  - primary key column name
  $exe - whether to execute the SQL statement
         0 - No (default); 1- Yes.

Variables used or routines called:

  echoMSG - display message

How to use:

    $self->add_primary_key(4dbh, 'my_tab', 'id');

Return: 0|1 - whether it is successful 0 - OK; 1 - failed

create_table($tab,$crf,$opt)

Input variables:

  $tab - table name
  $crf - array ref containing table definition as $crf->[$i]{$itm},
         where $i is column number and $itm is col,typ,req,wid,dft,etc.
  $opt - other table related options:
    dbh           - database handler
    fn_sql        - output file name for SQL codes
    fn_log        - log file name for spool off and default to 
                    fn_sql with the extension replaced with .log
    fh_sql        - output file handler for SQL codes 
    action        - whether to return SQL codes
         SQL (default) - return array ref containing SQL codes
               execute SQL codes if dbh is specified
         TXT - return SQL codes in text and write SQL codes to
               output file if fn_sql or fh_sql is specified. 
         ALL - return SQL codes and 
               write to output file if fn_sql or fh_sql is specified
               execute SQL statements if dbh is specified
    public_select - 1 or actual grant statement
    drop_table    - 1 - drop before creating it; 0 - not drop
    relax_constraint - 0 - no; 1 - yes

Variables used or routines called:

  echoMSG - display message

How to use:

  my $cs = 'usr/pwd\@db';
  my $dbh = get_dbh($cs);
  my $fh  = new IO::File "> myfile.sql"; 
  my %opt = (dbh=>$dbh, fh_sql => $fh,action=>'txt');
  my $crf = $self->read_tab_def('table.def', 'myTab',',');
  my $sql = $self->create_table('myTab',${$crf}{mytab},\%opt);

Return: the result of table creation or the SQL code for creating the table.

HISTORY

• Version 0.10

  This version is to test the concept.

• Version 0.2

SEE ALSO (some of docs that I check often)

Oracle::Loader, Oracle::Trigger, Oracle::DML, Oracle::DML::Common, CGI::Getopt, File::Xcopy

AUTHOR

Copyright (c) 2005 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 http://www.perl.com/perl/misc/Artistic.html)

cpanm Oracle::DDL

perl -MCPAN -e shell
install Oracle::DDL