2 Replies Latest reply on Jan 17, 2013 10:17 AM by Niket Kumar

    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),
        • 1. Re: Column size discrepancy
          By defining the field as VARCHAR2(20 CHAR) you tell Oracle to allocate enough space to store 20 characters, no matter how many bytes it takes to store each one. (but in your case it is 4 byte depend on database character set)
          • 2. Re: Column size discrepancy
            Niket Kumar
            you can see char length is 20 which is equal to varchar2(20) and data length is equal to Length of the column in bytes.......
            you can add 20 characters in column which have size upto 80bytes...
            1 person found this helpful