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)
