RE: nls_length_semantics anyone see this before (repeat e-mail)
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 21 May 2004 10:52:01 -0700
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
