Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: SQLLOADER Error ORA-01861

    Hello to all.
    I have the following ctl file:
    Code:
    LOAD DATA
    INFILE 'test_tab_sep.txt'
    APPEND
    INTO TABLE SAP_REP_MOVS
    FIELDS TERMINATED BY ';'
    TRAILING NULLCOLS
    (
    CONTA    CHAR
    ,TIPODOC    CHAR
    ,POSTKEY CHAR
    ,DOCNR CHAR
    ,YEAR_MONTH     CHAR
    ,DOC_DATE       DATE
    ,PSTG_DATE      DATE
    ,REFERENCE
    ,AMOUNT         DECIMAL EXTERNAL
    ,VATCODE
    ,CCENTER
    ,ASSIGN
    ,CUSTOMER
    ,VENDOR
    ,REFKEY1
    ,REFKEY2        CHAR
    ,REFKEY3
    ,TEXT
    ,COMPANY        CHAR
    )
    To load the following data:

    Code:
    60000001;KR;40;;86063;2014/01;01-10-2013;23-10-2013;13/618;             364.79;ZN;P010010030;0010 030;;121587;13/618;;;COMSUCOPIA  - Toner;;PV01
    60000001;KR;40;;86064;2014/01;04-10-2013;23-10-2013;13/633;             166.09;ZN;P010010030;0010 030;;121587;13/633;;;COMSUCOPIA  - Toner;;PV01
    60000001;KR;40;;86065;2014/01;10-10-2013;23-10-2013;13/646;             220.40;ZN;P010010030;0010 030;;121587;13/646;;;COMSUCOPIA  - Toner;;PV01
    60000001;KR;50;;592995;2014/01;01-10-2013;23-10-2013;13/618;-364.79;;P010010030;0010 030;;;13/618;;;Corrección Cebe:0000086063;;PV01
    The log is showing the following error:

    Code:
    Record 1: Rejected - Error on table SAP_REP_MOVS, column DOC_DATE.
    ORA-01861: literal does not match format string
    I tried changing ctl file to:

    Code:
    DOC_DATE       DATE "DD-MM-YYYY"
    but the error persists.

    I have similar data and this error does not appear. Can someone please give some help? Thanks in advance for any kind help.

    I'm using RDBMS 11.2.0.3.0 on RH Linux EL5.8

    Also checked MLS database parameters and saw that NLS_DATE_FORMAT is set to DD-MON-RR so change the dates on the data to be loaded to format XX-Oct-13

    Still receiving the same error.


    Thank you
    Octavio
    Last edited by oteixeira; 07-17-14 at 06:06.

  2. #2
    Join Date
    Jun 2007
    Posts
    65
    Answering to myself.
    I had an extra column on the datafile. Nothing is wrong with ctl...but something is wrong with my eyes

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Does it mean that you fixed it? (I believe so).

    Anyway, this:
    Code:
    2014/01
    is supposed to be DOC_DATE whose datatype is DATE. Of course, "2014/01" is not date, it is a string so you have to decide what to do with it - convert to date (using TO_DATE with appropriate format mask), or load that value into a VARCHAR2 column.

  4. #4
    Join Date
    Jun 2007
    Posts
    65
    Littlefoot, thank you for responding.
    Yes, i fixed it. But now is the AMOUNT column which is being rejected with:
    Code:
    ORA-01722: invalid number
    Should i use something different from DECIMAL EXTERNAL?
    Thank you.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check another topic you opened (by the way, that's a bad practice - you are solving the same problem, but discussing it in two topics. That creates mess and doesn't help at all).

Posting Permissions

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