Getting ORA-12899: Value too large error for nvarchar2 and varchar2 in Oracle

Question

I have created 2 tables and inserting data as below---

 SQL> create table t (aa nvarchar2(10));

Table created.

SQL> create table t1 (aa varchar2(10));

Table created.

Now, inserting some french characters like below--

SQL> insert into t values ('Éé, Eè, Eê, Eë');
insert into t values ('Éé, Eè, Eê, Eë')
                      *
ERROR at line 1:
ORA-12899: value too large for column "CDUREFDB"."T"."AA" (actual: 14, maximum:
10)


SQL> insert into t1 values ('Éé, Eè, Eê, Eë');
insert into t1 values ('Éé, Eè, Eê, Eë')
                       *
ERROR at line 1:
ORA-12899: value too large for column "CDUREFDB"."T1"."AA" (actual: 21,
maximum: 10)

The question is why there is difference in error message in both inserts.

Below is NLS parameters details:

SQL> SELECT *
  2    FROM v$nls_parameters
  3  WHERE parameter LIKE '%CHARACTERSET'
  4  ;

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
UTF8

And I am using Oracle 11g version.

Also, is it recommended to use NVARCHAR2(NCHAR etc.) for non-english characters in oracle?

Thanks.

Well, `Éé, Eè, Eê, Eë` is clearly longer than 10 characters. What do you expect to happen? — , Dec 07 '15 at 19:05
the ORA error message is showing different actual values in both cases. — Ashish Patil, Dec 07 '15 at 19:07

score 2 · Accepted Answer · answered Dec 07 '15 at 19:11

If you are asking why the "actual" value in the error message differs, the answer is length semantics.

When you declare an nvarchar2(n), you are implicitly using character length semantics. An nvarchar2(n) allocates space for n characters in the nls_nchar_characterset regardless of the number of bytes that requires. You're trying to insert a 14 character string into an nvarchar2(10) so you get 14 as the actual length the error.

When you declare a varchar2(n), you'll allocate space for either n bytes or n characters of storage depending on your session's nls_length_semantics. By default, that is byte so you'd allocate space for 10 bytes. Some of your characters require more than 1 byte of storage in your nls_characterset so your 14 character string would require 21 bytes of storage. That's why you get 21 as the actual length in the error. I'd expect that if you declared the column as varchar2(n char) using character length semantics that the reported actual size would be 14.

If your database character set supports Unicode, there is very seldom a reason to use nvarchar2. When both your database and national character set are UTF-8, it makes no sense to ever use nvarchar2.

Thanks @Justin Cave; Now I am crystal clear with concept. – Ashish Patil Dec 07 '15 at 19:22 — Ashish Patil, Dec 07 '15 at 19:22

Getting ORA-12899: Value too large error for nvarchar2 and varchar2 in Oracle

1 Answers1