Hi All,
I would like to understand the difference amongst the LENGTH-type columns in the USER_TAB_COLUMNS view.
I have following environment:
SELECT * FROM V$VERSION;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SELECT PARAMETER, VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LENGTH_SEMANTICS BYTE
Say that I create following table:
CREATE TABLE TEST_TABLE(
STRING_1 VARCHAR2(10) DEFAULT 'a',
STRING_2 VARCHAR2(10 CHAR) DEFAULT 'a',
STRING_3 NVARCHAR2(10) DEFAULT 'a'
);
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_LENGTH, CHAR_COL_DECL_LENGTH, DEFAULT_LENGTH, CHAR_USED
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TEST_TABLE';
TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_LENGTH, CHAR_COL_DECL_LENGTH, DEFAULT_LENGTH, CHAR_USED
TEST_TABLE STRING_1 VARCHAR2 10 10 10 3 B
TEST_TABLE STRING_2 VARCHAR2 40 10 40 3 C
TEST_TABLE STRING_3 NVARCHAR2 20 10 10 4 C
Documentation for above mentioned columns says following:
DATA_LENGTH Length of the column (in bytes)
DEFAULT_LENGTH Length of the default value for the column
CHAR_COL_DECL_LENGTH Declaration length of the character type column
CHAR_LENGTH Displays the length of the column in characters. This value only applies to the following datatypes: CHAR, VARCHAR2, NCHAR, NVARCHAR2
Looking at the values provided by the query, values in columns DATA_LENGTH and CHAR_LENGTH are understandable and I would say correct (UTF8 can have up to 4B per character, UTF16 has 2B per character).
However what are the values of CHAR_COL_DECL_LENGTH and DEFAULT_LENGTH??? Documentation says nothing about whether it is in units of characters or bytes - seems to be both. DEFAULT_LENGTH is completely out of my understanding...
Thanks in advance for all good ideas.
Dan