NAME
TVL::OraTools.pm - Tools and utilities for perl scripts
SYNOPSIS
use TVL::OraTools;
# Process the commandline
getOpt( \@ARGV, 'abc:d' );
foreach (@OPT) {
...
/c/ && do { print "-c has argument: " . ( $OPTARG{'r'} ) . "\n" };
/d/ && do { $TVL::OraTools::debug ++ ;
print STDERR "Tools debug level is now [" . $TVL::OraTools::debug . "]\n"
};
}
# Print some debug info
debug "Non-switched arguments are: " . join ( " " , @ARGS ) . "\n" , 2;
# Connect to a local database
$dbConn = doConnect ( "scott/tiger@test", "" );
# do something if $number holds a number
$number = <STDIN>;
if ( numeric( $number ) ) {...}
# Get a filename and ensure it is safe
$file = untaintFile( <STDIN> );
# Parse a resource xml file and extract statements
$statements = getStatements( $number , $file );
# Execute a statement
$result = execStatement ( $dbConn, $$statements{ 'mystatement' } );
# Send an email with the results, one per line, values separated by '|'.
sendEmail( { "SMTPServer" => "smtp.server.my.org", "MailFrom" => "the.office@my.org" }
, [ 'some.one@some.com', 'someone.else@else.where' ]
, 'Subject of this email'
, "The results are: \n\t" . join ( "\n\t" , @{$results} )
);
# Convert a date to the standard localtime output
print scalar localtime( parseDate( '12-JAN-2000 12:10' ) ) , "\n" ;
DESCRIPTION
Package with handy routines. Updated in 2010 with some routines previously contained in the query utility oralist.pl and some new functions. The utilities mostly relate to Oracle database connections, hence the name OraTools.
Notwithstanding the focus on Oracle databases, this module certainly offers useful functions for scripts that have no Oracle connection at all.
Author
Tony van Lingen, 6-nov-2002, 20-Jul-2010
METHODS
debug($;$)
Print a debug message. The debug level is maintained in the package variable debug. To increase the level say:
$TVL::OraTools::debug++;
The default debug level is 0: no messages are printed.
Parameters: messg : The message to be printed severity : The minimum debug level at which this message is to be printed (any level except 0, if omitted).
numeric($)
Check if a given string is numeric. Numeric is defined as [-][0-9]...[.[[0-9]...]]. No other characters than a leading '-' and a single '.' somewhere inbetween a string of digits is allowed.
Parameter: test - string to test for numericy.
Returns: False, if the string contains other than numbers, a dot and possibly a leading dash. True, if the string represents a number.
untaintFile($)
Check a filename for invalid characters ( like pipe symbols that could pipe into rm -r / and other nasty things). Needed when running with -T (taint mode).
Valid characters are:
Parameter : check - tainted file name
Returns : string - untained filename
Side effect: This function dies if the check string passed in fails the filename check.
sendEmail(\%$$$;$)
Send an email, optionally with a file attached. The site hash must be initialised otherwise there will be no mail sender or SMTP host. The following hash keys are required: SMTPServer, MailFrom.
Parameters: siteRef - reference to a site configuration hash recipientListRef - a reference to an array with recipient email addresses subject - subject line for the email mesg - the message body filename - optional, file to be attached
getOpt($$)
Filter options out of a string containing of groups of characters seperated by spaces, as in print @ARGV. When passing @ARGV, be sure to either escape or quote it!. This will interpret a group of charcters starting with - as a string of one-letter options. If an one-letter option in the string requires an argument, the rest of the string will be gobbled up to satisfy it. Failing that, the next word will be taken, regardless. Any words not starting with - and not used as argument to an option will be taken to be a positional argument (relative to other arguments, not hampered by any interfering options) and returned as a seperate array.
Syntax: getOpt ( string to filter , <valid options> )
Valid options are passed as a string, or an array reference where an option requiring an argument is followed by a : (as in unix). e.g. ab:c will cause the options -a, -b and -c to be recognised and -b will have an argument.
Sets: @OPTS - options found in "string to filter". @ARGS - arguments found in "string to filter" (in order: first argument is 0). %OPTARG - hash of arguments that go with options. The option requiring an argument is the key. Options not requiring arguments will not be included!
doConnect($$)
connect to an Oracle database using either tnsnames.ora or an oci connection. Exits on error. This function requires the DBD::Oracle to be availalbe.
Parameters: connect - Oracle connect string (user{/password}{@sid}) host - Host for OCI connect
Returns: DBD::Oracle connection object
getStatements($$;$)
Read the statements to be executed from an XML file into memory. The XML file has the following structure:
<?xml ... ?>
<root>
{ <type1> }
<statements version=7>... <some-statement>...</some-statement>...</statements>
<statements version=8.1>... <some-statement>...</some-statement>...</statements>
<statements version=11>... <some-statement>...</some-statement>...</statements>
{ </type1> }
...
<statements> <!-- generic version statements for all types -->
... <some-statement>...</some-statement>...
</statements>
</root>
The root tags can have any name and must be present. The type tags are optional and may be used to distinguish statements for different platforms contained in the same file. Only statements for one type are extracted. If no type is passed, any type tags are ignored.
Only one tag per type/version should be specified, as the statements are loaded in order of version. If more than one tag per version exists, no guarantee is given as to which statement is loaded first. For the same reason, a generic version tag should exist either in the root tag, or in The file is parsed with using TVL::PXC, see that module for more information. Statements are read starting with the lowest version in the file, and put into a hash where the statement lables are the keys. When a statement has sub-statements, the master key will contain a hash reference to a hash with the sub-statements. "Naked" statements will not be supported. If there are multiple versions defined in the XML file, they will be read up to the highest version that is lower or equal to the version number passed. If the version passed is lower than the lowest version in the file, the program will fail.
Parameters: version - database version to use (major and minor version only - treated as a decimal number. file - XML file name type - (optional) Type of the statements. If passed, subtags are expected with type names
Returns: reference to a hash containing the statements for the requested version. The keys are the tag names in the file. No other attributes than the version attribute of the statements tag are recognised. If no statements are found this returns undef.
buildHash($$;$)
(Not exported) Recursively retrieve the statements from a tag and build a hash table with them.
Parameters : statementHash - reference to the statement hash tag - tag to extract statements from iteration level - (optional) iteration level
Returns : A hash reference if subtags are present, or tag-text()>. Undef if neither subtags or text exist.
sortTagByVersion
(Not exported) Routine to sort TVL::PXC::Tag objects by the (numeric) value of their version attribute. Use with the sort function.
Syntax: sort sortTagByVersion @array;
execStatement($$;@)
Execute a statement in an Oracle database. The statement is either a PL/SQL block or a Select statement, see untaintStatement below for what constitutes a valid and acceptable statement. A PL/SQL statement must use dbms_output to produce a result. The dbms_output buffer is set to 1,000,000 bytes.
All comments and empty lines will be removed before execution. If no valid statement is found, this function will carp.
Parameters : dbConn - DBD::Oracle connection object; statement - string containing a Select or a PL/SQL block. binval - optional list of values to bind to the placeholders in the statement.
Returns: reference to array in which the cells contain one row/line of output.
untaintStatement($)
Check if a statement is a valid SQL Select statement ( { WITH ... } SELECT ... FROM ... ) or a PL/SQL block ( DECLARE .. END; ). Preceding comments will be stripped before the resulting statement is checked.
No updates, deletes or inserts will be allowed as part ofa PL/SQL block. Note that the check for this will not distinguish between comments and actual statement lines, so don't put DML keywords anywhere in the statement.
This function will croak in case of an invalid statement, and $! will be set to 3.
Parameter: check - string to check
Returns : plsql - boolean that indicates whether the statement is a PL/SQL block (true) or a SQL statement (false).
parseDate($)
parses a date into a time() compatible number. We'll accept (interspersed with [.,] and seperated by [-/]) e.g.:
DD Month [CC]YY [ HH24:MI[:SS] ]
Month DD [CC]YY [ HH24:MI[:SS] ]
DD Month [CC]YY [ HH:MI[:SS] AM|PM ]
Month DD [CC]YY [ HH:MI[:SS] AM|PM ]
DD-Month-[CC]YY [ HH24:MI[:SS] ]
DD Month, HH24:MI[:SS], [CC]YY
DD-MM-[CC]YY [ HH24:MI[:SS] ]
CCYY-MM-DD [ HH24:MI[:SS] ]
DD-MM-[CC]YY [ HH:MI[:SS] AM|PM ]
CCYY-MM-DD [ HH:MI[:SS] AM|PM ]
HH24:MI[:SS]
etc.
Basically everything with a written month in it will be interpreted correctly, with the first 2-digit number that has not ':' in it being the day. A numeric month should always be in second position, and enclosed in [-/], or it will be misinterpreted
A group of digits with ':' in them is always interpreted as time, a group of digits and/or characters with '/' or '-' in them is a date. Punctuation [.,;] is removed.
Parameter: date - string to be parsed
Returns: timelocal(@time,@date) (seconds since the epoch).