Internal Data Types

Every Oracle column has an internal data type represented by an integer value. The internal data type is used to represent the column type in the data dictionary (e.g. sys.col$). It is also used within memory structures such as the execution plan structures in heap 6.

The following table shows the known internal data types in Oracle 10.2:

Numeric Code	Description
1	VARCHAR2 or NVARCHAR2
2	NUMBER
8	LONG
9	NCHAR VARYING, VARCHAR
12	DATE
23	RAW
24	LONG RAW
25	LONG UB2
26	LONG SB4
58	ANYDATA
69	ROWID
96	CHAR or NCHAR
100	BINARY FLOAT
101	BINARY DOUBLE
102	REF CURSOR
104	UROWID
105	MLSLABEL
106	MLSLABEL
111	XMLTYPE (TABLE or REF)
112	CLOB or NCLOB
113	BLOB
114	BFILE
121	TYPE (USER-DEFINED)
122	TYPE (TABLE OF RECORD)
123	TYPE (VARRAY)
178	TIME
179	TIME WITH TIME ZONE
180	TIMESTAMP
181	TIMESTAMP WITH TIME ZONE
182	INTERVAL YEAR TO MONTH
183	INTERVAL DAY TO SECOND
208	UROWID
231	TIMESTAMP WITH LOCAL TIME ZONE

In addition a separate list of internal date types is maintained in the SYS.TYPES$ table

These types can be listed using the following query:

  SELECT t.typecode,o.name
  FROM sys.type$ t, sys.obj$ o
  WHERE BITAND (t.properties, 16) = 16
  AND t.toid = o.oid$
  ORDER BY t.typecode

In Oracle 10.2, the above query returns the following 69 rows:

Type Code	Name
1	VARCHAR
2	NUMBER
3	INTEGER
4	FLOAT
7	DECIMAL
9	VARCHAR2
12	DATE
21	REAL
22	DOUBLE PRECISION
23	UNSIGNED BINARY INTEGER(8)
25	UNSIGNED BINARY INTEGER(16)
26	UNSIGNED BINARY INTEGER(32)
27	SIGNED BINARY INTEGER(8)
28	SIGNED BINARY INTEGER(16)
29	SIGNED BINARY INTEGER(32)
32	POINTER
69	BINARY ROWID
95	RAW
96	CHAR
100	BINARY FLOAT
101	BINARY DOUBLE
102	PL/SQL REF CURSOR
104	UROWID
108	KOKED1
108	KOTTBX
108td>	KOTTB
108	KOTMI
108	KOTMD
108	KOTADX
108	KOTAD
108	KOTTD
108	KOKED
110	REF
112	CLOB
113	BLOB
114	BFILE
115	CFILE
185	TIME
186	TIME WITH TZ
187	TIMESTAMP
188	TIMESTAMP WITH TZ
189	INTERVAL YEAR TO MONTH
190	INTERVAL DAY TO SECOND
228	NAMED COLLECTION
228	NAMED OBJECT
232	TIMESTAMP WITH LOCAL TZ
245	OCTET
246	SMALLINT
247	VARYING ARRAY
248	TABLE
250	PL/SQL RECORD
251	PL/SQL COLLECTION
252	PL/SQL BOOLEAN
256	OID
257	CONTIGUOUS ARRAY
258	CANONICAL
259	LOB POINTER
260	PL/SQL POSITIVE
261	PL/SQL POSITIVEN
262	PL/SQL ROWID
263	PL/SQL LONG
264	PL/SQL LONG RAW
265	PL/SQL BINARY INTEGER
266	PL/SQL PLS INTEGER
267	PL/SQL NATURAL
268	PL/SQL NATURALN
269	PL/SQL STRING

Note that the list of types in TYPE$ is not compatible with the list of internal data types for a couple of reasons:

Internal data types are normally stored in a single byte (8 bits) which limits the range of values to 0..255
Some of the values in the two lists conflict e.g. VARCHAR and VARCHAR2