Search notes:

Oracle: anyType, anyData and anyDataSet

sys.anyType

anyType allows to create generic (aka transient) object-types at runtime. Such a creation is started with beginCreate and ended with endCreate.
An instance of an anyType can also be created from a persistent type (that was created using the ordinary create type statement) with the global function sys.getAnyTypeFromPersistent(schemaName, typeName).
The OCI type that corresponds to anyType is OCIType.
Methods:
beginCreate
endCreate
setInfo create additional information required for construction a collection or built-in types
getInfo
addAttr adds an attribute to an anyType whose type code is dbms_types.typecode_object
getAttrElemInfo Gets type information for an attribute of the type (if dbms_type.typecode_object)
getPersistent desupported, use getANYTypeFromPersistent()

sys.anyData

An instance of sys.anyData can be created with the following two ways
The OCI type that corresponds to anyData is OCIAnyData.
beginCreate start creating a new anyData object whose type corresponds to a anyType that was previously created.
endCreate
pieceWise must be called to iterate over the elements of an object in order to get* or set* their values. Unfortunately, it does not seem possible to get or set a value by either the element's name or position.
setXXX XXX is a data type such as number, date etc.
getXXX
convertXXX
accessXXX XXX corresponds to the data type of the returned value. If the anyData on which this method is called has another «underlying» data type, accessXXX returns null.
getTypeName Returns something like SYS.VARCHAR2, USR.MY_TYPE. If callled on a transient type, the function returns null.
getType

sys.anyDataSet

anyDataSet is a generic collection type.
beginCreate
endCreate
addInstance
pieceWise
getCount returns the number of data instances in the anyDataSet
getTypeName
getType
setXXX
getXXX
getInstance
setXXX
The OCI type that corresponds to anyData is OCIAnyDataSet.

getTvoid()

TODO…

Example

drop   type type_one;
create type type_one as object (
   foo number  (5,2),
   bar varchar2(100 byte),
   baz date
);
/

drop   type type_two;
create type type_two as object (
   a   number(5,2),
   b   char  (5 byte)
);
/

 -- TODO...
-- create type type_two_t as table of type_two;
-- /
Github repository Oracle-patterns, path: /Installed/types/any/passAnyObject/types.sql
create or replace procedure passAnyObject(obj anydata) is

   obj_2             type_two;
   typeName          varchar2(100);
   typeCode          integer;
   typeCode_getInfo  integer;
   typeCodeText      varchar2(50);
   objType           anytype;

   prec              pls_integer;
   scale             pls_integer;
   len               pls_integer;
   csid              pls_integer;
   csfrm             pls_integer;

   schema_name       varchar2(100);
   type_name         varchar2(100);
   version           varchar2(100);
   numelems          pls_integer;

--
-- Prevent  PLS-00363: expression 'OBJ' cannot be
--          used as an assignment target:
--
   obj_              anyData := obj;

   function typeCodeToText(tc pls_integer) return varchar2 is
   begin
     return
        case tc

             when dbms_types.typecode_object   then 'object'
             when dbms_types.typecode_varchar2 then 'varchar2'
             when dbms_types.typecode_number   then 'number'
             when dbms_types.typecode_date     then 'date'
             when dbms_types.typecode_char     then 'char'
             else                                   'todo: implement me'
        end;

    end typeCodeToText;

begin

   typeName := obj_.getTypename;
   typeCode := obj_.getType(objType);

   typeCodeText := typeCodeToText(typeCode);

   dbms_output.put_line('Received ' || typeCodeText || ' with type name ' || typeName);

   if objType is null then
      dbms_output.put_line('  objType is null');
   end if;

--
-- Special treatment for instance of type_two: get an
-- object for that type:
--
   if typeName = user || '.TYPE_TWO' then

      if obj_.getObject(obj_2) = dbms_types.success then

         dbms_output.put_line('  obj_2.a = ' || obj_2.a);
         dbms_output.put_line('  obj_2.b = ' || obj_2.b);
       else
         dbms_output.put_line('  getObject failed');
       end if;

   end if;


   case typeCode
   when dbms_types.typecode_object then

        typeCode_getInfo := objType.getInfo(
               prec, scale, len, csid, csfrm,
               schema_name, type_name,
               version,
               numelems
        );

         dbms_output.put_line('  object ' || schema_name || '.' || type_name || ' has ' || numelems || ' elements');

      --
      -- Why is it not possible to access the members
      -- in an anyType by name or position?
      --
         obj_.piecewise;
         for pos in 1 .. numelems loop

             declare
                attr_elt_type anytype;
                aname varchar2(100);
                val   varchar2(100);
                ret   pls_integer;
             begin

                 typeCode := objType.getAttrElemInfo(
                     pos,
                     prec, scale, len, csid, csfrm,
                     attr_elt_type,
                     aname
                 );

                 case when typeCode = dbms_types.typecode_varchar2 then ret := obj_.getVarchar2(val);
                      when typeCode = dbms_types.typecode_number   then ret := obj_.getNumber  (val);
                      when typecode = dbms_types.typecode_date     then ret := obj_.getDate    (val);
                      when typecode = dbms_types.typecode_char     then ret := obj_.getChar    (val);
                      else val := '?';
                 end case;

                 dbms_output.put_line('  ' || aname || ' (' || typeCodeToText(typeCode) || ') - prec: ' || prec || ', scale: ' || scale || ', len: ' || len || ', val = ' || val);

             end;
           end loop;

      when dbms_types.typecode_varchar2 then
      --
      -- Alternatively, use obj_.getVarchar2(vc):
      --
         dbms_output.put_line('  Varchar2 is: ' || obj_.accessVarchar2);
      else
         dbms_output.put_line('  TODO: implment me');
      end case;

      dbms_output.put_line('');

end passAnyObject;
/

show errors
Github repository Oracle-patterns, path: /Installed/types/any/passAnyObject/passAnyObject.sql
declare

--
-- Variables for the transient type and the instance
-- that is created from it:
--

   type_transient     anytype;
   obj_transient      anydata;

--
-- Variables for two instances of persistent types:
--
   obj_one type_one;
   obj_two type_two;

begin

--
-- Create two instances of persistent types:
--
   obj_one := new type_one(42, 'Hello world', sysdate);
   obj_two := new type_two(123.45, 'abcde');

--
-- Start creating a transient object type:
--
   anytype.beginCreate(dbms_types.typecode_object, type_transient);
   --
   -- The type has three attributes:
   --
      type_transient.addAttr('NUM', dbms_types.TYPECODE_NUMBER  , prec => null, scale => null, len=> null,csid => null, csfrm => null);
      type_transient.addAttr('TXT', dbms_types.TYPECODE_varchar2, prec => null, scale => null, len=>  100,csid => null, csfrm => null);
      type_transient.addAttr('DAT', dbms_types.TYPECODE_date    , prec => null, scale => null, len=> null,csid => null, csfrm => null);

   type_transient.endCreate;

--
-- Start creating an instance of that type:
-- 
   anyData.beginCreate(type_transient, obj_transient);

      obj_transient.setNumber(42);
      obj_transient.setVarchar2('Hello world');
      obj_transient.setDate(sysdate);

   obj_transient.endCreate();

   passAnyObject(obj_transient                       );
   passAnyObject(anyData.convertObject  (obj_one    ));
   passAnyObject(anyData.convertObject  (obj_two    ));
   passAnyObject(anyData.convertVarchar2('some text'));

   -- tq84_any_test.p(anydata.convertObject(obj));
end;
/
Github repository Oracle-patterns, path: /Installed/types/any/passAnyObject/run.sql

See also

$ORACLE_HOME/rdbms/admin/dbmsany.sql

Index