This discussion is archived
6 Replies Latest reply: Dec 28, 2010 4:00 PM by Srini Chavali-Oracle RSS

Sql*loader 11g - Error ORA-12899

ggiulian Newbie
Currently Being Moderated
Hi All,

I'm using Sqlload for inserti into a 11g db some csv file from db2.
My server is a Linux o.s. with Oracle 11.2.0.1.0 - 64bit.

I receive this error ORA-12899: Value too large for column on all file I try to load.

NLS for my db is:

SQL> select parameter, value from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8


Have you any idea?
Thanks very much for help

Regards
Giovanni
  • 1. Re: Sql*loader 11g - Error ORA-12899
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post your complete sqlldr command, along with details of the control file, sample data being loaded and the description of the table(s) being loaded

    HTH
    Srini
  • 2. Re: Sql*loader 11g - Error ORA-12899
    ggiulian Newbie
    Currently Being Moderated
    Ok,

    Control File is:

    LOAD DATA
    INFILE "ASLINVIANTE.csv"
    APPEND
    INTO TABLE OPERATION
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (INTERNALID, IDASL)


    Oracle TABLE is
    SQL> desc ASLINVIANTE
    Name
    ---------------------------------------------------------------------------------------- ---------------- ----------------------------------
    INTERNALID NOT NULL VARCHAR2(20)
    IDASL NOT NULL VARCHAR2(64)


    File CSV is
    "REG000200900009697687","REG000200900017420003"


    Thanks very much
    Regards
    Giovanni

    Edited by: ggiulian on 27-dic-2010 16.53
  • 3. Re: Sql*loader 11g - Error ORA-12899
    ggiulian Newbie
    Currently Being Moderated
    In internet I found this short message:
    “AL32UTF8 is a multi-byte characterset,that means some characters are stored in more than 1 character, that's true for these special characters.
    If you have same table definitions in both databases you likely face error ORA-12899.
    This metalink note discusses this problem, it's also applicable to sqlloader:
    Import reports "ORA-12899: Value too large for column" when using BYTE semantic
    Doc ID: Note:563893.1

    By metalink, I can see the Note linked to a one Oracle Internal Bug for Oracle 11g.....

    I'm waiting you suggestion... thanks very much in advance.
    Regards.
    Giovanni
  • 4. Re: Sql*loader 11g - Error ORA-12899
    732412 Journeyer
    Currently Being Moderated
    Hello Giovanni.
    The sample data provided for INTERNALID of REG000200900009697687 is 21 positions and therefore exceeds the column definition of VARCHAR2(20).


    Hope this helps,
    Luke

    Please mark the answer as helpful or answered if it is so. If not, provide additional details.
    Always try to provide create table and insert table statements to help the forum members help you better.
  • 5. Re: Sql*loader 11g - Error ORA-12899
    ggiulian Newbie
    Currently Being Moderated
    Yes, but I mistake to write here the correct source string.

    Now I change field size for table and I've:
    SQL> desc ASLINVIANTE
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    INTERNALID VARCHAR2(21)
    IDASL VARCHAR2(64)

    But with sqlloader I receive the same error with the same source:
    Error on table ASLINVIANTE, column INTERNALID.
    ORA-12899: value too large for column "REGRT"."ASLINVIANTE"."INTERNALID" (actual: 23, maximum: 21)
  • 6. Re: Sql*loader 11g - Error ORA-12899
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Your research that you posted above is correct - some characters in AL32UTF8 take up more than 1 byte, which could lead to the errors that your are encountering. There are two solutions, AFAIK - (1) increase the size of the column or (2) change NLS_LENGTH_SEMANTICS to CHAR ( pl read this MOS Doc before proceeding any further)

    Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) (Doc ID 144808.1)

    HTH
    Srini

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points