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.