Byte semantic vs length semantic

Byte semantic vs length semantic

The database-parameter NLS_LENGTH_SEMANTIC shows starting with ORACLE 9i the default value of the used semantic for the lenth of table-attributes.

SQL> show parameter NLS_LENGTH_SEMANTICS

NAME TYPE VALUE
-------------------- ----------- ------
nls_length_semantics string BYTE

Byte means: The reserved storage per char is here in Byte as a default. This default-value is used for at least varchar2- and char-fields.

varchar2(1)

Here explicitly used:

varchar2(1 BYTE)

So if the attribute has an length of 1 (Byte) and you use Character-Set AL32UTF8 a Char has a length of 1,2 3 or 4 byte and so you will never be able to insert a special-character having a length more than 1 Byte in your table…

But if you use CHAR as length-semantic the available storage is big enough for storing any char.

varchar2(1 CHAR)

Important
Excerpt ORACLE-Docu:
Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).

Here all the examples:

Used the default-value of NLS_LENGTH_SEMANTICS:

create table table_attribute_default (field1 varchar2(10));
SELECT column_name,
data_type,
data_length,
char_used,
decode(char_used,'B','BYTE','C','CHAR') semantic
FROM all_tab_columns
WHERE table_name = 'TABLE_ATTRIBUTE_DEFAULT';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_USED SEMANTIC
----------- --------- ----------- --------- --------
FIELD1 VARCHAR2 10 B BYTE

Used BYTE as length-semantic:

create table table_attribute_byte (field1 varchar2(10 BYTE));
SELECT column_name,
data_type,
data_length,
char_used,
decode(char_used,'B','BYTE','C','CHAR') semantic
FROM all_tab_columns
WHERE table_name = 'TABLE_ATTRIBUTE_BYTE';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_USED SEMANTIC
----------- --------- ----------- --------- --------
FIELD1 VARCHAR2 10 B BYTE

Used CHAR as length-semantic:

create table table_attribute_char (field1 varchar2(10 CHAR));
SELECT column_name,
data_type,
data_length,
char_used,
decode(char_used,'B','BYTE','C','CHAR') semantic
FROM all_tab_columns
WHERE table_name = 'TABLE_ATTRIBUTE_CHAR';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_USED SEMANTIC
----------- --------- ----------- --------- --------
FIELD1 VARCHAR2 40 C CHAR

DROP TABLE table_attribute_default;
DROP TABLE table_attribute_byte;
DROP TABLE table_attribute_char;