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

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.

Advertisements

About ellebaek
Sun Certified Programmer for Java 5 (SCJP). Oracle Advanced PL/SQL Developer Certified Professional (OCP Advanced PL/SQL). ISTQB Certified Tester Foundation Level (ISTQB CTFL).

4 Responses to Oracle Type Code Mappings

  1. Pingback: Describing a REF CURSOR in Oracle 10g+ Using PL/SQL, Java and C « Finn Ellebaek Nielsen's Blog

  2. bitbach says:

    hi finn,
    this is great stuff, exactly what i needed this time to set up an anytype from a DBMS_SQL.DESCRIBE_COLUMNS2 call. this also cleared my self-confidence from doubts that i can no longer read the screen because of the mad value returned from DBMS_SQL.DESCRIBE_COLUMNS2 for rowid columns (11 instead of 69). your article just made shure that i call still read it correctly :]]
    best regards peter

  3. jgebal says:

    Awesome,
    Exactly what I needed for my open source project.
    Making use of ANYDATA and any cursors i was about to choke on the type_code mismatch issue.
    Your article and work is a huge time saver.
    Thanks a milion – great work!

    Regards
    Jacek

    P.S.
    My project can be seen at:
    https://github.com/jgebal/any_data/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: