Oracle::SQL::Builder - Perl extension for building SQL statements.
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';
This is a package containing common sub routines that can be used in other programs.
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.
The :table tag includes sub-rountines for accessing Orable tables.
use Oracle::SQL::Builder qw(:sql);
It includes the following sub-routines:
$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.
fmtTime - get current time
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.
$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
id_datetime_format - get date and time format based on the date and time value provided.
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.
$k - column name $v - column value $ar - hash ref for column definition: ${$ar}{$k}{$itm}. It is from getTableDef with 'hash' type.
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.
$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.
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
$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
echoMSG - echo message isObjExist - check object existence getTableDef - get table definitions getTableData - get table data
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.
$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.
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.
$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
my $whr = $self->build_where('','id','=',1); $whr .= $self->build_where('Or','name','lk','A');
Return: string - where clause.
$dbh - datebase handler or connection string usr/pwd@db: for Oracle $sfn - sql file name with full path $hmd - home directory
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
Hanming Tu, hanming_tu@yahoo.com
Oracle::Trigger, Oracle:DDL, Oracle::DML, Oracle::DML::Common, Oracle::Loader, etc.
To install Oracle::SQL::Builder, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Oracle::SQL::Builder
CPAN shell
perl -MCPAN -e shell install Oracle::SQL::Builder
For more information on module installation, please visit the detailed CPAN module installation guide.