Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Red face Unanswered: ORA-01861 Errors with SQL Loader

    I've been trying to figure out and also did searches on e net n forum abt this error but no success.

    Error is:

    Record 1: Rejected - Error on table STG_SALES_LEADS, column APPT_START.
    ORA-01861: literal does not match format string

    This is what i've stated in e CTL file:

    LOAD DATA INFILE 'someinfile.txt'
    APPEND INTO TABLE stg_sales_leads
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    ......,
    .....,
    column2 ,
    appt_start DATE "dd-mm-yyyy" "to_date(:appt_start,'dd-mm-yyyy')",
    .....,
    .....
    )

    pls help!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just an idea ... what happens if you have such a line in your control file:
    appt_start CHAR "to_date(:appt_start,'dd-mm-yyyy')",

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51
    No No...
    Change the length or insert in the default mode and then change it

    use something like below
    ACCT_DATE POSITION(206:215) DATE "YYYY+MM+DD"
    in your control file if your data is like 2004+10+01

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    please post some lines of the data you are trying to load.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2004
    Posts
    2

    Exclamation

    thanks for the replies!

    i've made the changes to the ctl file to this way:

    load data...
    .........
    appt_start DATE "yyyy-mm-dd hh24:mi:ss",
    ........

    but now i'm having problems with columns data that have MILLISECONDS.

    Should i trim the source data to just include seconds only? any other ways??

    these r e sample data i'm loading into...

    2002-06-18 17:42:41.060
    2002-06-18 14:00:00.000
    2002-02-05 11:30:00.000


    it's in yyyy-mm-dd hh24:mm:ss --> then what's after???

    thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •