SYS.ANYDATA

Sys.anydata is a new datatype introduced in 9i. This datatype can be used to define a column which can be of any datatype. The advantage of this is that if we insert a data into a column defined as anydata datatype, it remains a date. If we insert a number, it remains a number. Below is the list of some example code provided on asktom site .

create table t(x sys.anyData);

insert into t values(sys.anyData.convertNumber(5));

insert into t values(sys.anyData.convertVarchar2('hello world'));

insert into t values(sys.anyData.convertDate(sysdate));

11:54:52 SQL> select * from t;

X()
-----------------------------------------

ANYDATA()
ANYDATA()
ANYDATA()


SQL> create or replace function getData(p_x in sys.anyData) return varchar2
2 as
3 l_varchar2 varchar2(4000);

4 l_rc number;
5 begin
6 case p_x.gettypeName
7 when 'SYS.NUMBER' then
8 l_rc := p_x.getNumber(l_varchar2);
9 when 'SYS.DATE' then

10 l_rc := p_x.getDate(l_varchar2);
11 when 'SYS.VARCHAR2' then
12 l_rc := p_x.getVarchar2(l_varchar2);
13 else
14 l_varchar2 := '** unknown **';
15 end case;
16

17 return l_varchar2;
18 end;
19 /

Function created.

SQL> select getData(x) getdata from t;


GETDATA
____________

5
19-MAR-02
hello world