Geo Tiger

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, hanming_tu@yahoo.com

SEE ALSO (some of docs that I check often)

Oracle::Trigger, Oracle:DDL, Oracle::DML, Oracle::DML::Common, Oracle::Loader, etc.