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 <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

 read_plsql($fn, $pr)
   Input variables:

     $fn - full path to a file name
     $pr - parameter array
           plsql_dir - directory where plsql files reside

   Variables used or routines called:

     CGI::AppBuilder::Message
       echo_msg - echo messages

   How to use:

     my $pr = $self->read_plsql('code_lib.ini');

   Return: Hash array or ref of hash array - $pr

   This method reads PL/SQL code files containing functions and procedures
   in the format of key=values. Multiple lines is allowed for values as
   long as the lines after the "key=" line are indented as least with two
   blanks. For instance:

     echo = procedure each ( msg clob, lvl NUMBER DEFAULT 999 ) IS
       BEGIN
         IF lvl <= p_lvl THEN dbms_output.put_line(msg); END IF;
       END;

     # you can define perl hash araay as well
     msg = {
       101 => "msg 101",
       102 => "msg 102"
       }
     # you can use variable as well
     js_var = /my/js/var_file.js
     js_src = /my/first/js/prg.js,$js_var
     # a comma (,) after sharp (#) make it not a comment
     my_sql = select sid, serial#,username from v\$session;
     # you can use the ##include: to include more code files
     ##include: /my/codes/function_lib.sql

   This will create a hash array of

     $pr->{echo}  = proc ... end;
     $pr->{desc}  = "This is a long description about the value"
     $pr->{msg}   = {101=>"msg 101",102=>"msg 102"}
     $pr->{js_var}= "/my/js/var_file.js";
     $pr->{js_src}= "/my/first/js/prg.js,/my/js/var_file.js";

HISTORY
   *   Version 0.10

       This version ported from ora_jobs.pl on 07/17/2010.

   *   Version 0.20

         08/12/2010 (htu):
           1. added read_plsql, _parseline2
           2. modified expand_vars to use read_plsql
           3. modified exec_plsql to preload codes if the preload_code = 1

SEE ALSO (some of docs that I check often)
   Oracle::Loader, Oracle::Trigger, CGI::AppBuilder, File::Xcopy,
   CGI::AppBuilder::Message

AUTHOR
   Copyright (c) 2009 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)

POD ERRORS
   Hey! The above document had some coding errors, which are explained
   below:

   Around line 773:
       You forgot a '=back' before '=head1'

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.