NAME

Perl class for creating Oracle triggers

SYNOPSIS

use Oracle::Trigger;

my %cfg = ('conn_string'=>'usr/pwd@db', 'table_name'=>'my_ora_tab');
my $ot = Oracle::Trigger->new;
my $sql= $ot->prepare(%cfg); 
# or combine the two
my $ot = Oracle::Trigger->new(%cfg);

DESCRIPTION

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

new (conn_string=>'usr/pwd@db',table_name=>'my_table')

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::Trigger;      # or
my $obj = Oracle::Trigger->new;     # or
my $cs  = 'usr/pwd@db';
my $tn  = 'my_table'; 
my $obj = Oracle::Trigger->new(cs=>$cs,tn=>$tn); # or
my $obj = Oracle::Trigger->new('cs',$cs, 'tn',$tn); 

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

This method constructs a Perl object and capture any parameters if specified. It creates and defaults the following variables:

$self->{conn_string} = "";       # or $self->{cs}
$self->{table_name}  = 'my_tab'; # or $self->{tn}  

prepare($cs, $tn, $tp)

Input variables:

$cs  - Oracle connection string in usr/pwd@db
$tn  - Oracle table name without schema
$tp  - trigger type
       DATA - trigger to audit a table. This is the default.

Variables used or routines called:

Debug::EchoMessage
  echoMSG - display message
{cs} - connection string
{tn} - table name 
{drop_audit}   - whether to drop audit table if it exists
{audit_table}  - audit table name, default to aud${$tn}
{trigger_name} - trigger name, default to trg${$tn}

How to use:

my $ar = $self->prepare('usr/pwd@db','my_tab');

Return: $hr - a hash array ref containing the following keys:

dbh         - the database handler
sql_audit   - SQL statement for creating the audit table
sql_trigger - SQL statement for creating the trigger

This method performs the following tasks:

1) create a database handler
2) check the existance of the table 
3) generate script for creating audit table
4) generate script for creating trigger

And it sets the following internal variable as well:

{dbh} - database handler
{sql_audit} - sql statements to create audit table
{sql_trigger} - sql statement to create trigger

execute($typ)

Input variables:

$typ - action type:
       TRIGGER - create trigger only 
       AUDIT   - create audit table only
       default - null and will create both

Variables used or routines called:

{dbh} - database handler
{sql_audit} - sql statements to create audit table
{sql_trigger} - sql statement to create trigger

How to use:

my $ar = $self->prepare('usr/pwd@db','my_tab');
$self->execute();

Return: 0|1: 0 - OK; 1 - failed

This method submit the sql statement to Oracle server.

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;

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 version is to test out the functions of Mail classes: Mail::Box::Message and Mail::Box::Manager.

  04/22/2005 (htu) - finished creating DATA trigger rountines.

SEE ALSO (some of docs that I check often)

Data::Describe, Oracle::Loader, CGI::Getopt, File::Xcopy, 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::Trigger

perl -MCPAN -e shell
install Oracle::Trigger