The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.


CGI::AppBuilder::PLSQL - Oracle PL/SQL Procedures


  use CGI::AppBuilder::PLSQL;

  my $sec = CGI::AppBuilder::PLSQL->new();
  my ($sta, $msg) = $sec->exe_sql($ar); 


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.


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:


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);
  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:

    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
      IF lvl <= p_lvl THEN dbms_output.put_line(msg); END IF;

  # 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";


  • Version 0.10

    This version ported from 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


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

1 POD Error

The following errors were encountered while parsing the POD:

Around line 773:

You forgot a '=back' before '=head1'