Oracle Internals
JulianDyke.com

Welcome

Consultancy

Seminars

Blog

Presentations

Diagnostics

Internals

 

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 CodeDescription
1VARCHAR2 or NVARCHAR2
2NUMBER
8LONG
9NCHAR VARYING, VARCHAR
12DATE
23RAW
24LONG RAW
25LONG UB2
26LONG SB4
58ANYDATA
69ROWID
96CHAR or NCHAR
100BINARY FLOAT
101BINARY DOUBLE
102REF CURSOR
104UROWID
105MLSLABEL
106MLSLABEL
111XMLTYPE (TABLE or REF)
112CLOB or NCLOB
113BLOB
114BFILE
121TYPE (USER-DEFINED)
122TYPE (TABLE OF RECORD)
123TYPE (VARRAY)
178TIME
179TIME WITH TIME ZONE
180TIMESTAMP
181TIMESTAMP WITH TIME ZONE
182INTERVAL YEAR TO MONTH
183INTERVAL DAY TO SECOND
208UROWID
231TIMESTAMP 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 CodeName
1VARCHAR
2NUMBER
3INTEGER
4FLOAT
7DECIMAL
9VARCHAR2
12DATE
21REAL
22DOUBLE PRECISION
23UNSIGNED BINARY INTEGER(8)
25UNSIGNED BINARY INTEGER(16)
26UNSIGNED BINARY INTEGER(32)
27SIGNED BINARY INTEGER(8)
28SIGNED BINARY INTEGER(16)
29SIGNED BINARY INTEGER(32)
32POINTER
69BINARY ROWID
95RAW
96CHAR
100BINARY FLOAT
101BINARY DOUBLE
102PL/SQL REF CURSOR
104UROWID
108KOKED1
108KOTTBX
108td>KOTTB
108KOTMI
108KOTMD
108KOTADX
108KOTAD
108KOTTD
108KOKED
110REF
112CLOB
113BLOB
114BFILE
115CFILE
185TIME
186TIME WITH TZ
187TIMESTAMP
188TIMESTAMP WITH TZ
189INTERVAL YEAR TO MONTH
190INTERVAL DAY TO SECOND
228NAMED COLLECTION
228NAMED OBJECT
232TIMESTAMP WITH LOCAL TZ
245OCTET
246SMALLINT
247VARYING ARRAY
248TABLE
250PL/SQL RECORD
251PL/SQL COLLECTION
252PL/SQL BOOLEAN
256OID
257CONTIGUOUS ARRAY
258CANONICAL
259LOB POINTER
260PL/SQL POSITIVE
261PL/SQL POSITIVEN
262PL/SQL ROWID
263PL/SQL LONG
264PL/SQL LONG RAW
265PL/SQL BINARY INTEGER
266PL/SQL PLS INTEGER
267PL/SQL NATURAL
268PL/SQL NATURALN
269PL/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