Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: nls_length_semantics anyone see this before (repeat e-mail)
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
PARAMETER VALUESQL> ------ By default database and session NLS_LENGTH_SEMANTICS are both BYTE SQL> select 'DATABASE:' as lvl, value
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 SQL> ------ ##############################################################
LVL VALUESQL> ------ Creating a table: varchar2 (30) will be (30 byte) SQL> create table t1_byte_setting (v varchar2 (30)) ; Table créée.
--------- ------------------------------
DATABASE: BYTE SESSION: BYTE SQL> ------ ##############################################################
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 VALUESQL> ------ Creating a table: varchar2 (30) will, this time, be (30 char) SQL> create table t2_char_setting (v varchar2 (30)) ; Table créée.
--------- ------------------------------
DATABASE: BYTE SESSION: CHAR SQL> ------ ##############################################################
SQL> ------ ##############################################################SQL> ------ Notice the difference in DATA_LENGTH SQL> select table_name, column_name, data_length, char_length 2 from user_tab_columns
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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Fri May 21 2004 - 12:49:24 CDT
-----------------------------------------------------------------
![]() |
![]() |