RE: nls_length_semantics anyone see this before

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 21 May 2004 10:47:38 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87863@irvmbxw02>

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                                     =


                          =20

------------------------------ ------------------------------            =


                          =20

NLS_CHARACTERSET               AL32UTF8                                  =


                          =20

NLS_NCHAR_CHARACTERSET         AL16UTF16                                 =


                          =20

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 =3D 'NLS_LENGTH_SEMANTICS'   4 union
  5 select 'SESSION:' as lvl, value
  6 from nls_database_parameters
  7 where parameter =3D 'NLS_LENGTH_SEMANTICS'   8 order by 1 ;

LVL       VALUE                                                          =


                          =20

--------- ------------------------------                                 =


                          =20

DATABASE: BYTE                                                           =


                          =20

SESSION:  BYTE                                                           =


                          =20

SQL> ------ =

##############################################################

SQL> ------ Creating a table: varchar2 (30) will be (30 byte) SQL> create table t1_byte_setting (v varchar2 (30)) ; Table cr=E9=E9e.

SQL> ------ =

##############################################################

SQL> ------ Change NLS_LENGTH_SEMANTICS for SESSION to CHAR SQL> alter session set nls_length_semantics =3D 'CHAR' ; Session modifi=E9e.

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

LVL       VALUE                                                          =


                          =20

--------- ------------------------------                                 =


                          =20

DATABASE: BYTE                                                           =


                          =20

SESSION:  CHAR                                                           =


                          =20

SQL> ------ =

##############################################################

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

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              =20
------------------------------ ------------------------------ =
----------- -----------              =20
T1_BYTE_SETTING                V                                       =
30          30              =20
T2_CHAR_SETTING                V                                      =
120          30              =20

=20

-----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:45:03 CDT