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