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.