Purpose of the ORACLE nls_length_semantics parameter

The Nls_length_semantics parameter is a byte-and char-two value that is specified for a column that is designed to create char and VARCHAR2 two character types, specifies the length of bytes used, or uses the character length definition, which defaults to byte.

When the parameter is set to Byte, the definition char column or VARCHAR2 column takes the byte-length method, and when the parameter is set to char, defines how the char column or VARCHAR2 column takes the character length. This parameter does not have any purpose for columns that already exist in the database, but it is only meaningful when you create a table or modify a table's columns.

The value of the Nls_length_semantics parameter does not have any effect on columns that already exist, except that when you create a column in a table, the default specified column length type is byte or char, and if you specify a length type when you create or modify a table's columns, completely overwrite nls_length_ The value of the semantics parameter.

The use of the Nls_length_semantics parameter is clearly stated in the three CREATE TABLE statement below:

1. Two SQL statements of the specified length type

Create Table varchar2 (20byte)); Create Table varchar2 (+char));

The only difference in the above two statements is that after specifying a column length of 20, the type of the length is specified and the value of the type is different.

2. SQL statements that do not specify a length type

Create Table varchar2 ());

This statement, after the length of the specified column is 20, does not specify a type of length, what is its type, which is determined by the value of the Nls_length_semantics parameter, which can be set at the session level.

1. View the value of the database Nls_length_semantics parameter (this value is only 2 values, Byte,char, which is byte by default)

Select *  from where t.parameter='nls_length_semantics';

2. View the value of the Nls_characterset

Select *  from where t.parameter='nls_characterset';

The nls_characterset of the database is Al32utf8, that is, a Chinese character occupies three to four bytes. If Nls_characterset is ZHS16GBK, a character occupies two bytes (1char=2byte).

Purpose of the ORACLE nls_length_semantics parameter