About the NLS_LENGTH_SEMANTICS Parameter

Specifies: Length semantics for columns of the character data types CHAR, VARCHAR2, and LONG; that is, whether these columns are specified in bytes or in characters. (Applies only to columns that are declared after the parameter is set.)

Acceptable Values:

BYTE

New CHAR, VARCHAR2, and LONG columns are specified in bytes.
CHAR

New CHAR, VARCHAR2, and LONG columns are specified in characters.

Default Value: BYTE

To try this example in SQL Developer, enter the statements and queries in the SQL Worksheet. For information about the SQL Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

NLS_LENGTH_SEMANTICS Affects Storage of VARCHAR2 Column

Note the current values of NLS_LENGTH_SEMANTICS.

For instructions, see "Viewing NLS Parameter Values".
If the value of NLS_LENGTH_SEMANTICS in step 1 is not BYTE, change it:
```
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
```

Create a table with a VARCHAR2 column:

CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));

Click the tab Connections.

The Connections pane shows the connection hr_conn.
Expand hr_conn.

A list of schema object types appears, including Tables.
Expand Tables.

A list of tables appears, including SEMANTICS_BYTE.
Select SEMANTICS_BYTE.

To the right of the Connections pane, the Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 BYTE).

Change the value of NLS_LENGTH_SEMANTICS to CHAR:

ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

Create another table with a VARCHAR2 column:

CREATE TABLE SEMANTICS_CHAR(SOME_DATA VARCHAR2(20));

In the Connections pane, click the Refresh icon.

The list of tables now includes SEMANTICS_CHAR.
Select SEMANTICS_CHAR.

The Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 CHAR).
Select SEMANTICS_BYTE again.

The Columns pane shows that for Column Name SOME_DATA, the Data Type is still VARCHAR2(20 BYTE).
Set the value of NLS_LENGTH_SEMANTICS to the value that it had in step 1.

About the NLS_LENGTH_SEMANTICS Parameter

Related Topics