Alessandro Lacava

on Designing and Developing Software. In love with Functional Programming.

How to Tell Oracle to Use More Than One Byte Per Character

When you create a table, by default, the number of bytes used to represent a character in a VARCHAR2 type is 1, irrespective of the characterset of the DB. This may be fine if the characterset is such that it uses just one byte per character. However, if the characterset of the DB is such that there might take more than one byte to represent a character (for example for characterset = AL32UTF8) you might run into troubles, such as the error: ORA-01401: inserted value too large for column. However you can specify, at creation time, that the size of characters is expressed in CHARs instead of bytes. For example:

CREATE TABLE test (col_test VARCHAR2(10 CHAR))

The previous code creates a table with just one column. The column will be able to contain 10 characters irrespective of the number of bytes used to represent the characters internally.