NLS_LENGTH_SEMANTICS

BACKGROUND

This document describes the limits and best practices when implementing NLS_LENGTH_SEMANTICS.

NLS_LENGTH_SEMANTICS allows one 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.
This parameter is a 9i (and up) feature and is not available in older releases.

SETTING NLS_LENGTH_SEMANTICS

The 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.

  • The NLS_LENGTH_SEMANTICS value found in NLS_DATABASE_PARAMETERS is the value at database creation time. This should be BYTE.
  • The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or spfile. One will then see the parameter change in NLS_INSTANCE_PARAMETERS.
  • If 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 ( Note: 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 one has columns who now use BYTE and you change the instance parameter to CHAR then those columns will still be BYTE. To change existing tables one needs to use "alter table".

See further down for more information on this.

THINGS TO KNOW WHEN USING CHAR SEMANTICS

  • CHAR semantics is NOT supported by the E-Business Suite.
  • If one runs patch scripts or scripts from $ORACLE_HOME/RDBMS/ADMIN like catalog.sql use STARTUP MIGRATE; and then the scripts. Make sure they are run with NLS_LENGTH_SEMANTICS=BYTE.
  • An ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=both; needs a restart of the database before it takes effect.
  • Do NOT set the NLS_LENGTH_SEMANTICS=CHAR during database creation, create the database with NLS_LENGTH_SEMANTICS=BYTE and set NLS_LENGTH_SEMANTICS=CHAR after the database creation in the init.ora or spfile.

If NLS_LENGTH_SEMANTICS=CHAR was set during database creation then XDB and SYS objects like dba_tables will then use CHAR semantics, which is not supported and will give problems. All known issues with this are solved in 11.1.0.6 (and up) but it might still be a good idea to create the database with BYTE to avoid any issues.

  • There is no problem using CHAR semantics in a 8 or 7 bit characterset. One can perfectly define a column as VARCHAR2(20 CHAR) in an US7ASCII or WE8MSWIN1252 database.
  • Clients (or servers) older then 9i (8i, 80..) will not see CHAR semantics they 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).
  • Oracle Text does NOT support instance wide NLS_LENGTH_SEMANTICS=CHAR. If one is using Oracle Text then the database needs to be created and started with NLS_LENGTH_SEMANTICS=BYTE. Using CHAR semantics on column definitions itself is supported however.

DEFAULTS AND LIMITS

  • 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_SEMANTICS
    • CHAR(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 scott.test1 (Col1 CHAR(20),Col2 VARCHAR2(100));

Table created.

SQL> Create table scott.test2 (Col1 CHAR(20 CHAR),Col2 VARCHAR2(100 CHAR));

Table created.

SQL> Create table scott.test3 (Col1 CHAR(20 BYTE),Col2 VARCHAR2(100 BYTE));

Table created.

SQL> desc scott.test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20)
 COL2                                               VARCHAR2(100)

SQL> desc scott.test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20 CHAR)
 COL2                                               VARCHAR2(100 CHAR)

SQL> desc scott.test3
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20)
 COL2                                               VARCHAR2(100)

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS}}CHAR;

Session altered.

SQL> desc scott.test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20 BYTE)
 COL2                                               VARCHAR2(100 BYTE)

SQL> desc scott.test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20)
 COL2                                               VARCHAR2(100)

SQL> desc scott.test3
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20 BYTE)
 COL2                                               VARCHAR2(100 BYTE)

SQL> Create table scott.test4 (Col1 CHAR(20),Col2 VARCHAR2(100));

Table created.

SQL> desc scott.test4
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20)
 COL2                                               VARCHAR2(100)

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS}}BYTE;

Session altered.

SQL> desc scott.test4
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(20 CHAR)
 COL2                                               VARCHAR2(100 CHAR)

SQL>

Note the dependency of the output of the DESC output on the sessions NLS_LENGTH_SEMANTICS setting. The CHAR_USED column from DBA_TAB_COLUMNS can also be used to to see if a column is created using BYTE or CHAR semantics, it then contains B or C regardless of the used NLS_LENGTH_SEMANTICS session setting.

  • When using CHAR semantics Oracle advices to define explicit the CHAR semantics when creating tables.
    • For example: create table scott.test (col1 CHAR(20 CHAR), col2 VARCHAR2(100 CHAR));
  • For a single-byte character set encoding ( WE8ISO8859P1, US7ASCII etc..), the character and byte length are the 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, there are still have some physical underlying byte based limits and development has chosen to allow the full usage of the underlying limits. This results in the following table giving the maximum amount of CHARacters occupying the MAX data length that can be stored for a certain datatype in 9i and up.
    • The table below gives a overview of the limits of CHAR semantics.
      All numbers are CHAR definitions 
                    UTF8  |      AL32UTF8 |  AL16UTF16 
            (1 to 3 bytes)| (1 to 4 bytes)|  (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

      (N/A means not possible)

NLS_LENGTH_SEMANTICS HAS NO EFFECT ON TABLES OWNED BY SYS

  • This is normal, for sys objects NLS_LENGTH_SEMANTICS is ignored and the these are always treated with byte semantics. Note that it is possible to create a table with explicit CHAR semantics under the sys schema, this is however not supported and should be avoided.
  • SYSTEM objects are affected by NLS_LENGTH_SEMANTICS, the docset of older Oracle versions is not correct on that.

ANONYMOUS BLOCKS WILL NOT PICK UP CHANGED NLS_LENGTH_SEMANTICS PARAMETER

The NLS_LENGTH_SEMANTICS session value active at the time of CREATE PROCEDURE or CREATE PACKAGE statement execution is stored together with the created PL/SQL unit and used for all following implicit recompilations, caused by modification of referenced objects. An explicit CREATE OR REPLACE or ALTER … COMPILE statement rereads the session setting NLS_SESSION_PARAMETERS (!), uses the value for defining variables, and stores it with the recompiled object. On the other hand, the ALTER statement may contain the REUSE SETTINGS clause or an explicit specification of NLS_LENGTH_SEMANTICS value, which again override the session parameter.

To be more clear, one can define the same variable in 3 ways:

  • x CHAR(3 BYTE);
  • x CHAR(3 CHAR);
  • x CHAR(3);

in the first 2 there is no confusion possible as you are explicit defining the used semantics for the variable itself. When declaring/creating the procedure or package however using x CHAR(3) when NLS_LENGTH_SEMANTICS=BYTE then the semantic will stay BYTE based, even after the session is changed to NLS_LENGTH_SEMANTICS = CHAR, until the blocks are recompiled, only then the package or procedure will pick up the changed NLS_LENGTH_SEMANTICS setting.

So without recompile the x CHAR(3) will "act" like x CHAR(3 BYTE) even after the session (!) has been changed to use NLS_LENGTH_SEMANTICS = CHAR. Only after a recompile the x CHAR(3) will "act" like x CHAR(3 CHAR). Double check when recompiling that one sees in the NLS_SESSION_PARAMETERS= table {{NLS_LENGTH_SEMANTICS = CHAR.

One can also check the used NLS_LENGTH_SEMANTICS session setting for any PL/SQL object in the NLS_LENGTH_SEMANTICS column of DBA_PLSQL_OBJECT_SETTINGS.

As it's rather hard to control the environment settings for 100% it is suggested to use explicit CHAR semantics in your code.

  • x CHAR (3 CHAR);
  • y VARCHAR2 (10 CHAR);

HOW TO GO TO CHAR SEMANTICS ON CLOUMN LEVEL

USE exp/imp

  • The import utility uses the exact semantics defined on the source tables from the export dump file, NOT from the target database settings. If the source tables are defined with BYTE semantics then they will be created with BYTE semantics, even when importing into a database that has NLS_LENGTH_SEMANTICS set to CHAR.
  • To migrate tables from BYTE to CHAR semantics using Full export/import do one of 3 things:
    • From the export, extract the create table statements with imp show=Y and pre-create the tables on the target side with the session NLS_LENGTH_SEMANTICS set to CHAR and then import with IGNORE=Y. This will create any column that has no explicit semantics defined with CHAR semantics.
  • Pre-create the tables on the target side with the explicit CHAR semantics in the column definitions, e.g. CHAR (20 CHAR) and then import with IGNORE=Y.
  • Change the tables explicitly to CHAR before taking the export - see Use ALTER TABLE MODIFY below.

USE ALTER TABLE MODIFY

One can make a script that alters all the column definitions of one's table.

  • To change a table containing a CHAR(10) col then one can issue
    • alter table <owner>.<table> modify <column> char (10 char);
  • To change a table containing a VARCHAR2(200) col then you can
    • alter table <owner>.<table> modify <column> varchar2 (200 char);

Note that there are some restrictions with alter table modify, consult the SQL Reference Guide for more info.

CAN CHAR SEMANTICS BE USED FOR TYPES?

Yes, the attributes of a type can also be defined as having either CHAR or BYTE length semantics.

SUMMARY OF BEST PRACTICES

  • Check for columns that are < 666 bytes for CHAR datatype and < 1333 bytes for VARCHAR2 datatype. Consider to use CLOB for VARCHAR2 or VARCHAR2 for CHAR columns or make sure the application layer limits the input string -or- make at least sure the application layer handles any ORA-01401 / ORA-12899 exception gracefully.
  • If you transfer data using database links from other databases strongly consider to use CHAR semantics for column definitions on the remote database, even if the remote database is using a 7/8 bit NLS_CHARACTERSET.
  • Use explicit CHAR semantic definitions in PL/SQL procedures, Type definitions, etc.
  • Use explicit CHAR semantic definitions in create table statements.
  • Change all existing table columns to CHAR semantics using one of the 2 options listed above.
  • Avoid to use a mixture of BYTE and CHAR semantics in the same table

INDEXES CREATED WITH A DIFFERENT NLS_LENGTH_SEMANTICS VALUE THAN THE TABLE

  • The index creation uses the table column definition:
    • thus, the index column COLUMN_LENGTH = table column DATA_LENGTH, and
    • the index column CHAR_LENGTH = table column CHAR_LENGTH.
  • Modifying the semantics of a table column => the associated index is automatically modified.
© copyright 2001-2014 ABCdba.com | all rights reserved