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> 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 <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:45 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
>
>
>
> 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
>

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


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