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