1 Reply Latest reply: Mar 17, 2011 1:34 PM by Sergiusz Wolicki-Oracle RSS

    Character length semantic recommendation

      The Oracle Database Globalization Support Guide (http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch2charset.htm#i1006683) recommends setting the NLS_LENGTH_SEMANTICS in the initialization parameter file:

      +The NLS_LENGTH_SEMANTICS initialization parameter determines whether a new column of character datatype uses byte or character semantics. The default value of the parameter is BYTE. The BYTE and CHAR qualifiers shown in the VARCHAR2 definitions should be avoided when possible because they lead to mixed-semantics databases. Instead, set NLS_LENGTH_SEMANTICS in the initialization parameter file and define column datatypes to use the default semantics based on the value of NLS_LENGTH_SEMANTICS.+

      However, in an Ask Tom post (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1224836384599), Sergiusz Wolicki recommends against doing this very thing:

      VARCHAR2(20) becoming VARCHAR2(20 CHAR) in all CREATE TABLE statements following in the same
      +session. If you set NLS_LENGTH_SEMANTICS=CHAR in spfile, which you should never do (!), this+
      default will change for all following sessions (unless overridden individually be a session back to

      Based on what I know of Sergiusz (from online posts), I'm inclined to go with his recommendation. However, I'd like to know why he differs from what the documentation says. It's a bit confusing. Can someone (esp. Sergiusz) clear this up?

      A second clarification:
      My understanding of Sergiusz's recommendation is...
      1) set the session parameter NLS_LENGTH_SEMANTICS=CHAR
      2) create tables like this:
      create table t1(c1 varchar2)
      He is not recommending "hard-coding" the length semantics into the DDL...
      create table t1(c1 varchar2 char)

      Is my understanding correct?
        • 1. Re: Character length semantic recommendation
          Sergiusz Wolicki-Oracle
          There is bug #10143226 open to fix the documentation according to my recommendations. The wrong recommendations are in Reference, SQL Reference, and Globalization Guide (at least). These recommendations were given when the character length semantics feature was introduced into Oracle SQL, before it became obvious that compatibility and clarity is more important than some pseudo-pros of using the initialization parameter.

          The proposed new wording of the problematic paragraph from your post is:

          The length semantics of character data type columns, user-defined type attributes, and PL/SQL variables can be specified explicitly in their definitions with the BYTE or CHAR qualifier. This method of specifying the length semantics is recommended as it properly documents the expected semantics in creation DDL statements and makes the statements independent of any execution environment.

          If a column, user-defined type attribute or PL/SQL variable definition contains neither the BYTE nor the CHAR qualifier, the length semantics associated with the column, attribute, or variable is determined by the value of the session parameter NLS_LENGTH_SEMANTICS.  If you create database objects with legacy scripts that are too large and complex to be updated to include explicit BYTE and/or CHAR qualifiers, execute an explicit ALTER SESSION SET NLS_LENGTH_SEMANTICS statement before running each of the scripts to assure that the scripts create objects in the expected semantics.

          The NLS_LENGTH_SEMANTICS initialization parameter determines the default value of the NLS_LENGTH_SEMANTICS session parameter for new sessions. Its default value is BYTE. For the sake of compatibility with existing application installation procedures, which may have been written before character length semantics was introduced into Oracle SQL, Oracle recommends that you leave this initialization parameter undefined or you set it to BYTE. Otherwise, created columns may be larger than expected, causing applications to malfunction or, in some cases, cause buffer overflows.

          As you see, I do recommend hardcoding the semantics into the DDL. If you find it reasonable that column data types and lengths are always specified in the DDL and not in some session parameters, you will understand that it is good practice to specify the semantics in the same way.

          -- Sergiusz