First introduced in Oracle 9i, ANYDATA is a “self-describing data instance type” which means it not only holds the value, but it also holds its own data type within each instance of it. An ANYDATA can be persistently stored in the database. It also contains many methods that allow you to perform various functions.

So how and where do you use it? Suppose you need to design a table with a column which should store any type of data, and you’ll only know what kind of data when the application is actually run. In each row, that column can hold any type of data.

In the past, what you would normally do is declare it as VARCHAR2. While inserting, you’d convert each value to VARCHAR2. The problem with this approach is that you wouldn’t know what kind of data type is stored. Even if you did know, you’d need to write a conversion for it to convert it into the original value. This is where ANYDATA type comes to the rescue.

These kinds of fields in Oracle 9i can be declared as SYS.ANYDATA. After that, you can use whatever type you want and it will stay that type. For example, if you use date, it stays date and you will be able to perform any date operation on it without any need for conversion.

Here’s some examples:

SQL> CREATE TABLE tab1( col1 SYS.ANYDATA );Table created.SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertVarchar2(Rajender Singh));SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertDate(SYSDATE));SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertNumber(1972));

Now you know how to create a table using ANYDATA you know how to put in values.

The next question is how to get back a value. The bad news is that there is no straight-forward method to do so. The good news is that the ANYDATA type provides a bunch of methods to help you perform this function (among others).

For example, by using the following methods, each exposed in the ANYDATA type, you can get the data you desire in its original data type:

gettypeName()getNumber()getDate()getVarchar2()DECLAREl_flag NUMBER;l_v VARCHAR2(100);l_n NUMBER;l_d DATE;l_error VARCHAR2(40);BEGINCASE anydata_type_variable.gettypeNameWHEN SYS.NUMBER THENl_flag :=anydata_type_variable.getNumber(l_n);WHEN SYS.DATE THENl_flag :=anydata_type_variable.getDate(l_d);WHEN SYS.VARCHAR2? THENl_flag :=anydata_type_variable.getVarchar2(l_v);ELSEl_error := ** unknown **;END CASE;.