Home » SQL & PL/SQL » SQL & PL/SQL » Default Character type of the column (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Default Character type of the column [message #656277] Sat, 01 October 2016 14:15 Go to next message
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 #656278 is a reply to message #656277] Sat, 01 October 2016 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't you think it would be better to post the statements you executed instead of "Table SR_CHAR created".
We even don't know what represents your result!

You mix up character set and character length.
The default character length unit is given by the NLS_LENGTH_SEMANTICS parameter.
The character set for CHAR, VARCHAR2, CLOB is ALWAYS the database character set given by the parameter NLS_CHARACTERSET in NLS_DATABASE_PARAMETERS.

Re: Default Character type of the column [message #656280 is a reply to message #656278] Sat, 01 October 2016 14:54 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
HI 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 ?
Re: Default Character type of the column [message #656283 is a reply to message #656277] Sat, 01 October 2016 17:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
saipradyumn wrote on Sat, 01 October 2016 12:54
HI 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 #657222 is a reply to message #656284] Wed, 02 November 2016 01:27 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks
Re: Default Character type of the column [message #657237 is a reply to message #656283] Wed, 02 November 2016 06:08 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara Boehmer wrote on Sat, 01 October 2016 18:02
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.
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.
Previous Topic: ORA-06502 with UTL_RAW
Next Topic: Column dropped
Goto Forum:
  


Current Time: Thu Mar 28 09:44:10 CDT 2024