Re: 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 13:56:59 -0700 (PDT)
Message-ID: <1370552219.52991.YahooMailNeo_at_web120006.mail.ne1.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_at_umn.edu> To: lu.jiang69_at_yahoo.com
Cc: list <oracle-l_at_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_at_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

Received on Thu Jun 06 2013 - 22:56:59 CEST