Shulou(Shulou.com)06/01 Report--

Oracle-defined string types VARCHAR2 and CHAR specify lengths as follows:

varchar2( ) is a number between 1 and 4000, indicating a maximum of 4000 bytes of storage space.

char( ) is a number between 1 and 2000, representing a maximum of 2000 bytes of storage space.

What is the difference between BYTE and CHAR?

BYTE, specified in bytes: VARCHAR2(10 BYTE). This can support up to 10 bytes of data, which may be just two characters in a multibyte character set. With multibyte character sets, bytes are not the same as characters.

CHAR, specified in characters: VARCHAR2(10 CHAR). This will support up to 10 characters of data, possibly up to 40 bytes of information. VARCHAR2(4000 CHAR) theoretically supports up to 4000 characters of data, although it may not be possible to get all 4000 characters due to the 4000 byte limit for string data types in Oracle.

When working with multibyte character sets such as UTF8, it is recommended that you use the CHAR modifier in VARCHAR2/CHAR definitions, that is, VARCHAR2(30 CHAR) instead of VARCHAR2(30), because your intention is likely to be to define a column that can actually store 30 characters of data. You can also use the session parameter or the system parameter NLS_LENGTH_SEMANTICS to modify the default behavior, changing the default setting BYTE to CHAR. It is not recommended to modify this setting at the system level, but rather at the session level using ALTER SESSION. It is also important to note that VARCHAR2 has an upper bound of 4000 bytes. However, even if you specify VARCHAR(4000 CHAR), you may not be able to fit 4000 characters in this field. In fact, if all characters are represented by 4 bytes in your chosen character set, you can only fit 1000 characters in this field!

Here is a small example to show the difference between BYTE and CHAR and to show the role of the upper bound.

Test environment 11.2.0.4 was done on a multibyte character set database, using character set AL32 UTF8, which supports the latest version of the Unicode standard and uses a variable-length method to encode each character using 1 - 4 bytes

zx@ORCL>col value for a30zx@ORCL>col parameter for a30zx@ORCL>select * from nls_database_parameters where parameter='NLS_CHARACTERSET';PARAMETER VALUE------------------------------ ------------------------------NLS_CHARACTERSET AL32UTF8zx@ORCL>show parameter nls_lengNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------nls_length_semantics string BYTE

Create a test sheet

zx@ORCL>create table t (a varchar2(1),b varchar2(1 char),c varchar2(4000 char));Table created.

Now insert a UTF character unistr ('\00d6') into this table, which is 2 bytes long, and observe the following:

zx@ORCL>select length(unistr('\00d6')),lengthb(unistr('\00d6')) from dual;LENGTH(UNISTR('\00D6')) LENGTHB(UNISTR('\00D6'))----------------------- ------------------------ 1 2zx@ORCL>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 "ZX". "T". "A" (actual: 2, maximum: 1)

This means VARCHAR(1) has bytes, not characters. It is true that there is only one Unicode character, but it does not fit in one byte; when migrating an application from a single-byte fixed-width character set to a multibyte character set, you may find that text that could fit in a field now does not fit. The reason for the second point is that in a single-byte character set, a string of 20 characters is 20 bytes long and can be placed in VARCHAR2(20). However, in a multibyte character set, 20 characters can be up to 80 bytes long (if each character is represented by 4 bytes), so that 20 Unicode characters probably cannot fit into 20 bytes. You might consider modifying DDL to VARCHAR2(20 CHAR), or using the NLS_LENGTH_SEMENTICS session parameter mentioned earlier when running DDL to create tables.

The following results were observed when inserting a field containing one character:

zx@ORCL>insert into t (b) values (unistr('\00d6'));1 row created.zx@ORCL>col dump for a30zx@ORCL>select length(b),lengthb(b),dump(b) dump from t; LENGTH(B) LENGTHB(B) DUMP---------- ---------- ------------------------------ 1 2 Typ=1 Len=2: 195,150

This INSERT succeeds, and as you can see, the length of all inserted data (LENGTH) is one character, and all string functions work in character units. The LENGTHB function (byte length) shows that this field takes up 2 bytes of storage space, and the DUMP function shows what these bytes are. This example shows that VARCHAR2(N) does not necessarily store N characters, but only N bytes.

VARCHAR2(4000) may not store 4000 characters

zx@ORCL>declare 2 l_date varchar2(4000 char); 3 l_ch varchar2(1 char) := unistr('\00d6'); 4 begin 5 l_date:=rpad(l_ch,4000,l_ch); 6 insert into t(c) values(l_date); 7 end; 8 /declare*ERROR at line 1:ORA-01461: can bind a LONG value only for insert into a LONG columnORA-06512: at line 6

As shown here, a 4000-character string is actually 8000 bytes long, and such a string cannot be stored permanently in a VARCHAR(4000 char) field; it can be placed in PL/SQL variables because VARCHAR2 can be up to 32K in PL/SQL. However, VARCHAR2 is rigidly limited to a maximum of 4000 bytes stored in tables. We can successfully store 2000 of these characters:

zx@ORCL>declare 2 l_date varchar2(4000 char); 3 l_ch varchar2(1 char) := unistr('\00d6'); 4 begin 5 l_date:=rpad(l_ch,2000,l_ch); 6 insert into t(c) values(l_date); 7 end; 8 /PL/SQL procedure successfully completed.zx@ORCL>zx@ORCL>select length(c),lengthb(c) from t where c is not null; LENGTH(C) LENGTHB(C)---------- ---------- 2000 4000

The output shows that c takes up 4000 bytes of storage space.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.