NAME CGI::AppBuilder::PLSQL - Oracle PL/SQL Procedures SYNOPSIS use CGI::AppBuilder::PLSQL; my $sec = CGI::AppBuilder::PLSQL->new(); my ($sta, $msg) = $sec->exe_sql($ar); DESCRIPTION This class provides methods for reading and parsing configuration files. new (ifn => 'file.cfg', opt => 'hvS:') This is a inherited method from CGI::AppBuilder. See the same method in CGI::AppBuilder for more details. exec_plsql($q,$ar) Input variables: $q - CGI class $ar - array ref containing the following variables: pid : project id such as ckpt, owb, dba, etc. task : task name required such as task1,task2,etc. target(sel_sn1) : select one (DB/server name) defining sid args(sel_sn2) : select two (Arguments) task_fn : task file name containing all the tasks defined svr_conn : host/server connection info db_conn : db connection info for each target/server task_conn : special connection for tasks. It overwrites db_conn for the task HA_* : hash array Variables used or routines called: None How to use: First define the parameters in the initial file or define all the parameters in a hash array reference as $ar->{$p} where $p are # # parameters in initial file pid = ckpt task = task2 target = owb1 args = val1:val2 task_fn = ora_jobs.txt outdir = /opt/www/logs excl_callsql = run_xmlrpt|run_genrpt # tasks excluded from calling call_plsql svr_conn = { # server connection usr => 'usr_name', pwd => 'security', svr => 'svr_name', orahome => '/opt/app/oracle/product/10.2.0/db_1', } db_conn = { tgt1 => 'system/pwd@dbl_1', tgt2 => 'system/pwd@dbl_2', } task_conn = { tgt1 => { task1 => 'owb_rep2/pwd@owb1', }, tgt2 => { }, } out_dir = { # overwrite general out_dir ckpt => 'd:/www/logs/ckpt/rpts', owb1 => '/opt/www/logs/owb1/rpts', } arg_required = { task1 => 'obj_name', } svr_allowed = { task1 => {cdx1=>1}, } task_sql = { task5 => 'chkts.sql', task8 => 'owb/owbcollect_exit.sql', } # # Tasks defined in task file (task_fn) task1 = # staigth SQL statement example ALTER session SET nls_date_format='YYYYMMDD.HH24MISS'; SET linesize 999 serveroutput ON SIZE 1000000 FORMAT WRAPPED; PROMPT Get instance status ; PROMPT
; COL host_name FOR a25; COL up_days FOR 9999.99; SELECT a.*, sysdate-startup_time as up_days FROM v\$instance a; task2 = # my ($q, $ar, $ar_log) = $self->start_app($0, \@ARGV); or my $ar = $self->read_init_file('/tmp/my_init.cfg'); $self->exec_plsql($q, $ar); You can use variables in the definition file. We have provided a list of pre-defined variables such as $a0~$a9 = arguments in sel_sn2 separated by colon (:) $sid = ($sn) $dtm = ("%Y%m%d_%H%M%S") $dt = ("%Y%m%d") $tm =