ANYDATA Type in Oracle

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 "Linux" in your "date" field.

---

A Simple Example

The following is a simple example of how to get started in using the SYS.ANYDATA type:

  create table test_any(id number, value sys.anydata);

  SQL> desc test_any 
  Name              Null?    Type 
  ----------------- ----- ---------------------------- 
  ID                      NUMBER 
  VALUE                   ANYDATA

  INSERT INTO test_any VALUES(1, sys.anydata.convertNumber(5));
  INSERT INTO test_any VALUES(2, sys.anydata.convertVarchar2('Oracle / Linux Application'));
  INSERT INTO test_any VALUES(3, sys.anydata.convertDate('14-FEB-1994'));

There are many SYS.ANYDATA type has many methods, one of which is getTypeName. This method provides the ability to see what kind of data is actually being stored in the SYS.ANYDATA type:

  SELECT  
      id Id
    , t.value.getTypeName() TypeName
  FROM test_any t;

     ID  TYPENAME
  -----  ------------
      2  SYS.NUMBER
      3  SYS.VARCHAR2
      4  SYS.DATE

Using SQL*Plus to display the contents of ANYDATA in a query is not so easy. The following PL/SQL function (getData) rectifies this problem and also gives you an insight into several more methods provided with the SYS.ANYDATA type.

  CREATE OR REPLACE FUNCTION getData(data IN SYS.ANYDATA)
    RETURN VARCHAR2
    AS
      l_varchar2   VARCHAR2(4000);
      l_rc         NUMBER;
    BEGIN

      CASE data.getTypeName
        when 'SYS.NUMBER' then
          l_rc := data.getNumber(l_varchar2);
        when 'SYS.DATE' then
          l_rc := data.getDate(l_varchar2);
        when 'SYS.VARCHAR2' then
          l_rc := data.getVarchar2(l_varchar2);
        else
          l_varchar2 := '** unknown **';
      END CASE;

      RETURN l_varchar2;

    END;
  /

---

  SELECT id, getData(value) GETDATA FROM   test_any;

       ID  GETDATA
  -------  -----------------------------------------------------
        2  5
        3  Testing Application
        4  14-FEB-94