This discussion is archived
2 Replies Latest reply: Feb 11, 2013 1:48 AM by BluShadow RSS

sqlldr date at when clause

carmac Newbie
Currently Being Moderated
Hi all,

i need to create a conditional loader script in sqlldr.

i write ctl script like this
LOAD DATA
INFILE 'COMSUMER.txt'
BADFILE 'COMSUMER.bad'
DISCARDFILE 'COMSUMER.dsc'
APPEND
INTO TABLE "RBS_CC_CUSTOMERINFO"
WHEN CODE = '0' and PROC_DT < '01/01/2009'  
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
PROC_DT  date "MM/DD/YYYY",
----
-----
---
)
I got the error like this

SQL*Loader-350: Syntax error at line 4.
Illegal combination of non-alphanumeric characters WHEN PROC_DT < '01/01/2009' and CODE = '0'

Please help me

Edited by: carmac on Feb 11, 2013 2:56 PM

Edited by: carmac on Feb 11, 2013 2:57 PM

Edited by: BluShadow on 11-Feb-2013 09:38
added {noformat}
{noformat} tags for readability.  Please read {message:id=9360002} and learn to do this yourself.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 1. Re: sqlldr date at when clause
    jeneesh Guru
    Currently Being Moderated
    If you can move the file to be loaded to the DB server, use External Table..It is more powerful..
  • 2. Re: sqlldr date at when clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Ok, for starters:
    PROC_DT &lt; '01/01/2009'  
    when you compare DATE datatypes you should not use strings.

    In SQL or PL/SQL code such a date comparison would be something like:
    PROC_DT &lt; to_date('01/01/2009','DD/MM/YYYY')
    Personally, I'd consider using External Tables instead, and then apply your conditions on the SQL that queries the data from the External Table. Much more flexible and easy to do...

    http://www.morganslibrary.org/reference/externaltab.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points