Oracle database has three generic (that is, generically programmed) SQL data types that enable you to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type, including object and collection types. You can also use these three types to create anonymous types, including anonymous collection types.

The three SQL types are implemented as opaque types. In other words, the internal structure of these types is not known to the database; their data can be queried only by implementing functions (typically 3GL routines) for the purpose. Oracle database provides both an OCI and a PL/SQL API for implementing such functions.

Of the three types, ANYTYPE is transient, and ANYDATA and ANYDATASET are not transient, but rather persistent. Transient types cannot be persistently stored because their structures are opaque to the database. You cannot create columns of transient types or make them attributes of persistent types.

Beginning with Oracle Database release 12c, release 12.2, transient types can be created on Active Data Guard instance if:

1. Real Time Apply is running on Active Data Guard, and

2. Logical Standby is not lagging far behind the Primary (typically, order of seconds).

The three generic SQL types are described in Table 8-2.

Each of these three types can be used with any built-in type native to the database as well as with object types and collection types, both named and unnamed. The types provide a generic way to work dynamically with type descriptions, lone instances, and sets of instances of other types. Using the APIs, you can create a transient ANYTYPE description of any kind of type. Similarly, you can create or convert (cast) a data value of any SQL type to an ANYDATA and can convert an ANYDATA (back) to a SQL type. And similarly again with sets of values and ANYDATASET.

The generic types simplify working with stored procedures. You can use the generic types to encapsulate descriptions and data of standard types and pass the encapsulated information into parameters of the generic types. In the body of the procedure, you can detail how to handle the encapsulated data and type descriptions of whatever type.

You can also store encapsulated data of a variety of underlying types in one table column of type ANYDATA or ANYDATASET. For example, you can use ANYDATA with Advanced Queuing to model queues of heterogeneous types of data. You can query the data of the underlying data types like any other data.

Example 8-10 defines and executes a PL/SQL procedure that uses methods built into SYS.ANYDATA to access information about data stored in a SYS.ANYDATA table column.

CREATE OR REPLACE TYPE dogowner AS OBJECT ( 
    ownerno NUMBER, ownername VARCHAR2(10) );
/
CREATE OR REPLACE TYPE dog AS OBJECT ( 
    breed VARCHAR2(10), dogname VARCHAR2(10) );
/
CREATE TABLE mytab ( id NUMBER, data SYS.ANYDATA );
INSERT INTO mytab VALUES ( 1, SYS.ANYDATA.ConvertNumber (5) );
INSERT INTO mytab VALUES ( 2, SYS.ANYDATA.ConvertObject (
    dogowner ( 5555, 'John') ) );
commit;

CREATE OR REPLACE procedure P IS
  CURSOR cur IS SELECT id, data FROM mytab;

  v_id mytab.id%TYPE;
  v_data mytab.data%TYPE;
  v_type SYS.ANYTYPE;
  v_typecode PLS_INTEGER;
  v_typename VARCHAR2(60);
  v_dummy PLS_INTEGER;
  v_n NUMBER;
  v_dogowner dogowner;
  non_null_anytype_for_NUMBER exception;
  unknown_typename exception;

BEGIN
  OPEN cur;
    LOOP
      FETCH cur INTO v_id, v_data;
      EXIT WHEN cur%NOTFOUND;
      v_typecode := v_data.GetType ( v_type /* OUT */ );
      CASE v_typecode
        WHEN Dbms_Types.Typecode_NUMBER THEN
          IF v_type IS NOT NULL
            THEN RAISE non_null_anytype_for_NUMBER; END IF;
          v_dummy := v_data.GetNUMBER ( v_n /* OUT */ );
          Dbms_Output.Put_Line (
            To_Char(v_id) || ': NUMBER = ' || To_Char(v_n) );
        WHEN Dbms_Types.Typecode_Object THEN
          v_typename := v_data.GetTypeName();
          IF v_typename NOT IN ( 'HR.DOGOWNER' )
            THEN RAISE unknown_typename; END IF;
          v_dummy := v_data.GetObject ( v_dogowner /* OUT */ );
          Dbms_Output.Put_Line (
            To_Char(v_id) || ': user-defined type = ' || v_typename ||
            '(' || v_dogowner.ownerno || ', ' || v_dogowner.ownername || ' )' );
      END CASE;
    END LOOP;
    CLOSE cur;

EXCEPTION
  WHEN non_null_anytype_for_NUMBER THEN
      RAISE_Application_Error ( -20000,
        'Paradox: the return AnyType instance FROM GetType ' ||
        'should be NULL for all but user-defined types' );
  WHEN unknown_typename THEN
      RAISE_Application_Error ( -20000,
        'Unknown user-defined type ' || v_typename ||
        ' - program written to handle only HR.DOGOWNER' );
END;
/

SELECT t.data.gettypename() FROM mytab t;
SET SERVEROUTPUT ON;
EXEC P;

T.DATA.GETTYPENAME()
-------------------------------------------------------------
SYS.NUMBER
HR.DOGOWNER
1: NUMBER = 5
2: user-defined type = HR.DOGOWNER(5555, John )