Re: Character Sets in 11g

Yes, you can create such a trigger (tested in 11.2.0.3):

SQL> create or replace trigger length_semantics
  2  after logon on database
  3  declare
  4           sqltxt varchar2(4000);
  5  begin
  6           if user='BING' then
  7               sqltxt:='alter session set nls_length_semantics=byte';
  8           else
  9               sqltxt:='alter session set nls_length_semantics=char';
 10           end if;
 11           execute immediate sqltxt;
 12  end;
 13  /

Trigger created.

SQL>
SQL> show errors trigger length_semantics
No errors.
SQL>
SQL>
SQL> connect bing/###########
Connected.
SQL>
SQL> show parameter semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE
SQL>
SQL> connect gribnaut/################
Connected.
SQL>
SQL> show parameter semantic

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL>


 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"




On Thursday, January 16, 2014 1:06 PM, Chris King <ckaj111_at_yahoo.ca> wrote:
 
Is there a way to set the NLS_LENGTH_SEMANTICS to as specific value for a specific user? 

My intent is: In order to share the same database, I would create a schema with its own tablespace for each application. One application requires the NLS_LENGTH_SEMANTICS =CHAR and the other NLS_LENGTH_SEMANTICS =BYTE. Is it possible to set this parameter in a trigger when the user logs on? Would this cause any corruption in data storage if I did so? Or will I have to create separate databases to accommodate each application?




On Thursday, January 16, 2014 2:58:55 PM, Chris King <ckaj111_at_yahoo.ca> wrote:
 
This is for BMC Server Automation.

I am starting to think that by CHAR schema they're referring to using the char vs. nchar charactersets.. (they use a lot of the wrong nomenclature for oracle in their documentation..)





On Thursday, January 16, 2014 2:48:24 PM, David Fitzjarrell <oratune_at_yahoo.com> wrote:
 
Unfortunately I don't get this instruction either.  Yes, America:American would be considered a LATIN-1 locale but what schema is this CHAR schema?  It's a reserved word in Oracle so I can't see why anyone would create a user named CHAR, much less create objects in that schema.

Can  you provide any additional information, such as the application providing these instructions?

 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"




On Thursday, January 16, 2014 12:16 PM, Chris King <ckaj111_at_yahoo.ca> wrote:
 
I'm trying to understand an instruction the following instructions from an application installation guide. It says:


If the database is configured for the UTF8 or
AL32UTF8 character set (NLS_CHARACTERSET parameter) in a LATIN-1 locale, and
the CHAR schema is used as an underlying persistent schema, set the NLS_LENGTH_SEMANTICS
parameter of the database to CHAR to prevent column field overflow.

I'm using UTF8 and AL32UTF8 with America (language), American (territory). Is this considered a Latin-1 locale? 


I'm somewhat confused because we typically use the western characterset, which I know includes latin characters. But I think locale refers to the language and territory? 


Also how do I determine what the underlying persistent schema is? 

I can't just set NLS_LENGTH_SEMANTICS to CHAR unless it's necessary, as I have a conflicting requirements from another application which will share this database, so understanding this will be key to how I set up the database(s).


Thanks in advance!

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