Re: SQLLDR Question

In article <7s87k2$fee$1_at_nnrp1.deja.com>,   amerar_at_ci.chi.il.us wrote:
>
>
> I am trying to have a dual condition on a field when loading it using
a
> control file.
>
> Ok, the file is not a comma delimited file. It is fixed with and
fixed
> column. Here is a cut from my control file:
>
> DATE_BILLING POSITION(118:125) DATE(8) "YYYYMMDD" NULLIF (DATE_BILLING
> = " "),
>
> I changed it to this:
>
> DATE_BILLING POSITION(118:125) DATE(8) "YYYYMMDD" "decode(DATE_BILL
> ING, '00000000' null, ' ', null)",
>
> I got this error message:
>
> SQL*Loader-417: SQL string (on column DATE_BILLING) not allowed in
> direct path.
>
> Any ideas?
>
> Thanks,
>
> Arthur
> amerar_at_ci.chi.il.us
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>
Hello Arthur,

SQL*Loader provides two methods for loading data:

• conventional path load
• direct path load

When using direct load path method (parameter direct=true) it is not allowed to apply any "SQL string" to data fields. This is what the error message -417 is telling us.

But you can use SQL*Loader keywords and according to oracle docs: - A column may have both a NULLIF clause and a DEFAULTIF - Using DEFAULTIF on character data (CHAR, DATE, or numeric EXTERNAL) sets the column to null.

You could apply your "dual condition" in direct load path method by using SQL*Loader keywords as follows:

DATE_BILLING POSITION(118:125)

             DATE 'YYYYMMDD'
             NULLIF    DATE_BILLING = blanks
             defaultif DATE_BILLING = '00000000',

This should achieve the same effect as your control file entry:

DATE_BILLING POSITION(118:125)

             DATE(8) "YYYYMMDD"
             "decode(DATE_BILLING, '00000000' null, '        ', null)",

when using conventional path load.

Hope this will help,

Ioan Iacobet

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Sep 21 1999 - 23:43:04 CEST