Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: ORA-01858: when using sqlloader

    Hello to all.
    I'm using Oracle RDBMS 11.2.0.3.0 on RH Linux

    My goal is to load a text file to a database table using sqlloader. I have the following ctl file:
    Code:
    LOAD DATA
    INFILE 'export.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
    )
    This are the first lines of the file:
    Code:
    60000001;KR;40;86063;2014/01;01-Oct-13;23-Oct-13;13/618;364.79;ZN;P010010030;0010 030;;121587;13/618;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;40;86064;2014/01;04-Oct-13;23-Oct-13;13/633;166.09;ZN;P010010030;0010 030;;121587;13/633;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;40;86065;2014/01;10-Oct-13;23-Oct-13;13/646;220.4;ZN;P010010030;0010 030;;121587;13/646;;;COMSUCOPIA  - Toner;PV01;
    Sqloader is failing with the following error:
    Code:
    Record 1: Rejected - Error on table SAP_REP_MOVS, column DOC_DATE.
    ORA-01858: a non-numeric character was found where a numeric was expected
    It seem is failing on the first DATE field, which i cannot understand because ctl file clearly indicates that is a DATE field (and there is a date on the sixth column of the file)
    NLS_DATE_FORMAT database parameter is set to DD-MON-RR.
    This a Describe of the table i'm loading:
    Code:
    SQL> desc APPS.SAP_REP_MOVS;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     CONTA                                              VARCHAR2(20)
     TIPODOC                                            VARCHAR2(6)
     POSTKEY                                            VARCHAR2(10 CHAR)
     DOCNR                                              VARCHAR2(20 CHAR)
     YEAR_MONTH                                         VARCHAR2(20)
     DOC_DATE                                           DATE
     PSTG_DATE                                          DATE
     REFERENCE                                          VARCHAR2(12 CHAR)
    Can someone please give an idea of what am i doing wrong?
    Thanks in advance for any kind help.
    Octavio

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2007
    Posts
    65
    Anacedent, thank you for your answer and sorry for bothering you again.
    I have been looking to the answers in your link but was unable to find a solution.
    Is my ctl file wong?
    Thank you.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2007
    Posts
    65
    Sorry but i don't understand. I have masked the date field in any possible ways but i'm allways getting error.I am new to this but i have other ctl files that load dates without any masking and they work perfectly...
    The sort of things only happen to me...

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    in your ctl file use

    doc_date DATE "DD-MON-RR"
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2007
    Posts
    65
    Hi beilstwh,
    Thank you for answering. I also tried that but the error still persists:
    Now on ctl file:

    Code:
    ,DOC_DATE       DATE "DD-MON-RR"
    ,PSTG_DATE      DATE "DD-MON-RR"
    Code:
    Record 1: Rejected - Error on table SAP_REP_MOVS, column DOC_DATE.
    ORA-01858: a non-numeric character was found where a numeric was expected
    I'm getting desperate. I need to deliver the table...

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't have your table; as you didn't provide its description, I created one by myself:
    Code:
    SQL> create table sap_rep_movs
      2  (conta      varchar2(20),
      3   tipodoc    varchar2(20),
      4   postkey    varchar2(20),
      5   docnr      varchar2(20),
      6   year_month varchar2(20),
      7   doc_date   date,
      8   pstg_date  date,
      9   reference  varchar2(20),
     10   amount     number,
     11   vatcode    varchar2(20),
     12   ccenter    varchar2(20),
     13   assign     varchar2(20),
     14   customer   varchar2(20),
     15   vendor     varchar2(20),
     16   refkey1    varchar2(20),
     17   refkey2    varchar2(20),
     18   refkey3    varchar2(20),
     19   text       varchar2(20),
     20   company    varchar2(20)
     21  );
    
    Table created.
    
    SQL>
    Now, a control file: pay attention to conversions I have made. I also put sample data into the control file (see INFILE and BEGINDATA); you don't have to do that, I was just lazy to create additional file in the directory.
    Code:
    LOAD DATA
    INFILE *
    APPEND
    INTO TABLE SAP_REP_MOVS
    FIELDS TERMINATED BY ';'
    TRAILING NULLCOLS
    (
    CONTA           CHAR
    ,TIPODOC        CHAR
    ,POSTKEY        CHAR
    ,DOCNR          CHAR
    ,YEAR_MONTH     CHAR
    ,DOC_DATE       "to_date(:doc_date, 'dd-mon-yy', 'nls_date_language = english')"
    ,PSTG_DATE      "to_date(:pstg_date, 'dd-mon-yy', 'nls_date_language = english')"
    ,REFERENCE
    ,AMOUNT         "to_number(:amount, '999.99')"
    ,VATCODE
    ,CCENTER
    ,ASSIGN
    ,CUSTOMER
    ,VENDOR
    ,REFKEY1
    ,REFKEY2        CHAR
    ,REFKEY3
    ,TEXT
    ,COMPANY        CHAR
    )
    
    begindata
    60000001;KR;40;86063;2014/01;01-Oct-13;23-Oct-13;13/618;364.79;ZN;P010010030;0010 030;;121587;13/618;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;40;86064;2014/01;04-Oct-13;23-Oct-13;13/633;166.09;ZN;P010010030;0010 030;;121587;13/633;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;40;86065;2014/01;10-Oct-13;23-Oct-13;13/646;220.4;ZN;P010010030;0010 030;;121587;13/646;;;COMSUCOPIA  - Toner;PV01;
    OK, let's try it:
    Code:
    C:\Users\lf>sqlldr scott/tiger@ora10 control=test6.ctl log=test6.log
    
    SQL*Loader: Release 11.2.0.2.0 - Production on Pet Srp 18 12:57:00 2014
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Commit point reached - logical record count 2
    Commit point reached - logical record count 3
    
    C:\Users\lf>
    Excerpt from the log file:
    Code:
    Table SAP_REP_MOVS:
      3 Rows successfully loaded.
      0 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      0 Rows not loaded because all fields were null.
    Select:
    Code:
    SQL> select year_month, doc_date, pstg_date, amount
      2  from sap_rep_movs;
    
    YEAR_MONTH           DOC_DATE   PSTG_DATE      AMOUNT
    -------------------- ---------- ---------- ----------
    2014/01              01.10.2013 23.10.2013     364,79
    2014/01              04.10.2013 23.10.2013     166,09
    2014/01              10.10.2013 23.10.2013      220,4
    
    SQL>
    Seems to be OK, eh?

  9. #9
    Join Date
    Jun 2007
    Posts
    65
    Littlefoot, thanks a million for your patience.
    As i have large amounts i changed your AMOUNT line to

    Code:
    ,AMOUNT         "to_number(:amount, '999999999999.99')"
    But i'm still getting:
    Code:
    Record 1: Rejected - Error on table SAP_REP_MOVS, column AMOUNT.
    ORA-01722: invalid number
    I'm going to throw myself out of the window

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Could you post sample data (one record would be enough) that reflects what you are saying?

  11. #11
    Join Date
    Jun 2007
    Posts
    65
    Sure, here it is (sorry to bother you again):

    Code:
    60000001;KR;40;86063;2014/01;01-Oct-13;23-Oct-13;13/618;364.79;ZN;P010010030;0010 030;;121587;13/618;;;COMSUCOPIA  - Toner;PV01;

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    it sounds like you have bad data in the file. add the following clause into your control file. this will save every bad record in the flat file export.bad

    LOAD DATA INFILE 'export.txt'
    BADFILE 'export.bad'
    APPEND INTO TABLE SAP_REP_MOVS FIELDS TERMINATED BY ';' TRAILING NULLCOLS
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Jun 2007
    Posts
    65
    Here's the bad data (just 3 lines).
    It seem like the original one....

    Code:
    60000001;KR;40;86063;2014/01;01-Oct-13;23-Oct-13;13/618;364.79;ZN;P010010030;0010 030;;121587;13/618;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;40;86064;2014/01;04-Oct-13;23-Oct-13;13/633;166.09;ZN;P010010030;0010 030;;121587;13/633;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;40;86065;2014/01;10-Oct-13;23-Oct-13;13/646;220.4;ZN;P010010030;0010 030;;121587;13/646;;;COMSUCOPIA  - Toner;PV01;
    60000001;KR;50;592995;2014/01;01-Oct-13;23-Oct-13;13/618;-364.79;;P010010030;0010 030;;;13/618;;;Corrección Cebe:0000086063;PV01;

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @oteixeira, we already have that line. I was hoping for the one that contains an invalid number. Actually, save existing input file somewhere else (for backup), and remove all but a single line in EXPORT.TXT. Then run loading again. What happens?

    I suspect that group and decimal characters matter. What are they set to now? Run
    Code:
    SQL> select * from nls_session_parameters
      2  where parameter = 'NLS_NUMERIC_CHARACTERS';
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_NUMERIC_CHARACTERS         ,.
    
    SQL>
    and let us know the outcome.

    @Bill, BAD file is created anyway (at least, in my environment).

  15. #15
    Join Date
    Jun 2007
    Posts
    65
    Littlefoot, i followed your instructions and left a single line in EXPORT.TXT (i picked a line from the middle of the file, which has almoust 300000 lines).
    The result was the same:
    Code:
    Record 1: Rejected - Error on table SAP_REP_MOVS, column AMOUNT.
    ORA-01722: invalid number
    Result from query:
    [CODE]SQL> select * from nls_session_parameters
    2 where parameter = 'NLS_NUMERIC_CHARACTERS';

    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_NUMERIC_CHARACTERS .,[CODE]

Posting Permissions

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