Home

repost: 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 TIMESTAMP 1 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 TIMESTAMP 1 187 SQLT_TIMESTAMP 180 180
TIMESTAMPWITH TIME ZONE 181 181 181 188 TIMESTAMP_TZ -101 188 SQLT_TIMESTAMP_TZ 181 181
TIMESTAMPWITH LOCALTIME ZONE 231 231 231 232 TIMESTAMP_LTZ -102 232 SQLT_TIMESTAMP_LTZ 231 231
INTERVAL YEARTO MONTH 182 182 182 189 INTERVAL_YM -103 189 SQLT_INTERVAL_YM 182 182
INTERVAL DAYTO 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 OPAQUE 2 2007 108 SQLT_NTY 58
ANYDATA 109 109 58 OPAQUE 2 2007 108 SQLT_NTY 58
ANYDATASET 109 109 58 OPAQUE 2 2007 108 SQLT_NTY 58
ANYTYPE 109 109 58 OPAQUE 2 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 REFCURSOR 102 102 -10 116 SQLT_RSET 102
Weak REFCURSOR 102 102 -10 116 SQLT_RSET 102

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

2 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.

Comments

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--return type is VARCHAR
select dump(decode(1, 2, null, 2)) 
  from dual;  --Typ=1 Len=1: 50
  
--return type is NUMBER
select dump(decode(1, 2, to_number(null), 2)) 
  from dual;  --Typ=2 Len=2: 193,3

--ORA-01790: expression must have same datatype as corresponding expression
select decode(1, 2, null, 2) from dual
union
select 2 from dual;

--2
select decode(1, 2, to_number(null), 2) from dual
union
select 2 from dual;
Edited on