How can I determine string value of Oracle's datatype by its code?

Question

I use DBMS_SQL.DESCRIBE_COLUMNS procedure to identify what datatypes are used for columns in result set for my SELECT query. But unfortunately in this way I can get only a code of Oracle's datatype (record.col_type) - 1, 8, 12 etc.

So I wonder, how can I easely get a string equivalent of returned datatype's code (i.e. VARCHAR2, LONG, DATE etc.) instead of its numeric code?

Answer 1

Look in DBMS_TYPES package (from my 11gr2):

select text from all_source
where owner='SYS'
and name='DBMS_TYPES'
and type='PACKAGE';

PACKAGE dbms_types AS

  TYPECODE_DATE            PLS_INTEGER :=  12;

  TYPECODE_NUMBER          PLS_INTEGER :=   2;

  TYPECODE_RAW             PLS_INTEGER :=  95;

  TYPECODE_CHAR            PLS_INTEGER :=  96;

  TYPECODE_VARCHAR2        PLS_INTEGER :=   9;

  TYPECODE_VARCHAR         PLS_INTEGER :=   1;

  TYPECODE_MLSLABEL        PLS_INTEGER := 105;

  TYPECODE_BLOB            PLS_INTEGER := 113;

  TYPECODE_BFILE           PLS_INTEGER := 114;

  TYPECODE_CLOB            PLS_INTEGER := 112;

  TYPECODE_CFILE           PLS_INTEGER := 115;

  TYPECODE_TIMESTAMP       PLS_INTEGER := 187;

  TYPECODE_TIMESTAMP_TZ    PLS_INTEGER := 188;

  TYPECODE_TIMESTAMP_LTZ   PLS_INTEGER := 232;

  TYPECODE_INTERVAL_YM     PLS_INTEGER := 189;

  TYPECODE_INTERVAL_DS     PLS_INTEGER := 190;



  TYPECODE_REF             PLS_INTEGER := 110;

  TYPECODE_OBJECT          PLS_INTEGER := 108;

  TYPECODE_VARRAY          PLS_INTEGER := 247;            /* COLLECTION TYPE */

  TYPECODE_TABLE           PLS_INTEGER := 248;            /* COLLECTION TYPE */

  TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122;

  TYPECODE_OPAQUE          PLS_INTEGER := 58;                 /* OPAQUE TYPE */



  /* NOTE: These typecodes are for use in AnyData api only and are short forms

     for the corresponding char typecodes with a charset form of SQLCS_NCHAR.

  */

  TYPECODE_NCHAR           PLS_INTEGER := 286;

  TYPECODE_NVARCHAR2       PLS_INTEGER := 287;

  TYPECODE_NCLOB           PLS_INTEGER := 288;



  /* Typecodes for Binary Float, Binary Double and Urowid. */

  TYPECODE_BFLOAT          PLS_INTEGER := 100;

  TYPECODE_BDOUBLE         PLS_INTEGER := 101;

  TYPECODE_UROWID          PLS_INTEGER := 104;



  SUCCESS                  PLS_INTEGER := 0;

  NO_DATA                  PLS_INTEGER := 100;

Also note that 109 is a user-defined type, you may need to use DESCRIBE_COLUMNS3 instead.

Answer 2

This is a slightly clunky solution, but you can use a CASE statement to "lookup" the datatype descriptions.

case record.col_type
  when dbms_types.TYPECODE_DATE then 'DATE'             
  when dbms_types.TYPECODE_NUMBER then 'NUMBER'           
  when dbms_types.TYPECODE_RAW then 'RAW'              
  when dbms_types.TYPECODE_CHAR then 'CHAR'             
  when dbms_types.TYPECODE_VARCHAR2 then 'VARCHAR2'         
  when dbms_types.TYPECODE_VARCHAR then 'VARCHAR'          
  when dbms_types.TYPECODE_MLSLABEL then 'MLSLABEL'         
  when dbms_types.TYPECODE_BLOB then 'BLOB'             
  when dbms_types.TYPECODE_BFILE then 'BFILE'            
  when dbms_types.TYPECODE_CLOB then 'CLOB'              
  when dbms_types.TYPECODE_CFILE then 'CFILE'            
  when dbms_types.TYPECODE_TIMESTAMP then 'TIMESTAMP'        
  when dbms_types.TYPECODE_TIMESTAMP_TZ then 'TIMESTAMP_TZ'     
  when dbms_types.TYPECODE_TIMESTAMP_LTZ then 'TIMESTAMP_LTZ'    
  when dbms_types.TYPECODE_INTERVAL_YM then 'INTERVAL_YM'      
  when dbms_types.TYPECODE_INTERVAL_DS then 'INTERVAL_DS'      
  when dbms_types.TYPECODE_REF then 'REF'              
  when dbms_types.TYPECODE_OBJECT then 'OBJECT'           
  when dbms_types.TYPECODE_VARRAY then 'VARRAY'                       
  when dbms_types.TYPECODE_TABLE then 'TABLE'                        
  when dbms_types.TYPECODE_NAMEDCOLLECTION then 'NAMEDCOLLECTION'  
  when dbms_types.TYPECODE_OPAQUE then 'OPAQUE'                            
  when dbms_types.TYPECODE_NCHAR then 'NCHAR'            
  when dbms_types.TYPECODE_NVARCHAR2 then 'NVARCHAR2'       
  when dbms_types.TYPECODE_NCLOB then 'NCLOB'                  
  when dbms_types.TYPECODE_BFLOAT then 'BFLOAT'           
  when dbms_types.TYPECODE_BDOUBLE then 'BDOUBLE'          
  when dbms_types.TYPECODE_UROWID then 'UROWID'              
end case

To get the values for this you should query the data dictionary as tbone suggests.