CODEWITHSUNDEEP
oracle-database
shabar
shabar

Reputation: 128

VARCHAR2 Length Mismatches in Table Description in Oracle

Just wondering in which table oracle stores Table Description that appear with following command

DESC DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC

Output:

DESC DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC
Name                 Null     Type                
-------------------- -------- ------------------- 
WORKORDERSPECID      NOT NULL NUMBER              
**ALNVALUE                      VARCHAR2(4000 CHAR)** 
ASSETATTRID          NOT NULL VARCHAR2(100)       
WONUM                NOT NULL VARCHAR2(15)        
CHANGEBY             NOT NULL VARCHAR2(30)
**NBNTARGETOBJECT               VARCHAR2(30 CHAR)   
NBNTARGETATTRIBUTE            VARCHAR2(50 CHAR)**    
CHANGEDATE           NOT NULL DATE                
CLASSSTRUCTUREID     NOT NULL VARCHAR2(20)        
DISPLAYSEQUENCE      NOT NULL NUMBER              
LINKEDTOATTRIBUTE             VARCHAR2(100)       
LINKEDTOSECTION               VARCHAR2(20)        
MEASUREUNITID                 VARCHAR2(25)        
NUMVALUE                      NUMBER(30,10)

I used following, but in VARCHAR2 data type could see length mismatches as highlighted

Decription

Upvotes: 1

Views: 2912

Answers (2)

Ulrik Larsen
Ulrik Larsen

Reputation: 139

Works like a charm NM, only there are minor spelling errors in your code and then we get for example:

select 
column_name,
DECODE(char_used,'C',char_length,data_length) data_length
from
all_tab_columns
where owner = upper(:1)
and table_name = upper(:2)

Thanks!

Upvotes: 1

Naeel Maqsudov
Naeel Maqsudov

Reputation: 1434

ALL_TABLES and ALL_TAB_COLUMNS should be enough to retrieve any metainformation on the table structure. According to documentation ALL_TAB_COLUMNS.DATA_LENGTH shows length in bytes and ALL_TAB_COLUMNS.CHAR_LENGTH — in characters according to length semantics applied to the column.

Instead of DATA_LENGTH in your query try
DECODE(char_used,'C',char_lenght,data_lenght) data_length

Additionally pay attention to select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET'). Number of bytes depends on character set.

30(chars) x 4(bytes each) = 120(bytes)

So I guess your database uses AL32UTF8 character set as a default character set.

Upvotes: 7

Related Questions