2 Replies Latest reply: Jan 17, 2013 4:17 AM by Niket Kumar RSS

    Column size discrepancy

      OS : RHEL 5.4

      In the below table, PRODUCT_SEGMENT_ID column is created with VARCHAR2(20 CHAR).
      But it is showing up as 80 IN dba_tab_columns.data_length ? Why is that ?
      Name                    Type               Nullable Default Comments 
      ----------------------- ------------------ -------- ------- -------- 
      MONTH_ID                NUMBER(6)          Y                         
      CLIENT_ID               VARCHAR2(100 CHAR) Y                         
      MGMT_CTRY_ID            VARCHAR2(4 CHAR)   Y                         
      PRODUCT_SEGMENT_ID      VARCHAR2(20 CHAR)  Y    -----------> column in question                     
      RWA_AMOUNT              NUMBER(20,5)       Y                         
      TOTAL_REVENUE_AMOUNT    NUMBER(20,5)       Y   
      SQL> col data_type format a25
      SQL> col column_name format a25
      SQL> set lines 200
      SQL> select column_name, Data_type, data_length, char_length
        2   from dba_tab_columns WHERE TABLE_NAME = 'CRTN_MONTH_DTL'
        3  and column_name = 'PRODUCT_SEGMENT_ID' and OWNER = 'STRP_SLS';
      COLUMN_NAME               DATA_TYPE                 DATA_LENGTH CHAR_LENGTH
      ------------------------- ------------------------- ----------- -----------
      PRODUCT_SEGMENT_ID        VARCHAR2                           80          20
      I extracted the CREATE TABLE ddl for this table . It confirms that PRODUCT_SEGMENT_ID is created with VARCHAR2(20 CHAR)
      create table STRP_SLS.CRTN_MONTH_DTL
      month_id                NUMBER(6),
      client_id               VARCHAR2(100 CHAR),
      mgmt_ctry_id            VARCHAR2(4 CHAR),
      PRODUCT_SEGMENT_ID      VARCHAR2(20 CHAR), -----------> column in question
      rwa_amount              NUMBER(20,5),
      total_revenue_amount    NUMBER(20,5),