[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