6 Replies Latest reply: Dec 28, 2010 6:00 PM by Srini Chavali-Oracle RSS

    Sql*loader 11g - Error ORA-12899

    ggiulian
      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
          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
            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
              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
                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
                  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
                    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