Re: SQL*Loader Question

Could you post your control file and an example of your file with data (just 2-3 rows is enough), please?
for example on pastebin or gist.github.com

On Thu, Apr 29, 2021 at 6:33 PM Scott Canaan <srcdco_at_rit.edu> wrote:

> I fixed that, too.
>
>
>
> *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.
>
>
>
> *From:* Sayan Malakshinov <xt.and.r_at_gmail.com>
> *Sent:* Thursday, April 29, 2021 11:31 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* Jay Hostetter <hostetter.jay_at_gmail.com>; ORACLE-L <
> oracle-l_at_freelists.org>
> *Subject:* Re: SQL*Loader Question
>
>
>
> >leavingdate "mm/dd/yyy" nullif leavingdate = 'NULL',
>
> >
>
> > ^
>
> yyy? Not yyyy?
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
>
>
> On Thu, Apr 29, 2021, 18:28 Scott Canaan <srcdco_at_rit.edu> wrote:
>
> 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_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.
>
>
>
> *From:* Jay Hostetter <hostetter.jay_at_gmail.com>
> *Sent:* Thursday, April 29, 2021 11:13 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* oracle-l_at_freelists.org
> *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 <http://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_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.
>
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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