First introduce 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 us to perform various functions with it.

Now big question, how and where could we use this?

Let imagine a situation where we need to design a table with a column which should store any type of data, and we know what kind of data only when the application is actually run. For each line of record there can be any type of data type.

In the past we normally do, is declare it VARCHAR2, While Inserting we convert each value to VARCHAR2. But the problem with this approach is that we don’t know what kind of data type is stored and even if we know we need to write conversion for it to convert it into original value.

Now ANYDATA type comes to rescue.

Such fields in Oracle 9i and above can be declared as SYS.ANYDATA. After that we can put whatever type we want and it will stay that type. Like if we put date, it will stays as date and we will be able to perform any date operation on it without any need for conversion.

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 we know how to create table with it and we know how to put in value.

Next question, How to get back value?

Bad news is that there is no straight forward method to do so!

Good news, ANYDATA type do provide many method which help us perform this and many others functions.

Example:
By using following methods exposed in ANYDATA type we can get the desire data in original data type:

gettypeName()
getNumber()
getDate()
getVarchar2()

…

DECLARE
l_flag NUMBER;
l_v VARCHAR2(100);
l_n NUMBER;
l_d DATE;
l_error VARCHAR2(40);
BEGIN
CASE anydata_type_variable.gettypeName
WHEN ‘SYS.NUMBER’ THEN
l_flag :=anydata_type_variable.getNumber(l_n);
WHEN ‘SYS.DATE’ THEN
l_flag :=anydata_type_variable.getDate(l_d);
WHEN ‘SYS.VARCHAR2′ THEN
l_flag :=anydata_type_variable.getVarchar2(l_v);
ELSE
l_error := ‘** unknown **’;
END CASE;
….
…

Reference