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)