Oracle nvarchar2

Multi-byte character field stored as the defined national character set. N fields are always Unicode.

select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’;

Maximum widths depend on the MAX_STRING_SIZE parameter. 2000 characters (4000 bytes) by default.

create table testvarchar(t1 varchar2(5), t2 nvarchar2(5));

insert into testvarchar values (‘€1234’, ‘€1234’);

Error report –

SQL Error: ORA-12899: value too large for column “ETL”.”TESTVARCHAR”.”T1″ (actual: 7, maximum: 5)

12899. 00000 – “value too large for column %s (actual: %s, maximum: %s)”

*Cause: An attempt was made to insert or update a column with a value

which is too wide for the width of the destination column.

The name of the column is given, along with the actual width

of the value, and the maximum allowed width of the column.

Note that widths are reported in characters if character length

semantics are in effect for the column, otherwise widths are

reported in bytes.

*Action: Examine the SQL statement for correctness. Check source

and destination column data types.

Either make the destination column wider, or use a subset

of the source column (i.e. use substring).

insert into testvarchar values (‘€12’, ‘€1234’);

1 rows inserted.

select length(t1), length(t2) from testvarchar;

SQL> select length(t1), length(t2) from testvarchar;

LENGTH(T1) LENGTH(T2)

———- ———-

3 5

alter table testvarchar modify t2 nvarchar2(2001);

SQL> alter table testvarchar modify t2 nvarchar2(2001);

alter table testvarchar modify t2 nvarchar2(2001)

*

ERROR at line 1:

ORA-00910: specified length too long for its datatype

Changing the data type while data exists requires data to be dropped therefore it’s complicated.

References

http://www.oracle.com/technetwork/database/database-technologies/globalization/twp-appdev-unicode-10gr2-129234.pdf

http://docs.oracle.com/database/121/NLSPG/ch6unicode.htm#NLSPG324

http://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG014

https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321

https://tools.ietf.org/html/rfc2781