NLS Parameters

Introduction
A Wonderful parameter introduced by Oralce since 9i onwards. The parameter is NLS_LENGTH_SEMANTICS which allows you to specify the length of a column
datatype in terms of Characters rather than in terms of BYTEs. This parameter is a 9i (and up) feature and is not available in older releases. All Oracle
Professional, who need to understand the impact of this on the storage capacity of object columns. This parameter can be set at instance level in the init.ora
or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS. This can be set at session
((ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR ) level only, in that case only NLS_SESSION_PARAMETERS will change.
It cannot be set as a client side environment/registry parameter in 9i, from 10g onwards it can be. Character Semantics in Oracle 9i Forms says that
you can set it as a client environment parameter, but this is only because forms 9i uses that to generate forms with CHAR or BYTE semantics.
This parameter allows you to specify the length of a column datatype in terms of Characters rather than in terms of BYTEs. Typically this is when using
an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has
as such no added value in a 7/8 bit characterset it's fully supported so any application code / table setup using CHAR can also be used in a
7/8bit characterset like US7ASCII/WE8MSWIN1252.
Instance/session NLS_LENGTH_SEMANTICS parameter only influence create/alter column statements and pl/sql variables who do not explicit define the semantic to be used.
NLS_LENGTH_SEMANTICS value found in NLS_DATABASE_PARAMETERS is the value at database creation time. This should be BYTE.
NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init*.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.
NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
NLS_LENGTH_SEMANTICS can also be set in a current session ((ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR ), in that case only the NLS_SESSION_PARAMETERS for this session will change and it will be active for that session only.
The instance or session value will only be used when creating NEW columns. Setting NLS_LENGTH_SEMANTICS to CHAR will NOT adapt current existing column definitions. In other words, if you have columns who now use BYTE and you change the instance parameter to CHAR then those columns will still be BYTE. To change existing tables you need to use "alter table". See further down for more information on this. Using CHAR semantics: CHAR semantics is NOT (yet) supported in the E-Business Suite up and including Release 11i. If you run patch scripts or scripts from $ORACLE_HOME/RDBMS/ADMIN like catalog.sql use STARTUP MIGRATE; and run then the scripts.( = with NLS_LENGTH_SEMANTICS=BYTE ) Clients (or servers) older then 9i (8i, 80..) will not see CHAR semantics the will get the length returned in BYTE. A 9i UTF8 database with a VARCHAR2(10 CHAR) column will show up in a 8 client as VARCHAR2(30). > The default sizing of character data types (CHAR, VARCHAR2, and LONG) is in bytes by default, only in CHARacters for NCHAR and NVARCHAR2. CHAR(10) in a table definition means 10 bytes not 10 characters. For a single-byte character set encoding, the character and byte length arethe same (one character = one byte). However, in multi-byte character set encodings like AL32UTF8 the character may use up to 4 bytes to store one character making sizing the column length more difficult. Hence the reason why CHAR semantics was introduced. However, we still have some physical underlying byte based limits and development has choosen to allow the full usage of the underlying limits. This results in the following table giving the maximum amount of CHARarcters occupying the MAX datalength that can be stored for a certain datatype in 9i and up. The MAX colum is the MAXIMUM amount of CHARACTERS that can be stored occupying the MAXIMUM data length. seen that UTF8 and AL32UTF8 are VARRYING charactersets this means that a string of X chars can be X to X*3 (or X*4 for AL32) bytes. N-types (NVARCHAR2, NCHAR) are *always* defined in CHAR semantics, you cannot define them in BYTE. The MIN col is the maximum size that you can *define* and that Oracle can store if all data is the MINIMUM datalength (1 byte for AL32UTF8 and UTF8) for that characterset. UTF8 (1 to 3 bytes) AL32UTF8 (1 to 4 bytes) AL16UTF16 ( 2 bytes) MIN MAX MIN MAX MIN MAX CHAR 2000 666 2000 500 N/A N/A VARCHAR2 4000 1333 4000 1000 N/A N/A NCHAR 2000 666 N/A N/A 1000 1000 NVARCHAR2 4000 1333 N/A N/A 2000 2000 This means that if you try to store more then 666 characters that occupy 3 bytes in UTF8 in a CHAR UTF8 colum you still will get a ORA-01401: inserted value too large for column (or from 10g onwards: ORA-12899: value too large for column ) even if you have defined the colum as CHAR (2000 CHAR) so here it might be a good idea to define that column as NCHAR that will raise the MAX to 1000 char's ...

The default setting for NLS_LENGTH_SEMANTICS is BYTE and the default sizing of character data types (CHAR, VARCHAR2) is in BYTES . For example, if NLS_LENGTH_SEMANTICS is not set or set to BYTE then CHAR(10) in a table definition means 10 bytes not 10 characters.If NLS_LENGTH_SEMANTICS is set to CHAR then using CHAR(10) will create a column with a 10 CHAR width.It also possible to explicit define the BYTE or CHAR semantics when creating a column:CHAR(10 BYTE) - will always be BYTE regardless of the used NLS_LENGTH_SEMANTICSCHAR(10 CHAR) - will always be CHAR regardless of the used NLS_LENGTH_SEMANTICS
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;Session altered.SQL> Create table KUMAR.EXAMPLE (NLS1 CHAR(20),NLS2 VARCHAR2(100));Table created.SQL> Create table KUMAR.EXAMPLE2 (NLS1 CHAR(20 CHAR),NLS2 VARCHAR2(100 CHAR));Table created.SQL> Create table KUMAR.EXAMPLE3 (NLS1 CHAR(20 BYTE),NLS2 VARCHAR2(100 BYTE));Table created.SQL> desc KUMAR.EXAMPLE Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20) NLS2 VARCHAR2(100)SQL> desc KUMAR.EXAMPLE2 Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20 CHAR) NLS2 VARCHAR2(100 CHAR)SQL> desc KUMAR.EXAMPLE3 Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20) NLS2 VARCHAR2(100)SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;Session altered.SQL> desc KUMAR.EXAMPLE Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20 BYTE) NLS2 VARCHAR2(100 BYTE)SQL> desc KUMAR.EXAMPLE2 Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20) NLS2 VARCHAR2(100)SQL> desc KUMAR.EXAMPLE3 Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20 BYTE) NLS2 VARCHAR2(100 BYTE)SQL> Create table kumar.example4 (NLS1 CHAR(20),NLS2 VARCHAR2(100));Table created.SQL> desc kumar.example4 Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20) NLS2 VARCHAR2(100)SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;Session altered.SQL> desc kumar.example4 Name Null? Type ----------------------------------------- -------- ---------------------------- NLS1 CHAR(20 CHAR) NLS2 VARCHAR2(100 CHAR)


Issues:
Even a ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=both; (Oracle 9i) needs a restart of the database.
There is a issue with catcpu.sql (CPU patchsets) that provokes a system wide NLS_LENGTH_SEMANTICS=BYTE , even if your system had NLS_LENGTH_SEMANTICS=CHAR. Perform after applying the CPU patches and before restarting the database
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=both; Fixed in JAN CPU 2006 patch .
Do NOT set the NLS_LENGTH_SEMANTICS=CHAR during database creation,create the database with NLS_LENGTH_SEMANTICS=BYTE (or not set) and set NLS_LENGTH_SEMANTICS=CHAR *after* the database creation. (especialy when XDB is installed, if XDB is not installed there are no known issues with creating a database whilst NLS_LENGTH_SEMANTICS=CHAR)