drop table temp1
/

create table temp1
(
 a number not null
,b sys.anydata
)
/

insert into temp1 values (1,sys.anydata.convertnumber(1))
/
insert into temp1 values (2,sys.anydata.convertdate(sysdate))
/
insert into temp1 values (3,sys.anydata.convertvarchar2('a'))
/

commit
/

select count(*) from temp1
/

col b format a20 trunc
select * from temp1
/

col typename format a20 trunc
select temp1.*,sys.anydata.gettypename(temp1.b) typename from temp1
/

create or replace
package pkg_temp1
as

   function getnumber (anydata_p in sys.anydata) return number;
   function getdate (anydata_p in sys.anydata) return date;
   function getvarchar2 (anydata_p in sys.anydata) return varchar2;
   function get_o_temp1 (anydata_p in sys.anydata) return o_temp1;
   function get_c_temp1 (anydata_p in sys.anydata) return c_temp1;

end;
/
show errors

create or replace
package body pkg_temp1
as

   function getnumber (anydata_p in sys.anydata) return number is
      x number;
      thenumber_v number;
   begin
      x := anydata_p.getnumber(thenumber_v);
      return (thenumber_v);
   end;

   function getdate (anydata_p in sys.anydata) return date is
      x number;
      thedate_v date;
   begin
      x := anydata_p.getdate(thedate_v);
      return (thedate_v);
   end;

   function getvarchar2 (anydata_p in sys.anydata) return varchar2 is
      x number;
      thevarchar2_v varchar2(4000);
   begin
      x := anydata_p.getvarchar2(thevarchar2_v);
      return (thevarchar2_v);
   end;

   function get_o_temp1 (anydata_p in sys.anydata) return o_temp1 is
      x number;
      o_temp1_v o_temp1;
   begin
      x := anydata_p.getobject(o_temp1_v);
      return (o_temp1_v);
   end;

   function get_c_temp1 (anydata_p in sys.anydata) return c_temp1 is
      x number;
      c_temp1_v c_temp1;
   begin
      x := anydata_p.getcollection(c_temp1_v);
      return (c_temp1_v);
   end;

end;
/
show errors

col thevalue format a20 trunc
select temp1.*,sys.anydata.gettypename(temp1.b) typename
,case
      when sys.anydata.gettypename(temp1.b) = 'SYS.NUMBER' then to_char(pkg_temp1.getnumber(temp1.b))
      when sys.anydata.gettypename(temp1.b) = 'SYS.DATE' then to_char(pkg_temp1.getdate(temp1.b),'dd-mon-rrrr hh24:mi:ss')
      when sys.anydata.gettypename(temp1.b) = 'SYS.VARCHAR2' then pkg_temp1.getvarchar2(temp1.b)
 end thevalue
 from temp1
/

update temp1 set b = sys.anydata.convertnumber(2)
where a = 1
/
update temp1 set b = sys.anydata.convertdate(sysdate+1)
where a = 2
/
update temp1 set b = sys.anydata.convertvarchar2('b')
where a = 3
/

col thevalue format a20 trunc
select temp1.*,sys.anydata.gettypename(temp1.b) typename
,case
      when sys.anydata.gettypename(temp1.b) = 'SYS.NUMBER' then to_char(pkg_temp1.getnumber(temp1.b))
      when sys.anydata.gettypename(temp1.b) = 'SYS.DATE' then to_char(pkg_temp1.getdate(temp1.b),'dd-mon-rrrr hh24:mi:ss')
      when sys.anydata.gettypename(temp1.b) = 'SYS.VARCHAR2' then pkg_temp1.getvarchar2(temp1.b)
 end thevalue
 from temp1
/

create or replace type o_temp1 oid '3150D5BF61DE33EDE0440003BA62E91A' is object (a number,b number,c number)
/

insert into temp1 values (4,sys.anydata.convertobject(o_temp1(1,2,3)))
/

create or replace type c_temp1 oid '3150D5BF61DF33EDE0440003BA62E91A' is table of o_temp1
/

set serveroutput on
declare
   c_temp1_v c_temp1;
begin
   select cast(multiset(select * from (
      select 1 c1,2 c2,3 c3 from dual union all
      select 4 c1,5 c2,6 c3 from dual union all
      select 7 c1,8 c2,9 c3 from dual
                                      )
                       ) as c_temp1
              )
   into c_temp1_v
   from dual;
dbms_output.put_line(c_temp1_v.count);
   insert into temp1 values (5,sys.anydata.convertcollection(c_temp1_v));
end;
/

COL AC_TEMP1 FORMAT A62
select temp1.*
      ,case when sys.anydata.gettypename(temp1.b) = 'SYS.NUMBER' then pkg_temp1.getnumber(temp1.b) end anumber
      ,case when sys.anydata.gettypename(temp1.b) = 'SYS.DATE' then  pkg_temp1.getdate(temp1.b) end adate
      ,case when sys.anydata.gettypename(temp1.b) = 'SYS.VARCHAR2' then  pkg_temp1.getvarchar2(temp1.b) end avarchar2
      ,case when substr(sys.anydata.gettypename(temp1.b),instr(sys.anydata.gettypename(temp1.b),'.')+1) = 'O_TEMP1' then pkg_temp1.get_o_temp1(temp1.b) end ao_temp1
      ,case when substr(sys.anydata.gettypename(temp1.b),instr(sys.anydata.gettypename(temp1.b),'.')+1) = 'C_TEMP1' then pkg_temp1.get_c_temp1(temp1.b) end ac_temp1
from temp1
/

insert into temp1 values (6,sys.anydata.convertxml(dbms_xmlgen.getxmltype('select * from dual')))
/

select xmltype.createxml(cursor(select * from dual)) c1 from dual;
select xmltype.createxml(cursor(select * from dual)).getclobval() c1 from dual;

insert into temp1 values (7,sys.anydata.convertclob(dbms_xmlgen.getxml('select * from dual')))
/
insert into temp1 values (7,sys.anydata.convertclob(xmltype.createxml(cursor(select * from dual)).getclobval()))
/

declare
   clob_v clob := '';
begin
   insert into temp1 values (7,sys.anydata.convertclob(clob_v));
end;
/

declare
   clob_v clob := '';
   anydata_v sys.anydata;
begin
   anydata_v := sys.anydata.convertclob(clob_v);
   insert into temp1 values (7,anydata_v);
end;
/

create type o_clob is object (aclob clob)
/

insert into temp1 values (11,sys.anydata.convertobject(o_clob(dbms_xmlgen.getxml('select * from dual'))))
/