NAME
   Oracle::SQL::Builder - Perl extension for building SQL statements.

SYNOPSIS
     use Oracle::SQL::Builder;

   No automatically exported routines. You have to specifically to import
   the methods into your package.

     use Oracle::SQL::Builder qw(:sql);
     use Oracle::SQL::Builder /:sql/;
     use Oracle::SQL::Builder ':sql';

DESCRIPTION
   This is a package containing common sub routines that can be used in
   other programs.

  new (%arg)
   Input variables:

     any input variable and value pairs

   Variables used or routines called:

     None

   How to use:

      my $obj = new Oracle::SQL;      # or
      my $obj = Oracle::SQL->new;     # or

   Return: new empty or initialized Oracle::SQL object.

 Export Tag: sql
   The *:table* tag includes sub-rountines for accessing Orable tables.

     use Oracle::SQL::Builder qw(:sql);

   It includes the following sub-routines:

  build_sql_stmt($idn,$idv,$hrf,$dft,$acm)
   Input variables:

     $idn - id/key name
     $idv - id/key value
     $hrf - hash ref with column definition. It is from
            getTableDef method
     $dft - date format. Default to 'YYYYMMDD.HH24MISS'
     $acm - add comma. If $acm = 1, then add a comma in
            the end.

   Variables used or routines called:

     fmtTime      - get current time

   How to use:

     my $cs  = 'usr/pwd@db';
     my $dbh = $self->getDBHandler($cs, "Oracle");
     my $tab = "test_table";
     my ($cns,$cd1,$hrf) = $self->getTableDef($dbh,$tab,'*','hash');
     my $dft = 'YYYYMMDD.HH24MISS';
     my $v   = $self->build_sql_stmt('dept',10,$hrf,$dft);

   Return: value string to be used in SQL statement.

   Any undef or 'null' value of $idv will be translated to '' for
   insert_records method and 'null' for update_records so that the DBI can
   handle correctly.

  build_sql_value($k,$v,$ar,$dft,$act)
   Input variables:

     $k   - column name
     $v   - column value
     $ar  - hash ref for column definition: ${$ar}{$k}{$itm}.
            It is from getTableDef with 'hash' type.
     $dft - date format.
            Default to 'YYYYMMDD.HH24MISS'? - not sure that we need
            to do that.
            It checks the dft in $ar for $k first;
            If not, then call id_datetime_format to get a format
            If not, then return undef.
     $act - action: update|insert

   Variables used or routines called:

     id_datetime_format - get date and time format based on
             the date and time value provided.

   How to use:

     my $cs  = 'usr/pwd@db';
     my $dbh = $self->getDBHandler($cs, "Oracle");
     my $tab = "test_table";
     my ($cns,$cd1,$ar) = $self->getTableDef($dbh,$tab,'*','hash');
     my $dft = 'YYYYMMDD.HH24MISS';
     my $v   = $self->build_sql_value('dept',10,$ar,$dft);

   Return: undef or value string to be used in SQL statement.

     undef  - value string can not be determined if no $k.
              Do not use the column in your SQL statement.
     'NULL' - null if $v is not defined and $v is not required.
     "''"   - empty string if $v is not defined and data type is CHAR
             or VARCHAR and NOT NULL.
     str    - any value string: number or quoted string

   This method returns the value with proper quotes and format string. For
   date datatype, it gets date and time format and use it in the TO_DATE
   function. If the $dft is provided or defined in the $ar for the column,
   then it convert the $v to the same format as defined in $dft if the $v
   has different date and time format.

  build_sql_operator($k,$v,$ar)
   Input variables:

     $k   - column name
     $v   - column value
     $ar  - hash ref for column definition: ${$ar}{$k}{$itm}.
            It is from getTableDef with 'hash' type.

   Variables used or routines called:

     None

   How to use:

     my $cs  = 'usr/pwd@db';
     my $dbh = $self->getDBHandler($cs, "Oracle");
     my $tab = "test_table";
     my ($cns,$cd1,$ar) = $self->getTableDef($dbh,$tab,'*','hash');
     my $v   = $self->build_sql_operator('dept',10,$ar);

   Return: SQL operator to be used in SQL statement.

     undef  - could not determine operator based on the inputs
              Do not use the column in your SQL statement.
     'LIKE' - match string with wild characters in $v.
     'IN'   - $v contains a list of values of string or number
              separated by comma.
     '='    - any number or quote strings

   This method returns SQL operator based on column data type and the value
   in $v.

  build_sql_where($str,$ar,$dft)
   Input variables:

     $str - a string with k1=v1,k2=v2,...
     $ar  - hash ref for column definition: ${$ar}{$k}{$itm}.
            It is from get_table_definition with 'hash' type.
     $dft - date format.
            Default to 'YYYYMMDD.HH24MISS'? - not sure that we need
            to do that.
            It checks the dft in $ar for $k first;
            If not, then call id_datetime_format to get a format
            If not, then return undef.

   Variables used or routines called:

     None

   How to use:

     my $cs  = 'usr/pwd@db';
     my $dbh = $self->getDBHandler($cs, "Oracle");
     my $tab = "test_table";
     my ($cns,$cd1,$ar) = $self->getTableDef($dbh,$tab,'*','hash');
     my $s = "id=1,ln=tu,fn=han";
     my $whr = $self->build_sql_where($s,$ar,$dft);

   Return: SQL WHERE clause

  form_sql($dbh,$arf,$rtp)
   Input variables:

     $dbh - database handler
     $arf - input array ref. It has the following elements:
       act - SQL action such as SELECT, UPDATE, DELETE, etc.
       tab - target table or view name
       cns - column names separated by comma
       where - condition array reference: ${$ar}[$i]{$itm}
            $i is condition index number
            $itm are:
            cn - column name
            op - operator such as =, <, >, in, lk, etc
            cv - value, or values separated by comma
            so - set operator such as AND or OR
       group_by - a list of columns separated by comma
       order_by - a list of columns separated by comma
       data - data array reference ${$ar}{$cn}
       dft - date format
       rwd - right column width for formating sql statement
     $rtp - return type: default - SQL statement string
       where    - just where clause
       hash     - hash array. It has
           table - table name
           cns  - column specification such as '*' or column names
           columns - column names. If '*', then all the column names.
           select/update/delete - actions
           from  - from a table
           where - where clause
           group_by - group by clause
           order_by - order by clause
           sql   - full SQL statement
       hash_ref - hash array reference pointing to the above hash
       sql      - the whole SQL statement

   Variables used or routines called:

     echoMSG      - echo message
     isObjExist   - check object existence
     getTableDef  - get table definitions
     getTableData - get table data

   How to use:

     my $cs  = 'usr/pwd@db';
     my $dbh = $self->getDBHandler($cs, "Oracle");
     my $drf = $self->getTableData($dbh,$srctab,'*','','hash');
     my $arf = bless {}, ref($self)||$self;
        ${$arf}{act} = 'SELECT';
        ${$arf}{tab} = 'test_tab';
        ${$arf}{cns} = 'id,name';
        ${$arf}{data} = $drf;
     my $tab = "test_table";
     $self->form_sql($dbh,$arf);

   Return: string, hash, hash ref based on return type.

  split_cns($str,$len,$chr,$nbk)
   Input variables:

     $str - string with words or column names separated by comma
            or by spliting character
     $len - length allow in a line, default to 65
     $chr - spliting character, default to comma
     $nbk - number of blank space in from of each line.
            If this is set, it will return a string with line breaks.

   Variables used or routines called:

     None

   How to use:

     my $cs  = 'col1, col2, col3, this, is, a multiple,line';
     my @a   = $self->split_cns($cs,10);

   Return: array with lines within length limit or a string.

  genWhere($so,$cn,$op,$cv,$ar,$dft)
   Input variables:

       $so  - set operator: AND, OR
       $cn  - column name
       $op  - operator: =, <=, >=, <>, lk, btw, in, nn, nl, etc.
       $cv  - column value
       $ar  - hash array ref: ${$ar}{$cn}{$itm}.
              $itm: col, typ, wid, max. dec, req, min, dft, and dsp
       $dft - date format
   Variables used or routines called:

     None

   How to use:

     my $whr = $self->build_where('','id','=',1);
        $whr .= $self->build_where('Or','name','lk','A');

   Return: string - where clause.

  run_sql($dbh,$sfn)
   Input variables:

       $dbh - datebase handler or connection string
              usr/pwd@db: for Oracle
       $sfn - sql file name with full path
       $hmd - home directory

   Variables used or routines called:

     None

   How to use:

     my $dbh = $self-?getDBHandler('usr/pwd@db');
     my $sfn = '/my/dir/sqls/crt1.sql';
        $self->run_sql($dbh, $sfn);

   Return: the following status codes:

     0 - ok;
     1 - no DB handler
     2 - inproper inputs
     3 - sql not found

AUTHOR
   Hanming Tu, [email protected]

SEE ALSO (some of docs that I check often)
   Oracle::Trigger, Oracle:DDL, Oracle::DML, Oracle::DML::Common,
   Oracle::Loader, etc.

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.