How to determine the string value of an Oracle datatype by its code?

Question

I use the DBMS_SQL.DESCRIBE_COLUMNS procedure to determine which data types are used for the columns in the result set for my SELECT query. But, unfortunately, I can only get Oracle data type code ( record.col_type ) - 1, 8, 12, etc.

So, I wonder how I can easily get the string equivalent of the returned data type code (i.e. VARCHAR2, LONG, DATE, etc.) instead of its numeric code?

Answer 1

This is a bit awkward solution, but you can use the CASE statement to “search” for data type descriptions.

 case record.col_type when dbms_types.TYPCODE_DATE then 'DATE' when dbms_types.TYPCODE_NUMBER then 'NUMBER' when dbms_types.TYPCODE_RAW then 'RAW' when dbms_types.TYPCODE_CHAR then 'CHAR' when dbms_types.TYPCODE_VARCHAR then 'VARCHAR2' when dbms_types.TYPCODE_VARCHAR then 'VARCHAR' when dbms_types.TYPCODE_MLSLABEL then 'MLSLABEL' when dbms_types.TYPCODE_BLOB then 'BLOB' when dbms_types.TYPCODE_BFILE then 'BFILE' when dbms_types.TYPCODE_CLOB then 'CLOB' when dbms_types.TYPCODE_CFILE then 'CFILE' when dbms_types.TYPCODE_TIMESTAMP then 'TIMESTAMP' when dbms_types.TYPCODE_TIMESTAMP_TZ then 'TIMESTAMP_TZ' when dbms_types.TYPCODE_TIMESTAMP_LTZ then 'TIMESTAMP_LTZ' when dbms_types.TYPCODE_INTERVAL_YM then 'INTERVAL_YM' when dbms_types.TYPCODE_INTERVAL_DS then 'INTERVAL_DS' when dbms_types.TYPCODE_REF then 'REF' when dbms_types.TYPCODE_OBJECT then 'OBJECT' when dbms_types.TYPCODE_VARRAY then 'VARRAY' when dbms_types.TYPCODE_TABLE then 'TABLE' when dbms_types.TYPCODE_NAMEDCOLLECTION then 'NAMEDCOLLECTION' when dbms_types.TYPCODE_OPAQUE then 'OPAQUE' when dbms_types.TYPCODE_NCHAR then 'NCHAR' when dbms_types.TYPCODE_NVARCHAR then 'NVARCHAR2' when dbms_types.TYPCODE_NCLOB then 'NCLOB' when dbms_types.TYPCODE_BFLOAT then 'BFLOAT' when dbms_types.TYPCODE_BDOUBLE then 'BDOUBLE' when dbms_types.TYPCODE_UROWID then 'UROWID' end case 

To get the values ​​for this, you must query the data dictionary as prompted by tbone .

Answer 2

Use ANYDATA.GetTypeName

Here is a link with some explanation and 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 your selection .

Answer 3

Look in the 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 custom type; you may need to use DESCRIBE_COLUMNS3 instead.

Answer 4

Try:

 select distinct c.type# type_code, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 100, 'BINARY_FLOAT', 101, 'BINARY_DOUBLE', 105, 'MLSLABEL', 106, 'MLSLABEL', 111, 'REF', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 121, 'USER_TYPE', 122, 'USER_TYPE', 123, 'USER_TYPE', 178, 'TIME(' ||c.scale|| ')', 179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.scale|| ')', 181, 'TIMESTAMP(' ||c.scale|| ')'||' WITH TIME ZONE', 231, 'TIMESTAMP(' ||c.scale|| ')'||' WITH LOCAL TIME ZONE', 182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH', 183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' || c.scale || ')', 208, 'UROWID', 'UNDEFINED') type_name from sys.col$ c order by c.type#;