Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: SQL*Loader Field too long

    The data of H field is too long so the data hasnt inserted into the table.
    Example data of H field is:
    http://seek.3721.com/right.htm?p=\xd...e=yahoo&page=1
    Any way can solve this problem? By specifying the READBUFFERS or need to split the raw data into pieces? Pls help as I cannot find any examp on the api from the otn web site.

    LOAD DATA
    INFILE 'c:\testDB.txt'
    INTO TABLE TESTDB
    (
    A CHAR TERMINATED BY " ",
    B CHAR TERMINATED BY " ",
    C CHAR TERMINATED BY " ",
    D CHAR ENCLOSED BY "[" AND "]",
    E CHAR(4000) TERMINATED BY " " ENCLOSED BY '"',
    F CHAR TERMINATED BY " ",
    G CHAR TERMINATED BY " ",
    H CHAR TERMINATED BY " " ENCLOSED BY '"',
    I CHAR(4000) TERMINATED BY " " ENCLOSED BY '"' ,
    ID SEQUENCE(MAX,1)
    )

    Many Thanks!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    is it longer than 4k?

    please supply all the error codes you get when you run sqlloader
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    it didnt have any error
    however, the data flow into the .bad file only

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    SO, why was it bad????
    What does your log say??
    Throw me a bone here.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Posts
    8
    From the log file:

    Record 246: Rejected - Error on table TESTDB, column H.
    ORA-01401: inserted value too large for column


    Is tht SQL*Loader can accept CHAR only?
    As I've tired VARCHAR2, it cant run.
    Thanks!

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Can you describe your table for me?

    H is not the 4000 character column it looks like.
    So, you either are missing a delimiter on that specific line and sqlloader is trying to load the 4k line into H, OR something else is amiss.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jan 2004
    Posts
    8
    O...yes the field specified is only VARCHAR(100), after increasing it.
    It works!
    Many thanks!

    Could you tell me how to do if the raw data of one field doesnt appear?
    What I mean is I've 10 fields of a table, in the .ctl field also contains 10 fields, however, the raw data only have the first 9 fields only?
    The error on the log said

    Insert option in effect for this table: INSERT

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    A FIRST * WHT CHARACTER
    B NEXT * WHT CHARACTER
    C NEXT * WHT CHARACTER
    D NEXT * [ CHARACTER
    ]
    E NEXT * WHT " CHARACTER
    F NEXT * WHT CHARACTER
    G NEXT * WHT CHARACTER
    H NEXT * WHT " CHARACTER
    NULL if H = BLANKS
    I NEXT * WHT " CHARACTER
    NULL if I = BLANKS
    ID SEQUENCE (MAX, 1)

    Record 1116: Rejected - Error on table TESTDB, column H.
    Column not found before end of logical record (use TRAILING NULLCOLS)

    Many Thanks!
    & Happy Chinese New Year!

  8. #8
    Join Date
    Jan 2004
    Posts
    8
    By the way, the .ctl has been changed to
    LOAD DATA
    INFILE 'c:\testDB.log'
    INTO TABLE TESTDB
    (
    A CHAR TERMINATED BY " ",
    B CHAR TERMINATED BY " ",
    C CHAR TERMINATED BY " ",
    D CHAR ENCLOSED BY "[" AND "]",
    E CHAR TERMINATED BY " " ENCLOSED BY '"',
    F CHAR TERMINATED BY " ",
    G CHAR TERMINATED BY " ",
    H CHAR TERMINATED BY " " ENCLOSED BY '"' NULLIF H=BLANKS,
    I CHAR TERMINATED BY " " ENCLOSED BY '"' NULLIF I=BLANKS,
    ID SEQUENCE(MAX,1)
    )

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    do exactly what the error says, use the TRAILING NULLCOLS setting.

    example: FIELDS TERMINATED BY " " TRAILING NULLCOLS

    your ctl file should be like:
    PHP Code:
    LOAD DATA
    INFILE 
    'c:\testDB.log'
    INTO TABLE TESTDB
    TRAILING NULLCOLS
    (
    A CHAR TERMINATED BY " ",
    B CHAR TERMINATED BY " ",
    C CHAR TERMINATED BY " ",
    D CHAR ENCLOSED BY "[" AND "]",
    E CHAR TERMINATED BY " " ENCLOSED BY '"',
    F CHAR TERMINATED BY " ",
    G CHAR TERMINATED BY " ",
    H CHAR TERMINATED BY " " ENCLOSED BY '"' NULLIF H=BLANKS,
    I CHAR TERMINATED BY " " ENCLOSED BY '"' NULLIF I=BLANKS,
    ID SEQUENCE(MAX,1)

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jan 2004
    Posts
    8
    Yes...I got it!
    Thanks a lot!

    How about if I want to add a condition for J field likes
    LOAD DATA
    INFILE c:\testDB.txt'
    INTO TABLE TESTDB
    TRAILING NULLCOLS
    (
    A CHAR TERMINATED BY " ",
    B CHAR TERMINATED BY " ",
    C CHAR TERMINATED BY " ",
    D CHAR ENCLOSED BY "[" AND "]",
    E CHAR TERMINATED BY " " ENCLOSED BY '"',
    F CHAR TERMINATED BY " ",
    G CHAR TERMINATED BY " ",
    J CHAR TERMINATED BY " " ENCLOSED BY '"' DEFAULTIF J=BLANKS "DECODE(INSTR(:J,'?'),0,:J,SUBSTR(:J,1,INSTR(:J,'? ')-1))",
    I CHAR TERMINATED BY " " ENCLOSED BY '"' DEFAULTIF I=BLANKS,
    ID SEQUENCE(MAX,1)
    )

    I found the examples from
    http://otn.oracle.co.kr/docs/oracle7.../ch05.htm#2407
    however, I got error likes this
    SQL*Loader-309: No SQL string allowed as part of J field specification

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    you are close I think.
    For that type of stuff I might augment the field aftger I load it however.

    for the J line try using replace and see if that works:
    "replace(:J,'?','0')"
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Jan 2004
    Posts
    8
    After changing the type from CLOB to VARCHAR2, it works!!
    Anway. many thanks for your help!

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    this makes me a happy duck.
    good work!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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