Setting the NLS_LENGTH_SEMANTICS Parameter
Establishing a Globalization Support Environment
6-26 Oracle Database 2 Day Developer's Guide
characters, which are 1 byte long, for a total of 20 bytes. Using character semantics, the
column requires 10 characters.
The expressions in the following list use byte semantics. Note the
BYTE
qualifier in the
VARCHAR2
expression and the
B
suffix in the SQL function name.
VARCHAR2(20 BYTE)
SUBSTRB(
string
, 1, 20)
The expressions in the following list use character semantics. Note the
CHAR
qualifier
in the
VARCHAR2
expression.
VARCHAR2(20 CHAR)
SUBSTR(
string
, 1, 20)
Using the NLS_LENGTH_SEMANTICS Parameter
The
NLS_LENGTH_SEMANTICS
parameter specifies
BYTE
(default) or
CHAR
semantics.
By default, the character data types
CHAR
and
VARCHAR2
are specified in bytes, not
characters. Therefore, the specification
CHAR(20)
in a table definition allows 20 bytes
for storing character data.
The
NLS_LENGTH_SEMANTICS
parameter enables you to create
CHAR
,
VARCHAR2
, and
LONG
columns using either byte-length or character-length semantics.
NCHAR
,
NVARCHAR2
,
CLOB
, and
NCLOB
columns are always character-based. Existing columns
are not affected.
To set the NLS_LENGTH_SEMANTICS parameter:
1.
In SQL Developer, make a note of the current semantics format in which Oracle
Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database,
and then National Language Support Parameters. In the Select Connection dialog
box, select
hr_conn
from the list of connections. The current semantics format is
listed after
NLS_LENGTH_SEMANTICS
.
2.
Set
NLS_LENGTH_SEMANTICS
to
BYTE
.
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
3.
Create the following table:
CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));
4.
Check the data types of table
SEMANTICS_BYTE
.
Select the Connections tab, and then expand the
hr_conn
connection to display
all the tables under Tables. Select the
SEMANTICS_BYTE
table. The data type for its
SOME_DATA
column is listed as
VARCHAR2(20 BYTE)
.
5.
Set
NLS_LENGTH_SEMANTICS
to
CHAR
.
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
6.
Create the following table:
See Also:
Oracle Database Globalization Support Guide for a complete
discussion of choosing or changing a character set, including
length semantics
