Oracle Type Code Mappings

Use case: Imagine that you need to represent type codes for the Oracle data types across PL/SQL, Java and C.

Challenge: Oracle uses different type codes in different contexts. This is fair enough if they need to adhere to standards (eg JDBC), but there’s really no excuse for using different type codes in the documentation, in PL/SQL, in DBMS_TYPES and Oracle Call Interface (OCI). In my view it’s a bit of a mess.

Solution: I’ve spent quite some time mapping between these slightly different type codes and also chosen a “uniform type code” across these. My rule of thumb has been that I use the type code from the Oracle Database 11g Release 2 documentation (Data Types). If the documentation doesn’t provide one, I use the one returned by one of the DBMS_SQL.DESCRIBE_COLUMNS% procedures as this is probably what most Oracle developers have used over time.

I’ve listed the mappings in the table below. If a cell is empty it’s because it’s missing or not supported for that particular type system (eg DUMP doesn’t work with all data types). I’ve underlined where the type codes differ from the Oracle documentation or between DBMS_SQL and the other type systems.

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

¹ Probably because in Java a true Date cannot hold a time value.

² XMLTYPE, ANYDATA, ANYDATASET and ANYTYPE are not “normal object types”. They are all declared like “CREATE TYPE <OWNER>.<OBJECT_TYPE> AS OPAQUE VARYING (*) USING LIBRARY...“.

The type codes from PL/SQL, Java and C have been verified through creation of a table or SELECT statement using the various data types and description of these through:

• PL/SQL: DBMS_SQL.DESCRIBE_COLUMNS3 on a DBMS_SQL cursor representing the above mentioned table/SELECT.
• Java: java.sql.ResultSetMetaData obtained through the java.sql.ResultSet representing the above mentioned table/SELECT.
• C: OCIParamGet(…, OCI_HTYPE_STMT, …) + OCIAttrGet(…, OCI_ATTR_DATA_TYPE, …) on a statement handle representing the above mentioned table/SELECT.

The type codes have been verified on Oracle Database 11g Release 2 11.2.0.1.0 Personal Edition on Windows 7. When I have more time I’ll create an automated test suite in order to verify my findings on Oracle Database 10g Release 1, Release 2 and Oracle Database 11g Release 1 and also verify on other platforms like Linux and Solaris.

I welcome your feedback and suggestions for improvements and corrections.