2 Replies Latest reply: Mar 1, 2011 1:51 PM by Ganesh Srivatsav RSS

    SQL - How to check data type definition

    843791
      Is it possible to check (inside SQL statement) how particular field is defined. Is it VARCHAR2(5) or VARCHAR2(10)
        • 1. Re: SQL - How to check data type definition
          Solomon Yakobson
          If by field you mean column, then use USER_TAB_COLUMNS, ALL_TAB_COLUMNS or DBA_TAB_COLUMNS:
          SELECT  TABLE_NAME || '.' || COLUMN_NAME || ' ' || DATA_TYPE ||
                  CASE
                    WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || DATA_LENGTH || ')'
                    WHEN DATA_TYPE = 'NUMBER' THEN '(' || DATA_PRECISION  || ',' || DATA_SCALE || ')'
                  END X
            FROM  USER_TAB_COLUMNS
            WHERE TABLE_NAME = 'EMP'
            ORDER BY COLUMN_ID
          /
          
          X
          -------------------------
          EMP.EMPNO NUMBER(4,0)
          EMP.ENAME VARCHAR2(10)
          EMP.JOB VARCHAR2(9)
          EMP.MGR NUMBER(4,0)
          EMP.HIREDATE DATE
          EMP.SAL NUMBER(7,2)
          EMP.COMM NUMBER(7,2)
          EMP.DEPTNO NUMBER(2,0)
          
          8 rows selected.
          
          SQL>   
          SY.
          • 2. Re: SQL - How to check data type definition
            Ganesh Srivatsav
            Try this.
            SELECT   COLUMN_NAME, TABLE_NAME, DATA_TYPE, DATA_LENGTH
              FROM   ALL_TAB_COLUMNS
             WHERE   DATA_TYPE = 'VARCHAR2'
                     AND DATA_LENGTH = 5;
            G.