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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 21 May 2004 10:52:01 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87864@irvmbxw02>

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_at_freelists.org 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


-----------------------------------------------------------------

Received on Fri May 21 2004 - 12:49:24 CDT