Re: SQL*Loader Question

Scott,

  Perhaps the double quotes need to be around the word "NULL" instead? I think you still want single-quotes around the date mask.

Jay

On Thu, Apr 29, 2021 at 9:55 AM Scott Canaan <srcdco_at_rit.edu> wrote:

> I am trying to load data extracted from a SQL Server database. In the SQL
> Server, all fields are defined as varchar(50), even if they are dates or
> numbers. In the Oracle database, I have created the table with the
> appropriate data types. The problem I’m having is with the date fields.
> If they are null, then the word NULL is in the field in the extract (a .csv
> file).
>
>
>
> I’ve run into this before and I had no problem using the following in the
> SQL*Loader control file:
>
>
>
> leavingdate 'mm/dd/yyyy' nullif leavingdate = 'NULL',
>
>
>
> When I try this on the extract file, I get:
>
>
>
> SQL*Loader-308: Optional SQL string of column PROGRAM_EFFECTIVE_DATE must
> be in double quotes.
>
>
>
> If I put double quotes around the mm/dd/yyyy, I get:
>
>
>
> SQL*Loader-350: Syntax error at line 22.
>
> Expecting "," or ")", found keyword nullif.
>
> leavingdate "mm/dd/yyy" nullif leavingdate = 'NULL',
>
> ^
>
>
>
> This is Oracle 19.9.
>
>
>
> What am I doing wrong?
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>

--
http://www.freelists.org/webpage/oracle-l