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

From: Timo Raitalaakso <rafu_at_iki.fi>
Date: Sat, 08 Jun 2013 18:22:11 +0300
Message-ID: <51B34C23.1040400_at_iki.fi>



You have hit most probably Bug 1488174 UNICODE: ALTER SYSTEM SET NLS_LENGTH_SEMANTICS DOESN'T TAKE EFFECT Problem: ALTER SYSTEM does not change the setting of NLS_LENGTH_SEMANTICS for the current and new (!) sessions. Workaround: Don't use ALTER SYSTEM SET NLS_LENGTH_SEMANTICS (scope=both); but set NLS_LENGTH_SEMANTICS as a init.ora parameter or issue ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile; and bounce the database.
or
use alter session to change the current session without bouncing the database (but new sessions will still use the old value...).
--
Timo Raitalaakso
http://rafudb.blogspot.com

6.6.2013 19:28, Lu Jiang kirjoitti:

> 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
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Sat Jun 08 2013 - 17:22:11 CEST

Original text of this message