NAME

Oracle::DML::Common - Perl class for creating Oracle triggers

SYNOPSIS

use Oracle::DML::Common;

my %cfg = ('conn_string'=>'usr/pwd@db', 'table_name'=>'my_ora_tab');
my $ot = Oracle::DML::Common->new;
# or combine the two together
my $ot = Oracle::DML::Common->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.

new ()

Input variables:

%ha  - any hash array containing initial parameters

Variables used or routines called:

None

How to use:

my $obj = new Oracle::DML::Common;      # or
my $obj = Oracle::DML::Common->new;  

Return: new empty or initialized Oracle::DML::Common object.

This method constructs a Perl object and capture any parameters if specified.

METHODS

The following are the common methods, routines, and functions used by other classes.

Connection Methods

The :db_conn tag includes sub-rountines for creating and managing database connections.

use Oracle::DML::Common qw(:db_conn);

It includes the following sub-routines:

get_dbh($con, $dtp)

Input variables:

$con - Connection string for
       Oralce: usr/pwd@db (default)
          CSV: /path/to/file
     ODBC|SQL: usr/pwd@DSN[:approle/rolepwd]
$dtp - Database type: Oracle, CSV, etc

Variables used or routines called:

DBI
DBD::Oracle
Win32::ODBC

How to use:

$self->get_dbh('usr/pwd@dblk', 'Oracle');
$self->get_dbh('usr/pwd@dblk:approle/rpwd', 'SQL');

Return: database handler

If application role is provided, it will activate the application role as well.

is_object_exist($dbh,$tn,$tp)

Input variables:

$dbh - database handler, required.
$tn  - table/object name, required.
       schema.table_name is allowed.

Variables used or routines called:

echoMSG    - display messages.

How to use:

# whether table 'emp' exist
$yesno = $self->is_object_exist($dbh,'emp');

Return: 0 - the object does not exist; 1 - the object exist;

Table Methods

The :table tag includes sub-rountines for creating, checking and manipulating tables.

use Oracle::DML::Common qw(:table);

It includes the following sub-routines:

get_table_definition($dbh,$tn,$cns,$otp)

Input variables:

$dbh - database handler, required.
$tn  - table/object name, required.
       schema.table_name is allowed.
$cns - column names separated by comma.
       Default is null, i.e., to get all the columns.
       If specified, only get definition for those specified.
$otp - output array type:
       AR|ARRAY        - returns ($cns,$df1,$cmt)
       AH1|ARRAY_HASH1 - returns ($cns,$df2,$cmt)
       HH|HASH         - returns ($cns,$df3,$cmt)
       AH2|ARRAY_HASH2 - returns ($cns,$df4,$cmt)

Variables used or routines called:

echoMSG - display messages.

How to use:

($cns,$df1,$cmt) = $self->getTableDef($dbh,$table_name,'','array');
($cns,$df2,$cmt) = $self->getTableDef($dbh,$table_name,'','ah1');
($cns,$df3,$cmt) = $self->getTableDef($dbh,$table_name,'','hash');
($cns,$df4,$cmt) = $self->getTableDef($dbh,$table_name,'','ah2');

Return:

$cns - a list of column names separated by comma.
$df1 - column definiton array ref in [$seq][$cnn].
  where $seq is column sequence number, $cnn is array
  index number corresponding to column names: 
        0 - cname, 
        1 - coltype, 
        2 - width, 
        3 - scale, 
        4 - precision, 
        5 - nulls, 
        6 - colno,
        7 - character_set_name.
$df2 - column definiton array ref in [$seq]{$itm}.
  where $seq is column number (colno) and $itm are:
        col - column name
        seq - column sequence number
        typ - column data type
        wid - column width
        max - max width
        min - min width
        dec - number of decimals
        req - requirement: null or not null
        dft - date format
        dsp - description or comments
$df3 - {$cn}{$itm} when $otp = 'HASH'
  where $cn is column name in lower case and
        $itm are the same as the above
$df4 - [$seq]{$itm} when $otp = 'AH2'
  where $seq is the column number, and $itm are:
        cname     - column name (col)
        coltype   - column data type (typ)
        width     - column width (wid)
        scale     - column scale (dec)
        precision - column precision (wid for N)
        nulls     - null or not null (req)
        colno     - column sequence number (seq)
        character_set_name - character set name

HISTORY

• Version 0.1

  This versionwas contained in Oracle::Trigger class.

• Version 0.2

  04/29/2005 (htu) - extracted common routines from Oracle::Trigger class and formed Oracle::DML::Common.

SEE ALSO (some of docs that I check often)

Data::Describe, Oracle::Loader, CGI::Getopt, File::Xcopy, Oracle::Trigger, perltoot(1), perlobj(1), perlbot(1), perlsub(1), perldata(1), perlsub(1), perlmod(1), perlmodlib(1), perlref(1), perlreftut(1).

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::DML::Common

perl -MCPAN -e shell
install Oracle::DML::Common