Re: SQLLDR Issue - again

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 21 Sep 1999 21:21:28 GMT
Message-ID: <37ecf6d2.7994293@news.arnes.si>

On Tue, 21 Sep 1999 15:17:19 GMT, amerar_at_ci.chi.il.us wrote:

>
>
>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?

So called "SQL strings" (ie expressions in double quotes at the end of the columns specification) are not allowed when SQL*Loader is run with direct option. In your case, you can't use a "DECODE ...." stuff if you don't switch the direct mode off.

Beware, even if you load with this ctl file in the conventional path mode, your DATE_BILLING column will contain only NULLs, since your DECODE expression is not logical. I believe what you want is this:

"decode(DATE_BILLING, '00000000' null,

                      '        ', null, date_billing)"
                                      ^^^^^^^^^^^^^^

>Thanks,
>
>Arthur
>amerar_at_ci.chi.il.us

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Sep 21 1999 - 16:21:28 CDT