René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

anydata example [Oracle]

This example demonstrates how instances of anydata are created and then stored in a table.
First, A table is created that stores an anydata: 
create table t_ (col_ sys.anydata);
Then three records are inserted into the table. The inserted instances are created «on the fly» using anydata's createXXX methods while they're inserted. 
insert into t_ values (sys.anydata.convertNumber(42));
insert into t_ values (sys.anydata.convertVarchar2('fourty two'));
insert into t_ values (sys.anydata.convertDate(sysdate));
An ordinary select on the table: 
select t.col_.getTypeName() from t_ t;
Unfortunately, the output of the query doesn't tell us much about what exactly was inserted: 
COL_()
------------------
ANYDATA()
ANYDATA()
ANYDATA()
PL/SQL is needed to a) determine the actual type of the inserted values and b) their value: 
declare
  n   number;
  v   varchar2(20);
  d   date;
begin

  for r in (select t.col_ from t_ t) loop

    case r.col_.getTypeName

      when 'SYS.NUMBER' then
        if r.col_.getNumber(n) = dbms_types.success then
          dbms_output.put_line(n);
        end if;

      when 'SYS.VARCHAR2' then
        if r.col_.getVarchar2(v)= dbms_types.success then
          dbms_output.put_line(v);
        end if;

      when 'SYS.DATE' then
        if r.col_.getDate(d)= dbms_types.success then
          dbms_output.put_line(d);
        end if;

    end case;
  end loop;
end;
/

42
fourty two
21-APR-07