Default Character type of the column [message #656277] |
Sat, 01 October 2016 14:15 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All ,,
what is the default character type of the column in oracle .
I had tested with one example, its giving as BYTE.
Is this parameter dependent ?
Oracle itself provides BYTE as default ?
Table SR_CHAR dropped.
Table SR_BYTE dropped.
Table SR_CHAR created.
Name Null Type
---- ---- ------------
NAME VARCHAR2(20)
Table SR_BYTE created.
Name Null Type
---- ---- -----------------
NAME VARCHAR2(20 CHAR)
TABLE_NAME COLUMN_NAME C CHAR_LENGTH CHAR_COL_DECL_LENGTH DATA_TYPE
------------------------------ ------------------------------ - --------------------------------------- --------------------------------------- ----------------------------------------------------------------------------------------------------------
SR_BYTE NAME C 20 80 VARCHAR2
SR_CHAR NAME B 20 20 VARCHAR2
[Edit MC: remove 20 empty lines at the end]
[Updated on: Sat, 01 October 2016 14:36] by Moderator Report message to a moderator
|
|
|
|
|
Re: Default Character type of the column [message #656283 is a reply to message #656277] |
Sat, 01 October 2016 17:02 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The nls_length_semantics determines whether byte or char is used. This is set in the nls_database_parameters, which can be overridden for the session in the nls_session_parameters. As you have already found, you can also override both in your table creation. The default for the nls_length_semantics is BYTE as stated in the section of the online documentation below. So, if you do not set or alter the nls_length_semantics, the default is BYTE.
http://docs.oracle.com/database/121/REFRN/GUID-221B0A5E-A17A-4CBC-8309-3A79508466F9.htm#REFRN10124
The following two demonstrations show that changing the nls_length_semantics for the session changes the default behavior for the session, without affecting the setting for the database.
-- BYTE:
SCOTT@orcl_12.1.0.2.0> alter session set nls_length_semantics = 'BYTE'
2 /
Session altered.
SCOTT@orcl_12.1.0.2.0> select parameter, value
2 from nls_database_parameters
3 where parameter = 'NLS_LENGTH_SEMANTICS'
4 /
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LENGTH_SEMANTICS BYTE
1 row selected.
SCOTT@orcl_12.1.0.2.0> select parameter, value
2 from nls_session_parameters
3 where parameter = 'NLS_LENGTH_SEMANTICS'
4 /
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LENGTH_SEMANTICS BYTE
1 row selected.
SCOTT@orcl_12.1.0.2.0> create table test_tab (test_col varchar2(20))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> select table_name, column_name, char_used
2 from user_tab_columns
3 where table_name = 'TEST_TAB'
4 /
TABLE_NAME COLUMN_NAME C
---------- ----------- -
TEST_TAB TEST_COL B
1 row selected.
-- CHAR:
SCOTT@orcl_12.1.0.2.0> drop table test_tab
2 /
Table dropped.
SCOTT@orcl_12.1.0.2.0> alter session set nls_length_semantics = 'CHAR'
2 /
Session altered.
SCOTT@orcl_12.1.0.2.0> select parameter, value
2 from nls_database_parameters
3 where parameter = 'NLS_LENGTH_SEMANTICS'
4 /
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LENGTH_SEMANTICS BYTE
1 row selected.
SCOTT@orcl_12.1.0.2.0> select parameter, value
2 from nls_session_parameters
3 where parameter = 'NLS_LENGTH_SEMANTICS'
4 /
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LENGTH_SEMANTICS CHAR
1 row selected.
SCOTT@orcl_12.1.0.2.0> create table test_tab (test_col varchar2(20))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> select table_name, column_name, char_used
2 from user_tab_columns
3 where table_name = 'TEST_TAB'
4 /
TABLE_NAME COLUMN_NAME C
---------- ----------- -
TEST_TAB TEST_COL C
1 row selected.
|
|
|
Re: Default Character type of the column [message #656284 is a reply to message #656280] |
Sat, 01 October 2016 17:18 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
saipradyumn wrote on Sat, 01 October 2016 12:54HI Mike ,
Sorry for creating the confusion & thanks for your valuable information .
I am able to find the value AL32UTF8 in NLS_CHARACTERSET of NLS_DATABASE_PARAMETERS.
What does it mean ?
For information about Oracle character sets in general, please see the following section of the online documentation. You can search within that for specific information on AL32UTF8. In general, it is a universal character set that can be used with multiple languages, including those with multi-byte characters.
http://docs.oracle.com/database/121/NLSPG/ch2charset.htm#NLSPG002
[Updated on: Sat, 01 October 2016 17:20] Report message to a moderator
|
|
|
|
Re: Default Character type of the column [message #657237 is a reply to message #656283] |
Wed, 02 November 2016 06:08 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Sat, 01 October 2016 18:02The nls_length_semantics determines whether byte or char is used. This is set in the nls_database_parameters, which can be overridden for the session in the nls_session_parameters.
Not exactly. nls_session_parameters inherit values from nls_instance_parameters, not from nls_database_parameters. But even then, since session parameters are client driven, nls_instance_parameters are not first in line. Oracle checks, at session logon time, client environment variables (except JDBC thin connections that do not pass client environment variables to database server). If client environment variable nls_length_semantics is set then session inherits nls_length_semantics from it. Otherwise it inherits nls_length_semantics from nls_instance_parameters which in turn inherits (if not explicitly set in spfile) it from nls_database_parameters. And, IMHO, Oracle didn't do good job to make it "user friendly". In fact, I don't see any reasons even for having such parameter. I just can't think of a case when user declaring CHAR(10)/VARCHAR2(10) column/variable would want to treat 10 as 10 bytes and not 10 characters. We store text in CHAR/VARCHAR2, not bytes. We can't rely on clients always setting environment variables, so setting nls_length_semantics to CHAR in nls_instance_parameters seems like a logical step. However, oracle warns you against it since data dictionary relies on single byte characters and "This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows". Which again, I don't fully understand since "Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter" they always use BYTE as default.
SY.
|
|
|