Re: ORA-12899: value too large for column with Oracle Patch 10.2.0.4

From: <epokopac_at_gmail.com>
Date: Mon, 31 Dec 2012 06:41:07 -0800 (PST)
Message-ID: <b507c923-b2eb-490e-9849-24dd0b42476f_at_googlegroups.com>

SOME SUGGESTIONS: It can happen that the character you are trying to insert looks like there should be no problem:

SQL> desc test

Name                                      Null?    Type


----------------------------------------- -------- -----------

N                                                  VARCHAR2(1)

SQL> insert into test values ('?');
insert into test values ('?')

                         *

ERROR at line 1:
ORA-12899: value too large for column "ME"."TEST"."N" (actual: 3, maximum: 1)

This can happen due to the character set the database is running in. <===

In a AL32UTF8 (unicode) database, some characters take more than 1 byte.

If you do not specify at create time what unit the size for a column is in, then the default is the amount of bytes. In our example, the ? will take 3 bytes due to the fact we are in a Unicode database and will cause the error.

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; VALUE

---

AL32UTF8 We can fix this by telling that the size is in CHAR’s instead of BYTE’s:

SQL> alter table test modify(n varchar2(1 char)); Table altered.

SQL> desc test Name                                      Null?    Type


----------------------------------------- -------- -----------------

N                                                  VARCHAR2(1 CHAR)

SQL> insert into test values ('?');
1 row created.

The root cause of an ORA-12899 at our site recently turned out to be "trash" in one column in one row:

MGR_ARCH_at_SGPR>select course_num from gp_trans_courses_2012   2 where rowid = 'AAAV7CAB8AAAVSZAA8' ;

COURSE_NUM

---

45.0910035¿¿¿¿

MGR_ARCH_at_SGPR>select rawtohex(course_num) from gp_trans_courses_2012   2 where rowid = 'AAAV7CAB8AAAVSZAA8' ;

RAWTOHEX(COURSE_NUM)

---

34352E30393130303335A0A0A0A0

34 = 4
35 = 5
2E = .
30 = 0
39 = 9
31 = 1
30 = 0
30 = 0
33 = 3
35 = 5
A0 = non-breaking space (no symbol for it)
A0 = non-breaking space (no symbol for it)
A0 = non-breaking space (no symbol for it)
A0 = non-breaking space (no symbol for it)

Who knows where the ‘A0’ came from? MS Word? Some text-editing tool that created the original data? A XAP burp? Received on Mon Dec 31 2012 - 15:41:07 CET