DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type;
  b=rLTS9O02nGbNWygOz6luf/o03wpd2Q0GbjbfmI6nd+3x1CbthBAICkq+XSGTx8c8gXtZv22IMC47W49haRtAcyOBwLgXJT3YC3Vx2hOIxJuzprCxywtFh576RlJWgrba4OXuYS//8h55QkOgWWB2PlrBR1Qn9qwDTIKA4jT2m80=;
References: <1370536139.11081.YahooMailNeo@web120004.mail.ne1.yahoo.com> <CAFU3ey7jBpv+VB_04PHRg8-Lqh_Jc2b3CuBUNSiP2O3qLgaSmA@mail.gmail.com>
Message-ID: <1370552219.52991.YahooMailNeo@web120006.mail.ne1.yahoo.com>
Date: Thu, 6 Jun 2013 13:56:59 -0700 (PDT)
From: Lu Jiang <lu.jiang69@yahoo.com>
Subject: Re: NLS_LENGTH_SEMANTICS=CHAR does not set the default character length type to char
To: "watt0012@umn.edu" <watt0012@umn.edu>
Cc: list <oracle-l@freelists.org>
In-Reply-To: <CAFU3ey7jBpv+VB_04PHRg8-Lqh_Jc2b3CuBUNSiP2O3qLgaSmA@mail.gmail.com>
MIME-Version: 1.0
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: lu.jiang69@yahoo.com

No, I logged in as an application user. 
Set this parameter at instance level is required by the application vendor. I like to explicitly delared it in ddl too.
 
Thanks,
Lu

________________________________
From: Andy Wattenhofer <watt0012@umn.edu>
To: lu.jiang69@yahoo.com 
Cc: list <oracle-l@freelists.org> 
Sent: Thursday, June 6, 2013 1:13 PM
Subject: Re: NLS_LENGTH_SEMANTICS=CHAR does not set the default character length type to char


Are you connected as SYS user when you do these tests? From the Oracle
documentation:
Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter.
> They use BYTE length semantics for all created objects unless overridden by
> the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL
> statements).


I would also reiterate Oracle's recommendation that you do not set this
parameter at the instance level unless the alternatives are not workable.
Alternatives are to set it at the session level or explicitly declare it in
your DDL.

Andy


On Thu, Jun 6, 2013 at 11:28 AM, Lu Jiang <lu.jiang69@yahoo.com> wrote:

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


-- 
Andy Wattenhofer
Manager, Database Administration
University of Minnesota


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

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