Mennan On Writing...

It is possible to create anydatas and anytypes in Oracle. Oracle has also some powerful object oriented features. With this OO features, you may not need to use anytypes.

A simple example shows how to use:

DECLARE
n NUMBER;
v VARCHAR2(16);
d DATE;
TYPE any_data_array_typ IS TABLE OF anydata;
any_data_array any_data_array_typ := any_data_array_typ();

temp_varchar2 VARCHAR2(16);
temp_number NUMBER;
temp_date DATE;
value_result PLS_INTEGER;

type_code PLS_INTEGER;
type_name VARCHAR2(32);
any_type anytype;
BEGIN
n := 1;
v := 'OneTwoThree';
d := SYSDATE;
any_data_array.EXTEND(3);
any_data_array(1) := anydata.ConvertNumber(n);
any_data_array(2) := anydata.ConvertVarchar2(v);
any_data_array(3) := anydata.ConvertDate(d);

FOR i IN any_data_array.FIRST .. any_data_array.LAST LOOP
type_name := any_data_array(i).GetTypeName();
type_code := any_data_array(i).GetType(any_type);

dbms_output.put_line('Iteration #' || i);
dbms_output.put_line('Type Name is ' || type_name);
dbms_output.put_line('Type Code is ' || type_code);

IF type_code = dbms_types.TYPECODE_VARCHAR2 THEN
value_result := any_data_array(i).GetVarchar2(temp_varchar2);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_varchar2);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
ELSIF type_code = dbms_types.TYPECODE_NUMBER THEN
value_result := any_data_array(i).GetNumber(temp_number);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_number);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
ELSIF type_code = dbms_types.TYPECODE_DATE THEN
value_result := any_data_array(i).GetDate(temp_date);
IF value_result = dbms_types.SUCCESS THEN
dbms_output.put_line('Value is ' || temp_date);
ELSIF value_result = dbms_types.NO_DATA THEN
dbms_output.put_line('No value!');
END IF;
END IF;

END LOOP;

END;

Result is:

Iteration #1
Type Name is SYS.NUMBER
Type Code is 2
Value is 1
Iteration #2
Type Name is SYS.VARCHAR2
Type Code is 9
Value is OneTwoThree
Iteration #3
Type Name is SYS.DATE
Type Code is 12
Value is 02/12/2006

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> DECLARE
  2    n NUMBER;
  3    v VARCHAR2(16);
  4    d DATE;
  5    TYPE any_data_array_typ IS TABLE OF anydata;
  6    any_data_array any_data_array_typ := any_data_array_typ();
  7 
  8    temp_varchar2 VARCHAR2(16);
  9    temp_number   NUMBER;
 10    temp_date     DATE;
 11    value_result  PLS_INTEGER;
 12 
 13    type_code PLS_INTEGER;
 14    type_name VARCHAR2(32);
 15    any_type  anytype;
 16  BEGIN
 17    n := 1;
 18    v := 'OneTwoThree';
 19    d := SYSDATE;
 20    any_data_array.EXTEND(3);
 21    any_data_array(1) := anydata.ConvertNumber(n);
 22    any_data_array(2) := anydata.ConvertVarchar2(v);
 23    any_data_array(3) := anydata.ConvertDate(d);
 24 
 25    FOR i IN any_data_array.FIRST .. any_data_array.LAST LOOP
 26      type_name := any_data_array(i).GetTypeName();
 27      type_code := any_data_array(i).GetType(any_type);
 28 
 29      dbms_output.put_line('Iteration #' || i);
 30      dbms_output.put_line('Type Name is ' || type_name);
 31      dbms_output.put_line('Type Code is ' || type_code);
 32 
 33      IF type_code = dbms_types.TYPECODE_VARCHAR2 THEN
 34        value_result := any_data_array(i).GetVarchar2(temp_varchar2);
 35        IF value_result = dbms_types.SUCCESS THEN
 36          dbms_output.put_line('Value is ' || temp_varchar2);
 37        ELSIF value_result = dbms_types.NO_DATA THEN
 38          dbms_output.put_line('No value!');
 39        END IF;
 40      ELSIF type_code = dbms_types.TYPECODE_NUMBER THEN
 41        value_result := any_data_array(i).GetNumber(temp_number);
 42        IF value_result = dbms_types.SUCCESS THEN
 43          dbms_output.put_line('Value is ' || temp_number);
 44        ELSIF value_result = dbms_types.NO_DATA THEN
 45          dbms_output.put_line('No value!');
 46        END IF;
 47      ELSIF type_code = dbms_types.TYPECODE_DATE THEN
 48        value_result := any_data_array(i).GetDate(temp_date);
 49        IF value_result = dbms_types.SUCCESS THEN
 50          dbms_output.put_line('Value is ' || temp_date);
 51        ELSIF value_result = dbms_types.NO_DATA THEN
 52          dbms_output.put_line('No value!');
 53        END IF;
 54      END IF;
 55 
 56    END LOOP;
 57 
 58  END;
 59  /

Iteration #1
Type Name is SYS.NUMBER
Type Code is 2
Value is 1
Iteration #2
Type Name is SYS.VARCHAR2
Type Code is 9
Value is OneTwoThree
Iteration #3
Type Name is SYS.DATE
Type Code is 12
Value is 04/12/2006

PL/SQL procedure successfully completed

SQL>