Database Reference
In-Depth Information
This shows that a 4,000-character string that is really 8,000 bytes long cannot be stored permanently in a
VARCHAR2(4000 CHAR)
field. It fits in the PL/SQL variable because there a
VARCHAR2
is allowed to be up to 32KB in size.
However, when it is stored in a table, the hard limit is 4,000 bytes. We can store 2,000 of these characters successfully:
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, 2000, l_ch );
6 insert into t ( c ) values ( l_data );
7 end;
8 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select length( c ), lengthb( c )
2 from t
3 where c is not null;
LENGTH(C) LENGTHB(C)
---------- ----------
2000 4000
And as you can see, they consume 4,000 bytes of storage.
The “N” Variant
So, of what use are the
NVARCHAR2
and
NCHAR
(for completeness)? They are used in systems where the need to manage
and store multiple character sets arises. This typically happens in a database where the predominant character set is
a single-byte fixed-width one (such as
WE8MSWIN1252
), but the need arises to maintain and store some multibyte data.
There are many systems that have legacy data but need to support multibyte data for some new applications; likewise,
there are systems that want the efficiency of a single-byte character set for most operations (string operations on a
string that uses fixed-width characters are more efficient than on a string where each character may use a different
number of bytes) but need the flexibility of multibyte data at some points.
The
NVARCHAR2
and
NCHAR
datatypes support this need. They are generally the same as their
VARCHAR2
and
CHAR
counterparts, with the following exceptions:
•
Their text is stored and managed in the database's national character set, not the default
character set.
•
Their lengths are always provided in characters, whereas a
CHAR
/
VARCHAR2
may specify either
bytes or characters.
In Oracle9
i
and above, the database's national character set may take one of two values:
UTF8
or
AL16UTF16
(
UTF-16
in 9
i
;
AL16UTF16
in 10
g
). This makes the
NCHAR
and
NVARCHAR
types suitable for storing only multibyte data,
which is a change from earlier releases of the database (Oracle8
i
and earlier allowed you to choose any character set
for the national character set).
