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)