RE: SQL*Loader Question

From: Scott Canaan <srcdco_at_rit.edu>
Date: Thu, 29 Apr 2021 16:37:58 +0000
Message-ID: <64340605b17e44cfb5aee8916d207a06_at_ex04test91a.ad.rit.edu>

Yep, that did it. I wonder why I didn’t have to do that on the other database? Oh well, I’m past that issue now!

Thank you,

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: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Sayan Malakshinov Sent: Thursday, April 29, 2021 12:12 PM 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

I've just created a simple short example: https://gist.github.com/xtender/b6d05e380125afbd90cd72c08c958ff2 You just need to add 'date':
program_effective_date DATE 'mm/dd/yyyy', leavingdate DATE 'mm/dd/yyyy' nullif leavingdate = 'NULL',

On Thu, Apr 29, 2021 at 6:48 PM Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> wrote: Here’s the control file:

load data
infile 'sis_import_data.csv'
truncate
into table coopeval_owner.sis_import
fields terminated by "," optionally enclosed by '"' trailing nullcols
(sterm,
rit_uid,
lastname,
firstname,
middlename nullif middlename = 'NULL',
gender,
year_level,
degree nullif degree = 'NULL',

primary_program,
student_career_number,
program_effective_date 'mm/dd/yyyy',

acadcareer,
program,
programstatus,
programaction,
leavingdate 'mm/dd/yyyy' nullif leavingdate = 'NULL', reasonleaving nullif reasonleaving = 'NULL', plan,
plantype,
subplan nullif subplan = 'NULL',
subplantype nullif subplantype = 'NULL', planorg,
planorgdesc,
academicgroup,
registered,
regcoop,
class nullif class = 'NULL',
unitsinprogress,
unitsinfa_or_gpa,
academicload,
faload,
dce nullif dce = 'NULL',
preferred_email nullif preferred_email = 'NULL', citizenship1 nullif citizenship1 = 'NULL', citizenshipstatus1 nullif citizenshipstatus1 = 'NULL', citizenship2 nullif citizenship2 = 'NULL', citizenshipstatus2 nullif citizenshipstatus2 = 'NULL', ntidsupported,
gpa,
cum_gpa,
certificationdate 'mm/dd/yyyy' nullif certificationdate = 'NULL', completion_term nullif completionterm = 'NULL', expectedgradterm nullif expectedgradterm = 'NULL', degree_checkout_status nullif degree_checkout_status = 'NULL', waived,
ethnicity,
coopregistrationdate 'mm/dd/yyyy' nullif coopregistrationdate = 'NULL', advisorlastname nullif advisorlastname = 'NULL', advisorfirstname nullif advisorfirstname = 'NULL', advisorid nullif advisorid = 'NULL',
advisoremail nullif advisoremail = 'NULL', facadvisorlastname nullif facadvisorlastname = 'NULL', facadvisorfirstname nullif facadvisorfirstname = 'NULL', facadvisorid nullif facadvisorid = 'NULL', facadvisoremail nullif facadvisoremail = 'NULL', ntidsupportfacadvisorlastname nullif ntidsupportfacadvisorlastname = 'NULL', ntidsupportfacadvisorfirstname nullif ntidsupportfacadvisorfirstname = 'NULL', ntidsupportfacadvisorid nullif ntidsupportfacadvisorid = 'NULL', ntidsupportfacadvisoremail nullif ntidsupportfacadvisoremail = 'NULL', campus,
numcoops)

I can’t give you the data as there is PII included.

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<mailto: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<mailto:xt.and.r_at_gmail.com>> Sent: Thursday, April 29, 2021 11:45 AM To: Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> Cc: Jay Hostetter <hostetter.jay_at_gmail.com<mailto:hostetter.jay_at_gmail.com>>; ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: 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<http://gist.github.com>

On Thu, Apr 29, 2021 at 6:33 PM Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> wrote: I fixed that, too.

From: Sayan Malakshinov <xt.and.r_at_gmail.com<mailto:xt.and.r_at_gmail.com>> Sent: Thursday, April 29, 2021 11:31 AM To: Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> Cc: Jay Hostetter <hostetter.jay_at_gmail.com<mailto:hostetter.jay_at_gmail.com>>; ORACLE-L <oracle-l_at_freelists.org<mailto: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<mailto: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.

From: Jay Hostetter <hostetter.jay_at_gmail.com<mailto:hostetter.jay_at_gmail.com>> Sent: Thursday, April 29, 2021 11:13 AM To: Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> Cc: oracle-l_at_freelists.org<mailto: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<mailto: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?

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

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

Received on Thu Apr 29 2021 - 18:37:58 CEST

This message: [ Message body ]
Next message: Mark W. Farnham: "RE: fragmented sysauth$ table"
Previous message: Sayan Malakshinov: "Re: SQL*Loader Question"
In reply to: Sayan Malakshinov: "Re: SQL*Loader Question"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message