a varchar2(n) is not the same as a varchar2(n char)

October 1, 2013 — Leave a comment

a lot of people define varchar2 columns this way:

create table t1 ( a varchar2(1) );

… and expect that they will be able to insert one character into this column. well, this is not always true. the default for a varchar2 column is byte, so the above statement is equal to:

create table t1 ( a varchar2(1 byte) );

The length semantics of character data types are measurable in bytes or characters. The treatment of strings as a sequence of bytes is called byte semantics. This is the default for character data types. The treatment of strings as a sequence of characters is called character semantics. A character is a code point of the database character set.

the data dictionary does reflect this if you know what the default is:

SQL> create table t1 ( a varchar2(1 char), b varchar2(1));

Table created.

SQL> desc t1;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 A						    VARCHAR2(1 CHAR)
 B						    VARCHAR2(1)

SQL> 

so, if you work with multibyte character sets and do not take care of this, this may happen:

create table t1 ( a varchar2(1 char), b varchar2(1));
insert into t1 (a) values (chr(1000));

1 row created.

SQL> insert into t1 (b) values (chr(1000));
insert into t1 (b) values (chr(1000))
                           *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."T1"."B" (actual: 2, maximum: 1)
Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s