Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2010
    Posts
    29

    loading txt file from sql loader to oracle database

    Hello All,

    i am trying to load data but i am getting errors:


    here is my table structure:

    create table LINECVG_LOOKUP
    (L2COCD NUMBER(2),
    L2SABB CHAR(2BYTE),
    L2PRCD CHAR(3BYTE),
    L2RRDT DATE,
    L2CVCD VARCHAR2(6BYTE),
    SSLNCV NUMBER(4)
    );


    Sample data:

    L2COCD L2SABB L2PRCD L2RRDT L2CVCD SSLNCV
    2 AL ATC 10/13/1997 ADDEQ 2100
    2 AL ATC 10/13/1997 BI 1901
    2 AL ATC 10/13/1997 COLL 2103
    2 AL ATC 10/13/1997 COMP 2101
    2 AL ATC 10/13/1997 MEDPAY 2801
    2 AL ATC 10/13/1997 PD 2001


    My control fiel:

    LOAD DATA
    INFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.txt'
    BADFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.bad'
    DISCARDFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.dsc'

    INTO TABLE "DATUSER"."LINECVG_LOOKUP"
    TRUNCATE
    FIELDS TERMINATED BY ' '
    OPTIONALLY ENCLOSED BY '"'
    (L2COCD ,

    L2SABB ,

    L2PRCD ,

    L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YY')",

    L2CVCD ,

    SSLNCV )
    ---------------------------------------------------------------

    when i am trying to load data i a getting error:


    Record 1: Rejected - Error on table "DATUSER"."LINECVG_LOOKUP", column L2SABB.
    Column not found before end of logical record (use TRAILING NULLCOLS)
    Record 2: Rejected - Error on table "DATUSER"."LINECVG_LOOKUP", column L2SABB.
    Column not found before end of logical record (use TRAILING NULLCOLS)





    Can anybody suggest me what the error might be?





    Thanks
    Hasya

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    Works fine for me.

    A control file:
    Code:
    options (skip = 1)
    load data
    infile *
    replace
    INTO TABLE LINECVG_LOOKUP
    TRUNCATE
    FIELDS TERMINATED BY ' '
    (L2COCD ,
     L2SABB ,
     L2PRCD ,
     L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YYYY')",
     L2CVCD ,
     SSLNCV 
    )
    
    begindata
    L2COCD L2SABB L2PRCD L2RRDT L2CVCD SSLNCV
    2 AL ATC 10/13/1997 ADDEQ 2100
    2 AL ATC 10/13/1997 BI 1901
    2 AL ATC 10/13/1997 COLL 2103
    2 AL ATC 10/13/1997 COMP 2101
    2 AL ATC 10/13/1997 MEDPAY 2801
    2 AL ATC 10/13/1997 PD 2001
    Loading session:
    Code:
    SQL> $sqlldr scott/tiger control=test1.ctl log=test1.log
    
    SQL*Loader: Release 11.2.0.2.0 - Production on Pet Svi 4 19:49:13 2012
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Commit point reached - logical record count 5
    Commit point reached - logical record count 6
    
    SQL>
    The result:
    Code:
    SQL> select * from linecvg_lookup;
    
        L2COCD L2 L2P L2RRDT     L2CVCD     SSLNCV
    ---------- -- --- ---------- ------ ----------
             2 AL ATC 13.10.1997 ADDEQ        2100
             2 AL ATC 13.10.1997 BI           1901
             2 AL ATC 13.10.1997 COLL         2103
             2 AL ATC 13.10.1997 COMP         2101
             2 AL ATC 13.10.1997 MEDPAY       2801
             2 AL ATC 13.10.1997 PD           2001
    
    6 rows selected.
    
    SQL>
    Log file excerpt:
    Code:
    Table LINECVG_LOOKUP, loaded from every logical record.
    Insert option in effect for this table: TRUNCATE
    
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    L2COCD                              FIRST     *  WHT      CHARACTER            
    L2SABB                               NEXT     *  WHT      CHARACTER            
    L2PRCD                               NEXT     *  WHT      CHARACTER            
    L2RRDT                               NEXT     *  WHT      CHARACTER            
        SQL string for column : "TO_DATE(:L2RRDT,'MM/DD/YYYY')"
    L2CVCD                               NEXT     *  WHT      CHARACTER            
    SSLNCV                               NEXT     *  WHT      CHARACTER            
    
    
    Table LINECVG_LOOKUP:
      6 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.
    
    
    Space allocated for bind array:                  99072 bytes(64 rows)
    Read   buffer bytes: 1048576
    
    Total logical records skipped:          1
    Total logical records read:             6
    Total logical records rejected:         0
    Total logical records discarded:        0

  3. #3
    Join Date
    Jan 2010
    Posts
    29
    Hello

    Thanks for your reply,I don't know why it is not working fine for me

    here is my modified control file:

    options (skip = 1)
    LOAD DATA
    INFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.txt'
    BADFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.bad'
    DISCARDFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.dsc'
    replace
    INTO TABLE DATUSER.LINECVG_LOOKUP
    TRUNCATE
    FIELDS TERMINATED BY ' '
    (L2COCD ,
    L2SABB ,
    L2PRCD ,
    L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YY')",
    L2CVCD ,
    SSLNCV )



    Error:


    Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2SABB.
    Column not found before end of logical record (use TRAILING NULLCOLS)
    Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2SABB.
    Column not found before end of logical record (use TRAILING NULLCOLS)
    Record 3: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2SABB.


    please help me!!


    Thanks
    Hasya

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    Oracle says
    (use TRAILING NULLCOLS)
    Did you try it?

  5. #5
    Join Date
    Jan 2010
    Posts
    29
    Thanks littelfoot for your reply,

    I tried TRAILING NULLCOLS,Now it is throwing error as

    Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number

    Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number


    Please suggest me where i am going wrong

    Thanks
    Hasya

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    I don't know. I created a table using CREATE TABLE you provided. I used sample data you provided. I fixed a control file, just a little bit.

    It works for me (on 11g XE), it doesn't work for you. Which database do you use, anyway?

  7. #7
    Join Date
    Jan 2010
    Posts
    29
    Hello Littelefoot,

    I am using Oracle10g

    Does it depends which version i am using,And do i need change quotes or something to accept the data because error is


    Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number


    Thanks in advance

    Hasya

  8. #8
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Your problem seems to be the date conversion string:

    Code:
    L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YY')",
    but the date format you have is

    Code:
    2 AL ATC 10/13/1997 COLL 2103
    So, if you change the line in the loader control file to

    Code:
    L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YYYY')",
    it should work
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    What a sharp eye you have!

    I used YYYY format mask in my control file; didn't pay attention to Hasya's ... shame on me!

  10. #10
    Join Date
    Jan 2010
    Posts
    29
    Thanks for your sugesstion,

    But still i am getting the same error


    i have changed the control file year format,in the sql ldr it showed Commit point reached 64(i have 60,000 records ),But no data in the table and i got the below error



    And also in the log file L2COCD is showing as character but it is number,Do i need to change anything there?


    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    L2COCD FIRST * WHT O(') CHARACTER
    L2SABB NEXT * WHT O(') CHARACTER
    L2PRCD NEXT * WHT O(') CHARACTER
    L2RRDT NEXT * WHT O(') CHARACTER
    SQL string for column : "TO_DATE(:L2RRDT,'MM/DD/YYYY')"
    L2CVCD NEXT * WHT O(') CHARACTER
    SSLNCV NEXT * WHT O(') CHARACTER

    Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number

    Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number
    Last edited by hasya05; 05-07-12 at 13:07.

  11. #11
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    hasya,

    I'm not sure, about the 10g sqlldr behavior on implicit type conversions (my installation also is a 11gR2).

    Just to prove, try

    Code:
    (L2COCD  "TO_NUMBER(:L2COCD)",
    Btw: you know, you have to "skip" the first line of your data, don't you ?
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  12. #12
    Join Date
    Jan 2010
    Posts
    29
    Hello Magicwand,

    Thanks for helping,

    But no luck....still getting the same error,But in the sqlldr it is showing

    commit point reached - logical record count 64....

    OMG i am not understanding what's happening...


    Yes i have to skip first record they are column names...

    here is my log file:

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    L2COCD FIRST * WHT O(') CHARACTER
    SQL string for column : "TO_NUMBER(:L2COCD)"
    L2SABB NEXT * WHT O(') CHARACTER
    L2PRCD NEXT * WHT O(') CHARACTER
    L2RRDT NEXT * WHT O(') CHARACTER
    SQL string for column : "TO_DATE(:L2RRDT,'MM/DD/YYYY')"
    L2CVCD NEXT * WHT O(') CHARACTER
    SSLNCV NEXT * WHT O(') CHARACTER

    Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number

    Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
    ORA-01722: invalid number


    Thanks
    Hasya

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    Could you ATTACH a few sample records from your file. Do not copy/paste them into a message (we already have that), attach a TXT file.

  14. #14
    Join Date
    Jan 2010
    Posts
    29
    hello Littelefoot,

    I have attached few records from my data...


    Thank you very much for your help
    Hasya
    Attached Files Attached Files

  15. #15
    Join Date
    Jan 2010
    Posts
    29
    waiting for your reply...

Posting Permissions

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