CGI-AppBuilder-PLSQL
view release on metacpan or search on metacpan
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 <b> Get instance status </b>;
PROMPT <hr>;
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 = <db_id_or_svr_id> ($sn)
$dtm = <date_and_time> ("%Y%m%d_%H%M%S")
$dt = <date> ("%Y%m%d")
$tm = <time> ("%H%M%S")
$y4 = <four_digit_year> ("%Y")
$mm = <month> ("%m")
$dd = <date> ("%d")
$hh = <hour> ("%H")
$mi = <minute> ("%M")
$ss = <second> ("%S")
Return: $pr will contain the parameters adn output from running the PL/SQL.
plsql_out - output from running the PL/SQL
is_callsql - whether to run call_plsql
=cut
sub exec_plsql {
my ($s, $q, $ar) = @_;
# print $s->disp_form($q, $ar);
print $s->disp_header($q, $ar);
my @c0 = caller(0); my @c1 = caller(1);
my $cls = (exists $c1[3]) ? $c1[3] : '';
my $prg = "$cls [$c0[2]] -> $c0[3]";
# 1. check required parameters
$s->echo_msg("1. checking required parameters...", 2);
my $vs = 'pid,task,task_fn,svr_conn,db_conn';
foreach my $t (split /,/, $vs) {
if (! exists $ar->{$t}) {
$s->echo_msg("ERR($prg): Parameter $t does not exist.",0); return;
}
}
my $pr = $s->def_inputvars($ar);
my $sn = $pr->{sid}; # sel_sn1 or target
my $pid = $ar->{pid}; # project id
my $tsk = lc $ar->{task}; # task name
my $ds = $pr->{ds}; # directory separator
my ($m) = ();
my $avs = 'svr_conn,db_conn,task_conn,out_dir,arg_required,svr_allowed';
$avs .= ',task_sql';
foreach my $t (split /,/, $avs) {
if ($t eq $tsk) {
$m = "ERR($prg): you could not use a preserved word to name your ";
$m .= "task - $tsk";
$s->echo_msg($m, 0); return;
}
}
# 2. read the task files
$s->echo_msg("2. reading task file...", 2);
my ($fn,$tsk_txt,$tsk_fn,$tsq) = ();
my $p = {};
my $tfn = $s->set_param('task_fn', $ar); # task file name:jp2.txt
my $ifn = $s->set_param('ifn', $ar); # parent init file name
my $tmp = $ifn; $tmp =~ s/\.(\w+)//;
my $tf2 = join $ds, $tmp, $pid, "$tsk.txt"; # task file name: jp2/ckpt/tsk1.txt
$ar->{ifn_dir} = $tmp; # init file dir
if (-f $tfn) {
$s->echo_msg("INFO: ($prg) reading $tfn...", 3);
$p = $s->read_init_file($tfn);
$tsk_txt = (exists $p->{$tsk}) ? $p->{$tsk} : "";
$s->echo_msg("INFO: ($prg) did not find $tsk in $tfn.", 3) if !$tsk_txt;
} else {
$s->echo_msg("INFO: ($prg) did not $tfn.", 3);
}
if (!$tsk_txt) {
if (-f $tf2) {
$s->echo_msg("INFO: ($prg) reading $tf2...", 3);
$p = $s->read_init_file($tf2);
$tsk_txt= (exists $p->{$tsk}) ? $p->{$tsk} : "";
$s->echo_msg("INFO: ($prg) did not find $tsk in $tf2.", 3) if !$tsk_txt;
} else {
$s->echo_msg("INFO: ($prg) did not find $tf2.", 3);
}
}
if (!$tsk_txt) { # look it in task_sql
if (! exists $ar->{task_sql}) {
$s->echo_msg("ERR: ($prg) no task sql is defined for $tsk.", 0); return;
}
$tsq = eval $ar->{task_sql};
if (! exists $tsq->{$tsk}) {
$s->expand_code($tpf1, $cr); # expand code variables
$s->expand_code($tpf1, $cr); # expand code variables
$s->expand_code($tpf1, $cr); # expand code variables
}
$cmd .= "$cfn -S $cs \@$tpf1";
croak "ERR: did not find sql file - $tpf1<br>\n" if ! -f $tpf1;
}
my $cmd2 = $cmd; $cmd2 =~ s/\</</g;
$s->echo_msg("CMD: <pre>\n$cmd2\n</pre>",2);
# 6. Execute the command
$s->echo_msg("6. executing the command...", 2);
# $s->echo_msg(" $p->{$tsk}",2);
# if ($^O !~ /^MSWin/i) { # non window
# ($r1, @a) = rexec($svr, $cmd, $usr, $pwd);
# } else {
$s->echo_msg("INFO: ($prg) set ORACLE_HOME=$ohm and PATH=$ohm/bin",3);
$ENV{ORACLE_HOME}=$ohm;
$ENV{PATH}="$ohm/bin";
open SQL, "$cmd|" or croak "ERR: could not run $cmd: $!<br>\n";
@a = <SQL>; close SQL;
# }
$pr->{plsql_out} = \@a;
$pr->{is_callsql} = 1;
$pr->{is_callsql} = 0 if (!$pr || ! exists $pr->{a0});
$pr->{is_callsql} = 0 if (exists $ar->{excl_callsql} &&
$tsk =~ /^($ar->{excl_callsql})/i);
$s->echo_msg("INFO: ($prg) output lines: $#a.", 3);
if ($s->debug > 4) { for my $i (0..$#a) { print "$i: $a[$i]<br>\n"; } }
# print "<pre><tt>\n@a\n</tt></pre>\n";
# get data records in the display and assign them into $ar
my $rr = $s->parse_record2(\@a, undef, '==',',');
foreach my $k (keys %{$rr->[0]}) { $ar->{$k} = $rr->[0]{$k}; }
# $s->set_cookies($q, $ar) if ($tsk =~ /^run_login/i);
$ar->{_sql_output} = \@a;
if ($tsk && $tsk =~ /^(run_login|run_logout)/i) {
my $u_vs = 'user_sid,user_uid,user_tmo';
my ($u_sid,$u_uid,$u_tmo) = $s->get_params($u_vs,$ar);
$u_sid = ($u_sid) ? $u_sid : '';
$u_uid = ($u_uid) ? $u_uid : '';
$u_tmo = ($u_tmo) ? $u_tmo : '';
$ar->{guid} = "$u_sid:$u_uid:$u_tmo";
$s->disp_index($q, $ar);
} else {
print "<pre>\n@a\n</pre>\n";
}
# $s->echo_msg(\@a,0);
wantarray ? %$pr : $pr;
# $s->call_plsql(\@a, $ar);
}
sub call_plsql {
my ($s, $rr, $ar) = @_;
my @c0 = caller(0); my @c1 = caller(1);
my $cls = (exists $c1[3]) ? $c1[3] : '';
my $prg = "$cls [$c0[2]] -> $c0[3]";
my $vs = 'dir_sep,sql_cfn,sql_cs';
my ($ds,$cfn,$cs) = $s->get_params($vs, $ar);
my $scn = eval $s->set_param('svr_conn', $ar); # Server connections
my $dbc = eval $s->set_param('db_conn', $ar); # DB connections
my ($svr,$usr,$pwd,$ohm) = ();
$ohm = $ar->{ohm} if exists $ar->{ohm};
$ohm = $scn->{orahome} if !$ohm && exists $scn->{orahome};
# my $vhm = 'ORACLE_HOME';
# $ohm = $ENV{$vhm} if exists $ENV{$vhm} && $ENV{$vhm};
$svr = $scn->{svr} if exists $scn->{svr};
$usr = $scn->{usr} if exists $scn->{usr};
$pwd = $scn->{pwd} if exists $scn->{pwd};
my $sn = $ar->{sid};
$sn = $ar->{sel_sn1} if !$sn;
$ds = '/' if !$ds;
$cs = $dbc->{$sn} if !$cs;
if (!$cfn) {
if ($^O !~ /^MSWin/i) {
$cfn = join $ds, $ohm, 'bin', 'sqlplus';
} else {
$cfn = join $ds, $ohm, 'bin', 'sqlplus.exe';
}
}
if (! -f $cfn) {
$s->echo_msg("ERR: ($prg) could not find sqlplus program - $cfn.", 0);
return;
}
if (!$cs) {
$s->echo_msg("ERR: ($prg) no db connect string.", 0);
return;
}
$s->echo_msg("INFO: ($prg) CFN set to $cfn.", 2);
#
# get a list of PL/SQL files
my ($f1, $d1, $r1) = ("","",""); # PL/SQL file name and folder name
# File - vw_acorda_stg_co.sql was written to /opt/ora/ufd/owbprod.
# N:\Client\owb_dir\owbprod
my @a = (); my @b = ();
my $re = qr/[\w\/\.\:\\\-\[\]\$]+/;
foreach my $i (0..$#$rr) {
my $rec = $rr->[$i];
if ($rec =~ m/^\s*File was spooled to ($re)/) {
push @b, $1;
} elsif ($rec =~ m/^\s*File - ([\w\.\-\$]+)\s* was written to ($re)/) {
($f1, $d1) = ($1, $2); push @b, (join $ds, $d1, $f1);
}
}
if ($#b < 0) {
$s->echo_msg("WARN: ($prg) did not find any sql file.", 2);
return;
} else {
$s->echo_msg("INFO: $#b SQL files will be executed.", 2);
}
#
# loop through each PL/SQL file
my ($cmd, $cmd2) = ();
( run in 0.788 second using v1.01-cache-2.11-cpan-39bf76dae61 )