Selecting Column Length Semantics for VARCHAR2 or CHAR data types

In Oracle Database, column length semantics determine whether the length of a column is specified in bytes or in characters. Use BYTE to specify that the length is in bytes, and use CHAR to specify that the length is in characters.

We set the length semantics for an Oracle database using the NLS_LENGTH_SEMANTICS initialization parameter, and all VARCHAR2 and CHAR columns use the setting specified for this initialization parameter as the default. If this initialization parameter is not set, then the default setting is BYTE.

An individual column can override the length semantics for the database. For example, if the length semantics for a database is CHAR, then you can still specify BYTE for the length semantics of an individual column using the CREATE TABLE or ALTER TABLE statement.

Let’s talk briefly about the two options:

BYTE

1. Indicates that the column will have byte length semantic.
2. Default option when creating VARCHAR2 columns.
3. Not recommended when storing non-English characters in database.
4. Eg:- VARCHAR2(10 BYTE)

• Oracle can use up to 10 bytes for storage, but you may not actually be able to store 10 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

CHAR

1. Indicates that the column will have character semantics.
2. Explicitly create VARCHAR2 with CHAR semantics.
3. Recommended option when storing non-English characters in database.
4. Eg:- VARCHAR2(10 CHAR)

• Oracle can use enough space to store 11 characters, no matter how many bytes it takes to store each one.
• A single character may require up to 4 bytes (Oracle 11g onwards).

Below is working example to show the difference b/w VARCHAR2 columns stored with CHAR (C) and BYTE (B) length semantics. Assuming database character set is AL32UTF8.

• Create table TEST1 with VARCHAR2 columns — name and status stored with CHAR and BYTE semantics respectively.
• Querying data dictionary table user_tab_columns, we can fetch details about the VARCHAR2 columns storage.
• Column with CHAR semantics will have space for up to 200 (50 * 4) characters.
• Column with BYTE semantics will have space for up to 15 characters.