ANYDATA in Oracle Database

SYS.ANYDATE (Generic Data Type)  is an object type that  used to store different data types in Table, or create variable that can setted by any data-type.

For example I will create new table below

CREATE TABLE ANYDATA_TABLE
(
   ID           NUMBER NOT NULL,
   ADT_COLUMN   SYS.ANYDATA
);

Now I will try to insert different data types in col2

insert into anydata_table values(1,sys.anydata.convertnumber(1))
/
insert into anydata_table values(2,sys.anydata.convertdate(sysdate))
/
insert into anydata_table values(3,sys.anydata.convertvarchar2('Mahmoud Ahmed El-Sayed'))
/

Note that I use anydata.convertnumber, anydata.convertdate and anydata.convertvarchar2 to specify data type of inserted data

let's now query data from table

SELECT * FROM ANYDATA_TABLE;

The data of ADT_COULMN is unreadable in result set as below picture

So I need to retrieve readable value of  ADT_COLUMN from query
We can do this by below steps
1-Determine data type of inserted data in ADT_COLUMN
2-Create different functions to return equivalent data type for every inserted data in ADT_COLUMN
    I will create these functions in package called ANYDATA_PKG
3-Use ANYDATA built-in member functions to do equivalent of ANYDATA_PKG in #2

1-Determine data type of inserted data in ADT_COLUMN

we can use SYS.ANYDATA.GETTYPENAME(ANYDATA_TABLE.ADT_COLUMN) to return data type of inserted data

SELECT ANYDATA_TABLE.*, SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) TYPE_NAME

  FROM ANYDATA_TABLE;

2-Create different functions to return equivalent data type for every inserted data in ADT_COLUMN

I will create ANYDATA_PKG which contains three functions
a-Get_number to return number value type
b-Get_varchar2 to return varchar2 type
c-Get_Date to return date type

 CREATE OR REPLACE PACKAGE ANYDATA_PKG  
 AS  
   GN$TMP  NUMBER;  
   FUNCTION GET_NUMBER (IN_ANYDATA IN SYS.ANYDATA)  
    RETURN NUMBER;  

   FUNCTION GET_VARCHAR2 (IN_ANYDATA IN SYS.ANYDATA)  
    RETURN VARCHAR2; 
 
   FUNCTION GET_DATE (IN_ANYDATA IN SYS.ANYDATA)  
    RETURN DATE;  
 END;  


 CREATE OR REPLACE PACKAGE BODY ANYDATA_PKG  
 AS  
   FUNCTION GET_NUMBER (IN_ANYDATA IN SYS.ANYDATA)  
    RETURN NUMBER  
   IS  
    LN$NUMBER_VALUE  NUMBER;  
   BEGIN  
    GN$TMP := IN_ANYDATA.GETNUMBER (LN$NUMBER_VALUE);  
    RETURN (LN$NUMBER_VALUE);  
   END;  

   FUNCTION GET_VARCHAR2 (IN_ANYDATA IN SYS.ANYDATA)  
    RETURN VARCHAR2  
   IS  
    LC$VARCHAR2_VALUE  VARCHAR2 (4000);  
   BEGIN  
    GN$TMP := IN_ANYDATA.GETVARCHAR2 (LC$VARCHAR2_VALUE);  
    RETURN (LC$VARCHAR2_VALUE);  
   END;  

   FUNCTION GET_DATE (IN_ANYDATA IN SYS.ANYDATA)  
    RETURN DATE  
   IS  
    LD$DATE_VALUE  DATE;  
   BEGIN  
    GN$TMP := IN_ANYDATA.GETDATE (LD$DATE_VALUE);  
    RETURN (LD$DATE_VALUE);  
   END;  

 END;  

Let's now write query again to retrieve data and add our new functions to get data in readable fashion.

SELECT ANYDATA_TABLE.*,
       SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) TYPE_NAME,
       CASE
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.NUMBER'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_NUMBER (ADT_COLUMN))
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.VARCHAR2'
          THEN
             ANYDATA_PKG.GET_VARCHAR2 (ADT_COLUMN)
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.DATE'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_DATE (ADT_COLUMN), 'DD-MM-RRRR')
       END
          READABLE_VALUE
  FROM ANYDATA_TABLE;

3-Use ANYDATA built-in members functions to do equivalent of ANYDATA_PKG in #2

 ANYDATA have built-in memebrs functions that can do what we do in ANYDATA_PKG
a- ANYDATA.ACESSNUMBER to return number value mapped to ANYDATA_PKG.GET_NUMBER
b-  ANYDATA.ACESSVARCHAR2 to return varchar2 value mapped to  ANYDATA_PKG.GET_VARCHAR2
c-  ANYDATA.ACESSDATE to return Date value mapped to ANYDATA_PKG.GET_DATE

Let's now use previous functions in our query
SELECT ANYDATA_TABLE.*,
       SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) TYPE_NAME,
       CASE
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.NUMBER'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_NUMBER (ADT_COLUMN))
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.VARCHAR2'
          THEN
             ANYDATA_PKG.GET_VARCHAR2 (ADT_COLUMN)
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.DATE'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_DATE (ADT_COLUMN), 'DD-MM-RRRR')
       END
          READABLE_VALUE,
       CASE
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.NUMBER'
          THEN
             TO_CHAR (SYS.ANYDATA.ACCESSNUMBER (ADT_COLUMN))
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.VARCHAR2'
          THEN
             SYS.ANYDATA.ACCESSVARCHAR2 (ADT_COLUMN)
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.DATE'
          THEN
             TO_CHAR (SYS.ANYDATA.ACCESSDATE (ADT_COLUMN), 'DD-MM-RRRR')
       END
          READABLE_VALUE2
  FROM ANYDATA_TABLE;

Conclusion

I illustrated how to use ANYDATA with scalar data types.
You can do your best practice to use ANYDATA with collection and object types.
If you take decision to do this use sys.anydata.convertcollection, sys.anydata.convertobject to insert collection and object types, then create your custom function to get inserted collection or object type in readable fashion.

Thanks