Oracle9i ANYDATA Types

Overview

With the introduction of Oracle9i, developers can now take advantage of three new special SQL datatypes that enable applications to store generic attributes - attributes for which you do not know what the datatypes are until you actually execute the code. Here is a brief description of these new SQL Datatypes:

SYS.ANYTYPE

A type description type. A SYS.ANYTYPE can contain a type description of any SQL type, named or unnamed, including object types and collection types. An ANYTYPE can contain a type description of a persistent type, but an ANYTYPE itself is transient: in other words, the value in an ANYTYPE itself is not automatically stored in the database. To create a persistent type, use a CREATE TYPE statement from SQL.

SYS.ANYDATA

A self-describing data instance type. A SYS.ANYDATA contains an instance of a given type, with data, plus a description of the type. In this sense, a SYS.ANYDATA is self-describing. An ANYDATA can be persistently stored in the database.

SYS.ANYDATASET

A self-describing data set type. A SYS.ANYDATASET type contains a description of a given type plus a set of data instances of that type. An ANYDATASET can be persistently stored in the database.

In the past, developers would have simply stored their generic data in a VARCHAR2 - dates, numbers, everything. Now, with the introduction of ANYDATA, developers can put in a date and it stays a date. The system will enforce that it is, in fact, a valid date and allow you to perform date operations on it.

Using the previous scenario where the developer decides to store the same date in the VARCHAR2, there is no way to stop another developer from storing the string "INDIA" in your "date" field.