I’ve tried that, too. I get the same error.
I’m wondering if somehow I’ve run into a bug.
What’s confusing is that I’ve done exactly the same thing on another 19.9
database without any issues as late as last week. That’s where I copied it
from.
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco@xxxxxxx<mailto:srcdco@xxxxxxx> | 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.
From: Jay Hostetter <hostetter.jay@xxxxxxxxx>
Sent: Thursday, April 29, 2021 11:13 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL*Loader Question
CAUTION: This message came from outside RIT. If you are unsure about the source
or content of this message, please contact the RIT Service Center at
585-475-5000 or help.rit.edu before clicking links, opening attachments or
responding.
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@xxxxxxx<mailto:srcdco@xxxxxxx>> 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@xxxxxxx<mailto:srcdco@xxxxxxx> | 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.
