All about ANYDATA Datatype

ANYDATA DATATYPE:
CREATE:

CREATE TABLE tab1( col1 SYS.ANYDATA );

INSERT:

INSERT INTO tab1 VALUES(SYS.ANYDATA.convertVarchar2('Rajender Singh'));

INSERT INTO tab1 VALUES(SYS.ANYDATA.convertDate(SYSDATE));

INSERT INTO tab1 VALUES(SYS.ANYDATA.convertNumber(1972));

DATATYPE:

select TAB1.*,sys.anydata.gettypename(TAB1.COL1) typename from TAB1;

DATA:

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;

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;

end;
/
show errors

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