Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQLLDR - how to set a default value for an empty string

643707Feb 9 2009 — edited Feb 9 2009

I'm using SQLLDR to load data from a flat file on Oracle 10g 10.2.0.4 EE, Linux RHEL 4 update 6. Seems like my question is simple, but darned if I can't find the solution.

My source file (comma delimited extracted form a different RDBMS) has some (not all) rows where the output for a string is "", and not NULL. How do I check for empty string and add a default value in the control file? The target column is non-nullable

Here's my current control file - however, the column attributeName where sometimes the source record is "" does not get evaluated as NULL.

load data
 infile 'mydata.del'
 badfile 'mydata.bad'
 discardfile 'mydata.dsc'
 into table mydata
 fields terminated by "," optionally enclosed by '"'
   TRAILING NULLCOLS		  
(l_metadata,
company,
className,
attributeName "decode(:vc_attributeName,null, 'none')",
attributeDefault)
{code}

When I tried decode with "", I received the following error:


{code}SQL*Loader-350: Syntax error at line 11.
Expecting "," or ")", found ", 'none')".
attributeName "decode(:vc_attributeName,"", 'none')", {code}

This does not work either:

{code}
load data
 infile 'mydata.del'
 badfile 'mydata.bad'
 discardfile 'mydata.dsc'
 into table mydata
 fields terminated by "," optionally enclosed by '"'
   TRAILING NULLCOLS		  
(l_metadata,
company,
className,
attributeName NULLIF attributeName = BLANKS ,"decode(:vc_attributeName,null, 'none')",
attributeDefault)

I get the following in the log:

Table COMPANYMETADATA, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
L_METADATA                          FIRST     *   ,  O(") CHARACTER
COMPANY                            NEXT     *   ,  O(") CHARACTER
CLASSNAME                         NEXT     *   ,  O(") CHARACTER
ATTRIBUTENAME                     NEXT     *   ,  O(") CHARACTER
    NULL if ATTRIBUTENAME = BLANKS
    SQL string for column : "decode(:attributeName,null, 'none')"
ATTRIBUTEDEFAULT                  NEXT     *   ,  O(") CHARACTER

Record 1: Rejected - Error on table COMPANYMETADATA, column ATTRIBUTENAME.
ORA-01400: cannot insert NULL into ("SRV5"."COMPANYMETADATA"."ATTRIBUTENAME")

{code}

Any help is appreciated -

Edited by: kpw on Feb 9, 2009 11:10 AM