oracle oracle11g sqldatatypes user-defined-data-types

Oracle How to Map Data Types to CODE/IDs - Dump and dbms_sql.define_column produce data type codes

When using DUMP() or when using dbms_sql.define_column both return the CODE of the Oracle Data type.

Is there a MAP that ties the Oracle Data Types to their Oracle CODEs that are returned by the DUMP() function or the dbms_sql.define_column feature?

I'm looking for this to aid in testing as well as to locate custom data types such as a NESTED TABLE. A nested table exists in one of my tables and produces a Data Type Code of 109. How do I lookup the type that is associated with Type Code of 109? Examples below of dump and dbms_sql.define_column.

I must assume this is available within the database to query due to the fact that when I write a query I define the columns using the 'TEXT' name of the column type and NOT the Code. So where is the text aka name of my data types stored whether they are 'built-in' or 'custom'? It's got to be somewhere.

DUMP

SELECT 
    DUMP(to_date('15-JAN-18'),10,1,1) AS date_type
  , DUMP(123,10,1,1)   AS num_type
  , DUMP('abc',10,1,1) AS var_type
FROM dual
;

Dump Output

/* OUTPUT: 
 |
 | "DATE_TYPE"          "NUM_TYPE"          "VAR_TYPE"
 |--------------------|-------------------|------------------
 | "Typ=13 Len=8: 226"  "Typ=2 Len=3: 194"  "Typ=96 Len=3: 97"
*/

DBMS_SQL.DEFINE_COLUMN

... some code above ...

-- Define columns:
FOR i IN 1..n_colcnt
LOOP
    dbms_output.put_line(v_desctab(i).col_name ||': ' || v_desctab(i).col_type);

    IF v_desctab(i).col_type = 2
    THEN dbms_sql.DEFINE_COLUMN(n_curid, i, v_num_var);
    ELSIF v_desctab(i).col_type = 12 THEN
        dbms_sql.define_column(n_curid, i, v_date_var);
    ELSE
        dbms_sql.define_column(n_curid, i, v_name_var, 50);
    END IF;
END LOOP;

... some code below ...

DBMS_SQL.DEFINE_COLUMN Output - Outputs column names and their data type CODEs. I want Text instead.

QUERY_ID: 2
OTHER_ID: 2
ACTIVE: 1
QUERY_NAME: 1
QUERY_DESC: 1
QUERY_DEF: 112
QUERY_TYPE: 2
CREATED: 12
UPDATED: 12
CREATED_BY: 2
UPDATED_BY: 2

One thing I have regarding a mapping of Oracle Data Types to their corresponding IDs is an excel file I continue to reference. I am hoping there is something I can query within Oracle to give me what I'm looking for rather than referencing an external document.

There is THIS link however this doesn't assist me when I'm looking for Custom Types and it missing documentation for CODE 13.

Here is another very nice Data Type Map somebody put together: Oracle Type Code Mappings

Solution

Using the data obtained from this link (Oracle Type Code Mappings) I decided to create my own table and populate it with the Oracle Data Types mapping the Oracle Type CODEs back to their 'TEXT' of the datatype.

Maybe somebody will come along and show a way to just obtain the NAME of the TYPE based on the code without this custom work around. For now this is going to have to be my accepted answer as it gets me as close as possible to what I needed in the first place. The other answers certainly help me to get here. Unfortunately it still isn't able to pull back the actual NAME of Custom Types that come back with a CODE of 109.

CREATE TABLE oracle_data_types
(
  Data_Type                 VARCHAR2(100 CHAR),
  Uniform_Type_Code         NUMBER(10),
  Oracle_Doc                NUMBER(10),
  DBMS_SQL                  NUMBER(10),
  DBMS_TYPES_TYPECODE_PERC  VARCHAR2(100 CHAR),
  JDBC_java_sql_Types       VARCHAR2(100 CHAR),
  OCI                       VARCHAR2(100 CHAR),
  DUMP                      NUMBER(10),
  V$_SQL_BIND_DATA_DATATYPE  NUMBER(10)
)
;

/

INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('VARCHAR2',1,1,1,'9 VARCHAR2 1 VARCHAR','12 VARCHAR','1 SQLT_CHR',1,1);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('NVARCHAR2',1,1,1,'287 NVARCHAR2','12 VARCHAR','1 SQLT_CHR',1,1);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('NUMBER',2,2,2,'NUMBER','2 NUMERIC','2 SQLT_NUM',2,2);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('FLOAT',2,2,2,'2 NUMBER','2 NUMERIC','2 SQLT_NUM',2,2);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('LONG',8,8,8,'','-1 LONGVARCHAR','8 SQLT_LNG',NULL,8);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('DATE',12,12,12,'12 DATE','93 TIMESTAMP1','12 SQLT_DAT',12,12);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('BINARY_FLOAT',21,21,100,'100 BFLOAT','100','100 SQLT_IBFLOAT',100,100);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('BINARY_DOUBLE',22,22,101,'101 BDOUBLE','101','101 SQLT_IBFLOAT',101,101);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('TIMESTAMP',180,180,180,'187 TIMESTAMP','93 TIMESTAMP1','187 SQLT_TIMESTAMP',180,180);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('TIMESTAMP WITH TIME ZONE',181,181,181,'188 TIMESTAMP_TZ','-101','188 SQLT_TIMESTAMP_TZ',181,181);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('TIMESTAMP WITH LOCAL TIME ZONE',231,231,231,'232 TIMESTAMP_LTZ','-102','232 SQLT_TIMESTAMP_LTZ',231,231);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('INTERVAL YEAR TO MONTH',182,182,182,'189 INTERVAL_YM','-103','189 SQLT_INTERVAL_YM',182,182);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('INTERVAL DAY TO SECOND',183,183,183,'190 INTERVAL_DS','-104','190 SQLT_INTERVAL_DS',183,183);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('RAW',23,23,23,'95 RAW','-3 VARBINARY','23 SQLT_BIN',23,23);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('LONG RAW',24,24,24,'','-4 LONGVARBINARY','24 SQLT_LBI',NULL,24);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('ROWID',69,69,11,'','-8','104 SQLT_RDD',69,69);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('UROWID',208,208,208,'104 UROWID','-8','104 SQLT_RDD',208,208);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('CHAR',96,96,96,'96 CHAR','1 CHAR','96 SQLT_AFC',96,96);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('NCHAR',96,96,96,'286 NCHAR','1 CHAR','96 SQLT_AFC',96,96);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('CLOB',112,112,112,'112 CLOB','2005 CLOB','112 SQLT_CLOB',NULL,112);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('NCLOB',112,112,112,'288 NCLOB','2005 CLOB','112 SQLT_CLOB',NULL,112);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('BLOB',113,113,113,'113 BLOB','2004 BLOB','113 SQLT_BLOB',NULL,113);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('BFILE',114,114,114,'114 BFILE','-13','114 SQLT_BFILEE',NULL,114);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('XMLTYPE',109,NULL,109,'58 OPAQUE2','2007','108 SQLT_NTY',58,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('ANYDATA',109,NULL,109,'58 OPAQUE2','2007','108 SQLT_NTY',58,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('ANYDATASET',109,NULL,109,'58 OPAQUE2','2007','108 SQLT_NTY',58,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('ANYTYPE',109,NULL,109,'58 OPAQUE2','2007','108 SQLT_NTY',58,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('Object type',109,NULL,109,'108 OBJECT','2002 STRUCT','108 SQLT_NTY',121,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('VARRAY',109,NULL,109,'247 VARRAY','2003 ARRAY','108 SQLT_NTY',NULL,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('Nested table',109,NULL,109,'248 TABLE','2003 ARRAY','108 SQLT_NTY',NULL,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('REF',111,NULL,111,'110 REF','2006 REF','110 SQLT_REF',111,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('Strong REF CURSOR',102,NULL,102,'','-10','116 SQLT_RSET',102,NULL);
INSERT INTO oracle_data_types (Data_Type, Uniform_Type_Code, Oracle_Doc, DBMS_SQL, DBMS_TYPES_TYPECODE_PERC, JDBC_java_sql_Types, OCI, DUMP, V$_SQL_BIND_DATA_DATATYPE) VALUES ('Weak REF CURSOR',102,NULL,102,'','-10','116 SQLT_RSET',102,NULL);

Screenshot of Data for a visual reference

Example of using the sys.DBMS_TYPES package

To select the PL/SQL code directly from the package:

SELECT TEXT 
FROM dba_source 
WHERE name = 'DBMS_TYPES' 
   AND regexp_like(text, 'TYPECODE')
;

Using PL/SQL to pull the Constants out:

BEGIN
  dbms_output.put_line(dbms_types.TYPECODE_CHAR            ||' TYPECODE_DATE');
  dbms_output.put_line(dbms_types.TYPECODE_NUMBER          ||' TYPECODE_NUMBER');
  dbms_output.put_line(dbms_types.TYPECODE_VARCHAR2        ||' TYPECODE_RAW');
  dbms_output.put_line(dbms_types.TYPECODE_VARCHAR         ||' TYPECODE_CHAR');
  dbms_output.put_line(dbms_types.TYPECODE_RAW             ||' TYPECODE_VARCHAR2');
  dbms_output.put_line(dbms_types.TYPECODE_MLSLABEL        ||' TYPECODE_VARCHAR');
  dbms_output.put_line(dbms_types.TYPECODE_BLOB            ||' TYPECODE_MLSLABEL');
  dbms_output.put_line(dbms_types.TYPECODE_BFILE           ||' TYPECODE_BLOB');
  dbms_output.put_line(dbms_types.TYPECODE_CLOB            ||' TYPECODE_BFILE');
  dbms_output.put_line(dbms_types.TYPECODE_CFILE           ||' TYPECODE_CLOB');
  dbms_output.put_line(dbms_types.TYPECODE_TIMESTAMP       ||' TYPECODE_CFILE');
  dbms_output.put_line(dbms_types.TYPECODE_TIMESTAMP_TZ    ||' TYPECODE_TIMESTAMP');
  dbms_output.put_line(dbms_types.TYPECODE_TIMESTAMP_LTZ   ||' TYPECODE_TIMESTAMP_TZ');
  dbms_output.put_line(dbms_types.TYPECODE_INTERVAL_YM     ||' TYPECODE_TIMESTAMP_LTZ');
  dbms_output.put_line(dbms_types.TYPECODE_INTERVAL_DS     ||' TYPECODE_INTERVAL_YM');
  dbms_output.put_line(dbms_types.TYPECODE_REF             ||' TYPECODE_INTERVAL_DS');
  dbms_output.put_line(dbms_types.TYPECODE_OBJECT          ||' TYPECODE_REF');
  dbms_output.put_line(dbms_types.TYPECODE_VARRAY          ||' TYPECODE_OBJECT');
  dbms_output.put_line(dbms_types.TYPECODE_TABLE           ||' TYPECODE_VARRAY');
  dbms_output.put_line(dbms_types.TYPECODE_NAMEDCOLLECTION ||' TYPECODE_TABLE');
  dbms_output.put_line(dbms_types.TYPECODE_DATE            ||' TYPECODE_NAMEDCOLLECTION');
  dbms_output.put_line(dbms_types.TYPECODE_OPAQUE          ||' TYPECODE_OPAQUE');
  dbms_output.put_line(dbms_types.TYPECODE_NCHAR           ||' TYPECODE_NCHAR');
  dbms_output.put_line(dbms_types.TYPECODE_NVARCHAR2       ||' TYPECODE_NVARCHAR2');
  dbms_output.put_line(dbms_types.TYPECODE_NCLOB           ||' TYPECODE_NCLOB');
  dbms_output.put_line(dbms_types.TYPECODE_BFLOAT          ||' TYPECODE_BFLOAT');
  dbms_output.put_line(dbms_types.TYPECODE_BDOUBLE         ||' TYPECODE_BDOUBLE');
  dbms_output.put_line(dbms_types.TYPECODE_UROWID          ||' TYPECODE_UROWID');
END;

Output

96 TYPECODE_DATE
2 TYPECODE_NUMBER
9 TYPECODE_RAW
1 TYPECODE_CHAR
95 TYPECODE_VARCHAR2
105 TYPECODE_VARCHAR
113 TYPECODE_MLSLABEL
114 TYPECODE_BLOB
112 TYPECODE_BFILE
115 TYPECODE_CLOB
187 TYPECODE_CFILE
188 TYPECODE_TIMESTAMP
232 TYPECODE_TIMESTAMP_TZ
189 TYPECODE_TIMESTAMP_LTZ
190 TYPECODE_INTERVAL_YM
110 TYPECODE_INTERVAL_DS
108 TYPECODE_REF
247 TYPECODE_OBJECT
248 TYPECODE_VARRAY
122 TYPECODE_TABLE
12 TYPECODE_NAMEDCOLLECTION
58 TYPECODE_OPAQUE
286 TYPECODE_NCHAR
287 TYPECODE_NVARCHAR2
288 TYPECODE_NCLOB
100 TYPECODE_BFLOAT
101 TYPECODE_BDOUBLE
104 TYPECODE_UROWID

Using the DECODE function code pulled directly out of the view for ALL_TAB_COLUMNS

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#
;