[Bath-pm] Oracle SQL in Perl with unexpected results

andy at pasty.org.uk andy at pasty.org.uk
Sun Mar 20 02:25:42 PST 2005


Hi,

* Perl 5.6.1
* Oracle 8i

If I create an empty table (in SQL*Plus):

create table animals (
           a_dob date,
           a_name varchar2(10),
           a_cost number(9,2)
)

and execute the following three SQL statements which creates an sqlldr
control file:
select 'LOAD DATA'|| chr (10) ||
       'INFILE ''' || lower (table_name) || '.dat''' || chr (10) ||
       'INTO TABLE '|| table_name || chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS' || chr (10) || '('
from   user_tables
where  table_name = upper ('&tname');

select decode (rownum, 1, '   ', ' , ') ||
       rpad (column_name, 33, ' ')      ||
       decode (data_type,
           'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
           'FLOAT',    'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
           'NUMBER',   decode (data_precision, 0,
                       'INTEGER EXTERNAL NULLIF ('||column_name||
                       '=BLANKS)', decode (data_scale, 0,
                       'INTEGER EXTERNAL NULLIF ('||
                       column_name||'=BLANKS)',
                       'DECIMAL EXTERNAL NULLIF ('||
                       column_name||'=BLANKS)')),
           'DATE',     'DATE "&dformat" NULLIF
           ('||column_name||'=BLANKS)', null)from   user_tab_columns
where  table_name = upper ('&tname')
order  by column_id;

select ')'
from dual;
spool off

(from http://www.oracleutilities.com/OSUtil/sqlldr.html)

A control file, as expected, is returned.

If I take those same chunks of SQL, replace the &'s, and prepare/execute
them with DBI:
        my $SQL2 = <<__SQL2__;
select 'LOAD DATA' || chr(10) ||
       'INFILE ''$datafile''' || chr(10) ||
       'INTO TABLE '|| table_name || chr(10) ||
       'FIELDS TERMINATED BY '','''||chr(10) ||
       'TRAILING NULLCOLS' || chr(10) || '('
from   user_tables
where  table_name = upper ('$table')
__SQL2__

        my $SQL3 = <<__SQL3__;
select decode (rownum, 1, '   ', ' , ') ||
       rpad (column_name, 33, ' ')      ||
       decode (data_type,
           'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
           'FLOAT',    'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
           'NUMBER',   decode (data_precision, 0,
                       'INTEGER EXTERNAL NULLIF ('||column_name||
                       '=BLANKS)', decode (data_scale, 0,
                       'INTEGER EXTERNAL NULLIF ('||
                       column_name||'=BLANKS)',
                       'DECIMAL EXTERNAL NULLIF ('||
                       column_name||'=BLANKS)')),
           'DATE',     'DATE "DD/MM/YYYY" NULLIF
           ('||column_name||'=BLANKS)', null)from   user_tab_columns
where  table_name = upper ('$table')
order  by column_id
__SQL3__

        my $SQL4 = <<__SQL4__;
select ')'
from dual
__SQL4__

I only get the first column.

LOAD DATA
INFILE 'animals.dat'
INTO TABLE ANIMALS
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(   A_DOB                            DATE "DD/MM/YYYY" NULLIF (A_DOB=BLANKS))

What doesn't Perl like about the middle chunk of SQL?

Regards,

Andy




More information about the Bath-pm mailing list