Oracle between data_length vs char_length difference

Oracle Doc defind describes the columns

Column:      DATA_LENGTH
Datatype:    NUMBER
Description: Length of the column (in bytes)

Column:      CHAR_LENGTH
Datatype:    NUMBER
Description: Displays the length of the column in characters. This value only applies to the following data types:
CHAR、VARCHAR2、NCHAR、NVARCHAR2

Column:   CHAR_USED
Datatype: VARCHAR2(1)
Description: Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C), or whether the data type is not any of the following (NULL):
CHAR、VARCHAR2、NCHAR、NVARCHAR2

Example:

SQL> create table t1 (c1 int, c2 varchar2(100), c3 varchar2(100 char));
Table created.

Query data_length and char_length, and found char_used is 'C', the char_length and data_length are difference length

SQL> select column_name, char_used, char_length, data_length 
  2  from user_tab_columns 
  3  where table_name = 'T1';

COLUMN_NAME                    C CHAR_LENGTH DATA_LENGTH
------------------------------ - ----------- -----------
C1                                         0          22
C2                             B         100         100
C3                             C         100         400

Query database server characterset

SQL> select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET');

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8                       Character set

The database server characterset is AL32UTF8, store 1 charter is 4 bytes

100(chars) x 4(bytes each) = 400(bytes)

Maybe best a query suitable for DATA_LENGTH

SQL> select column_name, DECODE(char_used,'C',char_length,data_length) data_length from user_tab_columns where table_name = 'T1';

COLUMN_NAME                    DATA_LENGTH
------------------------------ -----------
C1                                      22
C2                                     100
C3                                     100

Ref:

DBA_TAB_COLUMNS

VARCHAR2 Length Mismatches in Table Description in Oracle

張貼留言

0 留言