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?
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.
case record.col_type when DBMS_TYPES.TYPECODE_VARCHAR then 'VARCHAR2_TYPE' when DBMS_TYPES.NUMBER then 'NUMBER_TYPE' and so on.
TIMESTAMP and 183 for INTERVAL_DS. It looks like some types have multiple ids. Found it after doing some tests. Not able to find any doc about it. Oracle version is 12.2.sys.col$ gives us another set of ids that we may union with the ones in DBMS_TYPE to get more types. select count(*) from col$; = 115462. select count(distinct type#) nvals from col$; = 24. select listagg(col_type, ', ') within group (order by col_type) col_types from (select distinct type# col_type from col$);= 1, 2, 8, 12, 23, 24, 25, 29, 58, 69, 96, 100, 101, 111, 112, 113, 121, 122, 123, 180, 181, 183, 208, 231.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.
select regexp_substr(text, '^.*PLS_INTEGER :=\s*(\d+);.*$', 1, 1, NULL, 1) AS type_code, regexp_substr(text, '^.*TYPECODE_([^ ]+).*$', 1, 1, NULL, 1) AS type_name from all_source where owner='SYS' and name='DBMS_TYPES' and type='PACKAGE' and text like '%TYPECODE_%:=%;%' order by to_number(type_code)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.
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#;
