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

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.

Look in DBMS_TYPES package (from my 11gr2):

select text from all_sourcewhere 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.

Use ANYDATA.GetTypeName

Here's a link with some explanation and an example: http://www.oracle-developer.net/display.php?id=218

And I quote:

SQL> DECLARE  2     v_anydata  ANYDATA := ANYDATA.ConvertVarchar2('String');  3     v_typename VARCHAR2(128);  4  BEGIN  5     v_typename := v_anydata.GetTypeName();  6     DBMS_OUTPUT.PUT_LINE('Type of ANYDATA instance is [' || v_typename || ']');  7  END;  8  /Type of ANYDATA instance is [SYS.VARCHAR2]PL/SQL procedure successfully completed.

Here are some examples of using this in a select.