In Oracle database SYS.ANYDATA can be used as a generic datatype that can be used to represent any datatype as shown in the example below. A table can have a column with a datatype of SYS.ANYDATA and can be used to store dates, VARCHAR2, number, BLOB and CLOB.

  'SYS.VARCHAR2', anydata.AccessVarchar2(q_any.col),
  'SYS.DATE', TO_CHAR(anydata.AccessDate(q_any.col),'mm/dd/yyyy'),
  'SYS.NUMBER', anydata.AccessNumber(q_any.col),
  'SYS.CLOB', anydata.AccessCLOB(q_any.col),
  'SYS.BLOB', UTL_RAW.cast_to_varchar2(anydata.AccessBLOB(q_any.col))
 ) col_value,
 SYS.ANYDATA.getTypeName(q_any.col) AS type_name
(SELECT 1 idx, SYS.ANYDATA.convertNumber(10) as col FROM DUAL  UNION ALL
 SELECT 2 idx, SYS.ANYDATA.convertVarchar2('Hello Anydata') as col FROM DUAL  UNION ALL
 SELECT 3 idx, SYS.ANYDATA.convertDate('15-APR-2009') as col FROM DUAL    UNION ALL
 SELECT 4 idx, SYS.ANYDATA.convertCLOB('Anydata CLOB Data') as col FROM DUAL   UNION ALL
 SELECT 5 idx, SYS.ANYDATA.convertBLOB(UTL_RAW.cast_to_raw('Anydata BLOB Data')) as col FROM DUAL
) q_any;
Note: UNION ALL has to be used to avoid the ORA-22950 error (cannot ORDER objects without MAP or ORDER method)

Oracle SYS.ANYDATA example

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012