RE: nls_length_semantics anyone see this before (repeat e-mail)

yes,
thats true but as we just found out this is a BUG
the description is as below and as helpful as oracle always is its not fixed 
till 10gR2
 [BUG:1488174] UNICODE: ALTER SYSTEM SET NLS_LENGTH_SEMANTICS DOESN'T TAKE 
EFFECT 
(this is an internal bug, sorry) 
 Fixed Ver(s): will not be fixed before 10g R2 
 Testcase: 
     alter system set nls_length_semantics='CHAR' 
     create table test(v1 varchar2(10)); 
   it should be 10 character, but actually, it is 10 BYTE; 
 Workarounds:  
   alter session, or set the parameter in the init.ora. 
 Problem:  
   ALTER SYSTEM does not change the current session's setting of 
   NLS_LENGTH_SEMANTICS, at least not in the opinion of DDL. 
 Workaround: Don't use ALTER SYSTEM SET NLS_LENGTH_SEMANTICS ... 
 Both init.ora parameters and alter session work OK. 
 

Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx> wrote:

Sorry - resending because I forgot to change the e-mail format to HTML and I 
want to get rid of the "quoted-printable" characters.

NLS_LENGTH_SEMANTICS can be set at the session level. If you expect the value 
to be BYTE and it's set to CHAR this will modify the way in which VARCHAR2 
columns are created. If NLS_LENGTH_SEMANTICS is set in the appropriate place in 
your Windows registry (for a Windows client) or as an environment variable 
(UNIX client) or in a login.sql file then the value from the registry / 
environment variable / login.sql will be the effective one for any new 
connection.

This can lead to unexpected results when creating a table. See example below.

SQL> ------ ##############################################################
SQL> ------ Database has multibyte character set
SQL> select
  2    parameter, value
  3   from nls_database_parameters
  4   where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET') ;

PARAMETER                      VALUE                                            
                   
------------------------------ ------------------------------                   
                   
NLS_CHARACTERSET               AL32UTF8                                         
                   
NLS_NCHAR_CHARACTERSET         AL16UTF16                                        
                   

SQL> ------ ##############################################################
SQL> ------ By default database and session NLS_LENGTH_SEMANTICS are both BYTE
SQL> select 'DATABASE:' as lvl, value
  2    from nls_session_parameters
  3    where parameter = 'NLS_LENGTH_SEMANTICS'
  4  union
  5  select 'SESSION:' as lvl, value
  6    from nls_database_parameters
  7    where parameter = 'NLS_LENGTH_SEMANTICS'
  8  order by 1 ;
LVL       VALUE                                                                 
                   
--------- ------------------------------                                        
                   
DATABASE: BYTE                                                                  
                   
SESSION:  BYTE                                                                  
                   

SQL> ------ ##############################################################
SQL> ------ Creating a table: varchar2 (30) will be (30 byte)
SQL> create table t1_byte_setting (v varchar2 (30)) ;
Table créée.

SQL> ------ ##############################################################
SQL> ------ Change NLS_LENGTH_SEMANTICS for SESSION to CHAR
SQL> alter session set nls_length_semantics = 'CHAR' ;
Session modifiée.

SQL> select 'DATABASE:' as lvl, value
  2    from nls_database_parameters
  3    where parameter = 'NLS_LENGTH_SEMANTICS'
  4  union
  5  select 'SESSION:' as lvl, value
  6    from nls_session_parameters
  7    where parameter = 'NLS_LENGTH_SEMANTICS'
  8  order by 1 ;
LVL       VALUE                                                                 
                   
--------- ------------------------------                                        
                   
DATABASE: BYTE                                                                  
                   
SESSION:  CHAR                                                                  
                   

SQL> ------ ##############################################################
SQL> ------ Creating a table: varchar2 (30) will, this time, be (30 char)
SQL> create table t2_char_setting (v varchar2 (30)) ;
Table créée.

SQL> ------ ##############################################################
SQL> ------ Notice the difference in DATA_LENGTH
SQL> select table_name, column_name, data_length, char_length
  2   from user_tab_columns
  3   where table_name in ('T1_BYTE_SETTING', 'T2_CHAR_SETTING') ;
TABLE_NAME                     COLUMN_NAME                    DATA_LENGTH 
CHAR_LENGTH              
------------------------------ ------------------------------ ----------- 
-----------              
T1_BYTE_SETTING                V                                       30       
   30              
T2_CHAR_SETTING                V                                      120       
   30              


-----Original Message-----
Fuad Arshad

has anyone encountered this with nls_length_semantics before.

The issue that i'm having is  someone changed the nls_length semantics to char 
and now  we try to change the parameter but  it still stays tha same on the 
session level.

thus we are unable to change the  table unless we specify the session level 
change.



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: