External Table Query runs in PL/SQL but fails in perl script

[tundal45]

Hey Guys,

I am trying to automate the process of loading data in our oracle server. As a part of that process, I am working on a perl script that loads external tables from data files. What I am seeing is that I get following error when I select from external table created using script.

ORA-29913: error in executing ODCIEXTTABLEOPE N callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier ": expecting one of: "number, plussign"
KUP-01008: the bad identifier was p8
KUP-01007: at line 7 column 23
ORA-06512: at "SYS.ORACLE_LOA DER", line 19

The thing is that if print out the query string to a text file and run the query inside PL/SQL, the external table is created and I can select from it without any errors. its only when perl executes the query that the above error rears its ugly head.

The code in the script is:

$str_sql = "CREATE TABLE acnhs.bi_load_$year"."$quarter                 (                   cust_num varchar(255),                   date_id varchar(255),                   outlet_cde varchar(255),                   upc_cde varchar(255),                   units varchar(255),                   net_dlr varchar(255),                   ret_disc varchar(255),                   mfr_disc varchar(255)                   )                 ORGANIZATION external                  (                   TYPE oracle_loader                   DEFAULT DIRECTORY acn_load_test                   ACCESS PARAMETERS                    (                     RECORDS DELIMITED BY 0X\'0A\'                     READSIZE 1048576                     FIELDS LRTRIM                     MISSING FIELD VALUES ARE NULL                      REJECT ROWS WITH ALL NULL FIELDS                      (                       cust_num (1:8),                       date_id (9:14),                       outlet_cde (15:18),                       upc_cde (19:30),                       units (31:32),                       net_dlr (33:37),                       ret_disc (38:41),                       mfr_disc (42:45)                     )                   )                   location                    ($file_string)                 )REJECT LIMIT UNLIMITED";

The SQL generated by the script is:

CREATE TABLE acnhs.bi_load_08Q2                 (                   cust_num varchar(255),                   DATE_ID varchar(255),                   outlet_cde varchar(255),                   UPC_cde varchar(255),                   units varchar(255),                   net_dlr varchar(255),                   ret_disc varchar(255),                   mfr_disc varchar(255)                   )                 ORGANIZATION external                  (                   TYPE oracle_loader                   DEFAULT DIRECTORY acn_load_test                   ACCESS PARAMETERS                    (                     RECORDS DELIMITED BY 0X'0A'                     READSIZE 1048576                     FIELDS LRTRIM                     MISSING FIELD VALUES ARE NULL                      REJECT ROWS WITH ALL NULL FIELDS                      (                       cust_num (1:8),                       DATE_ID (9:14),                       outlet_cde (15:18),                       UPC_cde (19:30),                       units (31:32),                       net_dlr (33:37),                       ret_disc (38:41),                       mfr_disc (42:45)                     )                   )                   location                    ('PD_1','PD_2')                 )REJECT LIMIT UNLIMITED

Any thoughts as to what I could be doing wrong?

Thanks,

Ashish

[debasisdas]

please find a related article here.

[tundal45]

Thanks for the reply. I guess I was not clear in my last post. I have the SQL code for the external table that works fine if I run it manually in PL/SQL. What I am trying to do is to automate the process and run the query through perl and for some reason the same query (that runs in Pl/SQL) causes an error (described in the previous post) when executed using Perl. Does anyone know why this is happening? (the Perl code that I use can be found in my previous post as well)

[amitpatel66]

Thanks for the reply. I guess I was not clear in my last post. I have the SQL code for the external table that works fine if I run it manually in PL/SQL. What I am trying to do is to automate the process and run the query through perl and for some reason the same query (that runs in Pl/SQL) causes an error (described in the previous post) when executed using Perl. Does anyone know why this is happening? (the Perl code that I use can be found in my previous post as well)

What is the error that it is throwing?