NLS_LENGTH_SEMANTICS=CHAR does not set the default character length type to char

From: Lu Jiang <lu.jiang69_at_yahoo.com>
Date: Thu, 6 Jun 2013 09:28:59 -0700 (PDT)
Message-ID: <1370536139.11081.YahooMailNeo_at_web120004.mail.ne1.yahoo.com>

Hi all,
 

I just found that set parameter NLS_LENGTH_SEMANTICS=CHAR at instance level does not make the default character length type to char when created table with  char/varchar column in one of our11g database. Has any one seen this before?
 

The following if what I got from this database:
 

1.
SQL> show parameter length
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

nls_length_semantics string CHAR

2. I created a table as following, but the length semantics does not use the default 'char' SQL> Create table test (Col1 CHAR(20),Col2 VARCHAR2(100));

Table created.

SQL> desc test
Name Null? Type

----------------------------------------- -------- ----------------------------

COL1 CHAR(20 byte)
COL2 VARCHAR2(100 byte)

3. Create a table with the length semantic explicitly specify to char

SQL> Create table test1 (Col1 CHAR(20 char),Col2 VARCHAR2(100 char));

Table created.

SQL> desc test
Name Null? Type

----------------------------------------- -------- ----------------------------

COL1 CHAR(20 CHAR)
COL2 VARCHAR2(100 CHAR) Although define the the data type explicitly is a good practice, but i don't know why set NLS_LENGTH_SEMANTICS parameter is useless.  Could any one shed any light on this?
 

Thanks,
Lu

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 06 2013 - 18:28:59 CEST