/ 
Oracle-SQL-Builder-0.01
River stage zero No dependents
/ Oracle::SQL::Builder

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.