Database Reference
In-Depth Information
Now, if we try to insert into our table a single character that is 2 bytes long in UTF, we observe the following:
EODA@ORA12CR1> insert into t (a) values (unistr('\00d6'));
insert into t (a) values (unistr('\00d6'))
*
ERROR at line 1:
ORA-12899: value too large for column "EODA"."T"."A" (actual: 2, maximum: 1)
This example demonstrates two things:
•
VARCHAR2(1)
is in bytes, not characters. We have a single Unicode character, but it won't fit
into a single byte.
•
As you migrate an application from a single-byte fixed-width character set to a multibyte
character set, you might find that the text that once fit into your fields no longer does.
The reason for the second point is that a 20-character string in a single-byte character set is 20 bytes long and
will absolutely fit into a
VARCHAR2(20)
. However a 20-character field could be as long as 80 bytes in a multibyte
character set, and 20 Unicode characters may well not fit in 20 bytes. You might consider modifying your DDL to be
VARCHAR2(20 CHAR)
or using the
NLS_LENGTH_SEMANTICS
session parameter mentioned previously when running your
DDL to create your tables.
If we insert that single character into a field set up to hold a single character, we will observe the following:
EODA@ORA12CR1> insert into t (b) values (unistr('\00d6'));
1 row created.
EODA@ORA12CR1> select length(b), lengthb(b), dump(b) dump from t;
LENGTH(B) LENGTHB(B) DUMP
---------- ---------- --------------------
1 2 Typ=1 Len=2: 195,150
That
INSERT
succeeded, and we can see that the
LENGTH
of the inserted data is one character—all of the character
string functions work
character-wise
. So the length of the field is one character, but the
LENGTHB
(length in bytes)
function shows it takes 2 bytes of storage, and the
DUMP
function shows us exactly what those bytes are. So, that
example demonstrates one very common issue people encounter when using multibyte character sets, namely that a
VARCHAR2(N)
doesn't necessarily hold N
characters
, but rather N
bytes.
The next issue people confront frequently is that the maximum length in bytes of a
VARCHAR2
is 4,000 and in a
CHAR
is 2,000:
EODA@ORA12CR1> declare
2 l_data varchar2(4000 char);
3 l_ch varchar2(1 char) := unistr( '\00d6' );
4 begin
5 l_data := rpad( l_ch, 4000, l_ch );
6 insert into t ( c ) values ( l_data );
7 end;
8 /
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 6
