Your answer is adequate in term of giving me a profound understanding of the topic.
Good work , Kyte.

Thanks Tom for the wonderful explaination.

I'm trying to use 9i reflection in PL/SQL to extract data that I feeded into MY_FUNCTION like this:

SELECT * FROM TABLE(MY_FUNCTION(CURSOR(
select ename, sal from emp
)))

In MY_FUNCTION i want to be able to generically extract each record data:

CREATE or replace FUNCTION MY_FUNCTION( cur SYS_REFCURSOR )
RETURN IntegerSet PIPELINED IS
field1 VARCHAR2(100);
field2 INTEGER;
begin
loop
fetch cur into ...;
exit when cur%NOTFOUND;
-- get 2 fields
end loop;
END;

Now I'm trying to figure out what code do I need in MY_FUNCTION that would extract those 2 fields for each record...

I am highly impressed by your answere Kyte.
Keep going ON.

Hi Tom!

You are simply great with a pool full of knowledge.

Cheers,

Good explanation, and potential implimentation usage, but it mainly looks like an unstructured data store. One would have to be careful on using it else it turn into a pile of brown smelly stuff.

Much better explanantion than documentation. I understood the concepts after reading the article once. I wish all technical documentation was so straight forward. Thanks.

Hello TOM,

        I have following queries which i couldn't understand.  Please explain me.





   1. SELECT mgr, ename, hiredate,
       COUNT(*) OVER (PARTITION BY mgr ORDER BY hiredate
       RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count
      FROM emp
   2. SELECT last_name, hire_date, salary, SUM(salary) 
   OVER (ORDER BY hire_date 
   RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
   FROM employees;



   3.

  1  SELECT empno,ename,hiredate FROM emp
  2    WHERE hiredate - TO_DSINTERVAL('100 10:00:00')
  3*   > DATE '1985-01-01'
SQL> /

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7788 SCOTT      19-APR-87
      7876 ADAMS      23-MAY-87


    4. How can i manipulate data with this table?.

  SQL> drop table tr;

   Table dropped.
 
  SQL>  create table tr(t1 INTERVAL YEAR TO MONTH, t2 INTERVAL DAY TO SECOND );

  Table created.



    5. I want to give my hire date in the following query. Is it possible?.


      SELECT EXTRACT(YEAR FROM DATE '2002-08-12'  )  from dual;

Thank you.

regards,
Siva

       

I need to store bind values, which are used in JDBC statements like this:
select * from emp where empno = ? and ename = ?
Bind variables come in different shapes and colors: in my example, they would be java.math.BigDecimal and java.land.String. Therefore, I have 2 options
1. ANYDATA column
2. serialize java object into CLOB
I wonder which method is better.

The second question is that after filling out my database with bind values I would like to query it. I guess that I wouln't be able to query anything with method #2, but suspect that it's the same with method #1. What is your opinion?

I want to store Dates, Numbers and texts in VARCHAR2. The table which will store those data hast to be compact and as efficient as possible due to frequent and fast SELECTs (I use IOT):

array_id NUMBER,
param_name VARCHAR2(15),
min_val VAERCHARE2(30),
max_val VARCHAR2(30))

where the first 3 columns are PK.
Data could be ranges (for dates and numbers) or discret (for texts only):

array_id param_name min_val max_val
1 GENDER M
1 COUNTRY US
1 DOB 19500101 19991231
1 POINT -10.00 +20.00

and SELECT has:

WHERE param_name=p_param_name
AND (p_val = min_val OR p_val BETWEEN min_val AND max_val)

The problem is that I want to use number comparision on text which does not work for negative numbers. And I don't want to use TO_NUMBER conversion on database columns due to decreasing performance.

My question is if there any way (e.g. number formats) which can deal with negative numbers in VARCHAR2?

Tkanks, but your solution will not help me. It is not the question only of saving space but reducing of steps during an evaluation of query. E.g. if the incomming parameter is:

p_param_name='GENDER'
p_val= 'X'

then my query has to by much more complex otherwise it will raise an error:

WHERE param_name=p_param_name
AND (p_val = text_val -- Not found then continue
OR p_val BETWEEN min_val AND max_val) -- Implicit Conversion Raise an Error

If I may just say so, perhaps ...

14 if ( p_x.getDate( l_date ) = dbms_types.success )
15 then
16 l_varchar2 := l_date;
17 end if;

... might be better as ...

14 if ( p_x.getDate( l_date ) = dbms_types.success )
15 then
16 l_varchar2 := to_char(l_date,'DD-Mon-YYYY HH24:mi:ss');
17 end if;

Seem fair? ;)

Sorry, forgot to sign that.

Anyhoo, that's partly true except that "select sysdate from dual" preserves the time and the undisplayed century data, if you are selecting into a date variable, or as an in-line view. The getData() function is returning a varchar2, so the information is lost.

I'm just being reactionary here -- as a regular reader i know how you always insist that such un-pictured conversions are a bad thing, so i thought i'd hit you with your own stick as it were.

hi tom,

thanks, the example was very helpfull and i use the sys.anydata regular. i would like to ask you some open questions on this issue.

1) how does oracle store the anydata internal (blob, varchar2 .. ) in memory and in table?
2) is there a way to find out how big (in bytes) the 'thing' in anydata is? like i can get the type-name using the methode
gettypename() without 'casting' before?
2) i use the ConvertObject methode very often ... it is very usefull when you deal with objects. i would also like to store some collections in the same way using ConvertCollection methode for the sys.anydata object but i cant figure out how to do this. (the oracle-docu is a little bit short - just giving us the 'signature' of the methode but nothing else). may be you could provide us with a little example so we can see hwo to use it.

thx in advance

hannibal

p.s.: congradulations to your new book!!! very GOOD and UESFULL!!!! i like it!!!!

h.

1) it is an invisible type -- magic.

2) 
ops$tkyte@ORA920LAP> create table t ( a int, x sys.anydata );

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t values ( 1, sys.anyData.convertNumber(5) );

1 row created.

ops$tkyte@ORA920LAP> insert into t values ( 2, sys.anyData.convertDate(sysdate) );

1 row created.

ops$tkyte@ORA920LAP> insert into t values ( 3, sys.anyData.convertVarchar2('hello world') );

1 row created.

ops$tkyte@ORA920LAP> insert into t values ( 4, sys.anyData.convertVarchar2('hello world hello world hello world') );

1 row created.

ops$tkyte@ORA920LAP> select a, vsize(x) from t;

         A   VSIZE(X)
---------- ----------
         1         48
         2         53
         3         57
         4         81

ops$tkyte@ORA920LAP>

 

Hello Tom,

thx for all your help. i must comeback with some questions ... as you can see i tested the sys.anydata
with some objects and tables of objects. by using my little look-function i could look into the dump and
i guess i got a glimps on how your are storing the data magically :-). however, i looks like the results
of vsize and dump are only correct as long as the data is not too big???? the result of vsize on column
11 should be much bigger then column 10 but it is'nt, so i guess you shift the data to an other place
when it gets to big for a LONG or what ever. so i start to think vsize does only work for small(er) items.
is this correct? how can i get the real size of a bigger object if possible? is there a at least chance
to recognize that the value given by vsize is not the real value but a much smaller one. f.e., by examening
the dump?

i dislike it to ask you again if you could give an example for the correct usage of
sys.anydata.ConvertCollection. i know you are very buisy ... may be you give me a link where i can
read more on this topic.

regards

hannibal

== just creating some entryies ..

jom@NINA.US.ORACLE.COM> desc t
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER(38)
LDATA SYS.ANYDATA

jom@NINA.US.ORACLE.COM> insert into t values ( 1 , sys.anydata.convertnumber(1));

1 row created.

jom@NINA.US.ORACLE.COM> insert into t values ( 2 , sys.anydata.convertvarchar2('tom'));

1 row created.

jom@NINA.US.ORACLE.COM> insert into t values ( 3 , sys.anydata.convertdate(sysdate));

1 row created.

jom@NINA.US.ORACLE.COM> desc myobj
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
HEADER VARCHAR2(256)
BODY VARCHAR2(1024)

== taking a object type
jom@NINA.US.ORACLE.COM> r
1 declare
2 obj myobj;
3 begin
4 obj := myobj(null,null);
5 obj.header := 'this is the header';
6 obj.body := 'this is the body - just a text ... ';
7 insert into t values ( 6 , sys.anydata.convertobject(obj));
8* end;

jom@NINA.US.ORACLE.COM> r
1 declare
2 obj myobj;
3 begin
4 obj := myobj(null,null);
5 obj.header := 'this is the header';
6 obj.body := 'this is the body - just a looooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
og text ... ';
7 insert into t values ( 7 , sys.anydata.convertobject(obj));
8* end;

PL/SQL procedure successfully completed.

==taking a more complex object type
jom@NINA.US.ORACLE.COM> desc obj_test_tab;
obj_test_tab TABLE OF OBJ_TEST
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
X NUMBER(5)
S VARCHAR2(32)

jom@NINA.US.ORACLE.COM> desc obj_test
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
X NUMBER(5)
S VARCHAR2(32)

jom@NINA.US.ORACLE.COM> create or replace type myobj2 as object ( payload obj_test_tab );
2 /

Type created.

jom@NINA.US.ORACLE.COM> desc myobj2
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PAYLOAD OBJ_TEST_TAB

jom@NINA.US.ORACLE.COM> r
1 declare
2 obj2 myobj2;
3 tab obj_test_tab;
4 obj obj_test;
5 begin
6 obj := obj_test( null, null);
7 tab := obj_test_tab(null);
8 obj.x := 1;
9 obj.s := 'hallo';
10 tab.extend;
11 tab(1) := obj;
12 obj.x := 2;
13 obj.s := 'hallo2';
14 tab.extend;
15 tab(2) := obj;
16 obj2 := myobj2(null);
17 obj2.payload := tab;
18 insert into t values ( 10 , sys.anydata.convertobject(obj2));
19* end;

PL/SQL procedure successfully completed.

jom@NINA.US.ORACLE.COM> r
1 declare
2 obj2 myobj2;
3 tab obj_test_tab;
4 obj obj_test;
5 v_int int;
6 begin
7 obj := obj_test( null, null);
8 tab := obj_test_tab(null);
9 for v_int in 1..1000
10 loop
11 obj.x := v_int;
12 obj.s := 'hallo'||to_char(v_int);
13 tab.extend;
14 tab(v_int) := obj;
15 end loop;
16 obj2 := myobj2(null);
17 obj2.payload := tab;
18 insert into t values ( 11 , sys.anydata.convertobject(obj2));
19* end;

PL/SQL procedure successfully completed.

== the size is o.k., this is a much bigger object type.
jom@NINA.US.ORACLE.COM> r
1 declare
2 obj2 myobj2;
3 tab obj_test_tab;
4 obj obj_test;
5 v_int int;
6 begin
7 obj := obj_test( null, null);
8 tab := obj_test_tab(null);
9 for v_int in 1..1000
10 loop
11 obj.x := v_int;
12 obj.s := 'hallo'||to_char(v_int);
13 tab.extend;
14 tab(v_int) := obj;
15 end loop;
16 obj2 := myobj2(null);
17 obj2.payload := tab;
18 dbms_output.put_line(tab(600).s);
19 dbms_output.put_line(tab.count);
20* end;
hallo600
1001

PL/SQL procedure successfully completed.

jom@NINA.US.ORACLE.COM> r
1* select t.id , t.ldata, t.ldata.gettypename(), vsize(t.ldata) , dump(t.ldata), look(substr(dump(t.ldata),instr(dump(t.ldata),':'
,1,1)+2)) from t t

ID
----------
LDATA()
----------------------------------------------------------------------------------------------------
T.LDATA.GETTYPENAME()
----------------------------------------------------------------------------------------------------
VSIZE(T.LDATA)
--------------
DUMP(T.LDATA)
----------------------------------------------------------------------------------------------------
LOOK(SUBSTR(DUMP(T.LDATA),INSTR(DUMP(T.LDATA),':',1,1)+2))
----------------------------------------------------------------------------------------------------
1
ANYDATA()
SYS.NUMBER
48
Typ=58 Len=48: 0,1,0,0,0,0,0,1,0,0,0,0,148,115,0,32,9,0,0,0,0,0,0,16,0,0,0,0,0,1,133,1,16,1,1,0,2,0,
2,0,0,0,0,2,193,2,0,0
? ? ös ? ?à???? ? ? ?-?

2
ANYDATA()
SYS.VARCHAR2
49
Typ=58 Len=49: 0,1,0,0,0,0,0,1,0,0,0,0,148,116,0,33,9,0,0,0,0,0,0,17,0,0,0,0,0,1,133,1,17,1,1,0,2,0,
9,0,0,0,0,3,116,111,109,0,0
? ? öt ! ? ?à???? ? ?tom

3
ANYDATA()
SYS.DATE
53
Typ=58 Len=53: 0,1,0,0,0,0,0,1,0,0,0,0,148,117,0,37,9,0,0,0,0,0,0,21,0,0,0,0,0,1,133,1,21,1,1,0,2,0,
12,0,0,0,0,7,120,103,11,24,12,12,59,0,0
? ? öu % § ?à?§?? ? ? xg????;

5
ANYDATA()
JOM.MYOBJ
69
Typ=58 Len=69: 0,1,0,0,0,0,0,1,0,0,0,0,148,118,0,53,9,0,0,0,0,0,0,37,0,0,0,0,0,1,133,1,37,1,1,0,4,0,
108,194,204,83,80,126,191,69,39,165,152,44,62,150,104,32,64,0,1,0,0,0,0,5,132,1,5,255,255,0,0
? ? öv 5 % ?à?%?? ? l-¦SP~+E'Ñÿ,>ûh @ ? ?ä??

6
ANYDATA()
JOM.MYOBJ
122
Typ=58 Len=122: 0,1,0,0,0,0,0,1,0,0,0,0,148,119,0,106,9,0,0,0,0,0,0,90,0,0,0,0,0,1,133,1,90,1,1,0,4,
0,108,194,204,83,80,126,191,69,39,165,152,44,62,150,104,32,64,0,1,0,0,0,0,58,132,1,58,18,116,104,105
,115,32,105,115,32,116,104,101,32,104,101,97,100,101,114,35,116,104,105,115,32,105,115,32,116,104,10
1,32,98,111,100,121,32,45,32,106,117,115,116,32,97,32,116,101,120,116,32,46,46,46,32,0,0
? ? öw j Z ?à?Z?? ? l-¦SP~+E'Ñÿ,>ûh @ ? :ä?:?this is the header#this is the bod
y - just a text ...

7
ANYDATA()
JOM.MYOBJ
357
Typ=58 Len=357: 0,1,0,0,0,0,0,1,0,0,0,0,148,120,1,85,9,0,0,0,0,0,1,69,0,0,0,0,0,1,133,1,254,0,0,1,69
,1,1,0,4,0,108,194,204,83,80,126,191,69,39,165,152,44,62,150,104,32,64,0,1,0,0,0,1,33,132,1,254,0,0,
1,33,18,116,104,105,115,32,105,115,32,116,104,101,32,104,101,97,100,101,114,254,0,0,1,2,116,104,105,
115,32,105,115,32,116,104,101,32,98,111,100,121,32,45,32,106,117,115,116,32,97,32,108,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,11
1,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,111,103,32,116,101,120,116,32,46,46,46
,32,0,0
? ? öx?U ?E ?à?¦ ?E?? ? l-¦SP~+E'Ñÿ,>ûh @ ? ?!ä?¦ ?!?this is the header¦ ??thi
s is the body - just a loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooog text ...

10
ANYDATA()
JOM.MYOBJ2
103
Typ=58 Len=103: 0,1,0,0,0,0,0,1,0,0,0,0,148,167,0,87,9,0,0,0,0,0,0,71,0,0,0,0,0,1,133,1,71,1,1,0,4,0
,108,102,5,16,101,175,197,73,143,149,81,147,71,42,254,98,132,0,1,0,0,0,0,39,132,1,39,35,136,1,35,1,3
,0,3,12,132,1,12,2,193,2,5,104,97,108,108,111,13,132,1,13,2,193,3,6,104,97,108,108,111,50,255,0,0
?-??hallo2 öº W G ?à?G?? ? lf??e»+IÅòQôG*¦bä ? 'ä?'#ê?#?? ??ä???-??hallo


11
ANYDATA()
JOM.MYOBJ2
44
Typ=58 Len=44: 0,1,0,0,0,0,0,1,0,0,0,0,148,168,0,28,5,0,0,0,0,2,2,72,0,0,0,0,0,2,2,192,0,20,2,192,0,
21,2,192,0,22,0,0
? ? ö¿ ?? ??H ??+ ¶?+ §?+ ?

8 rows selected.

Hi Tom,

My database version is 10.2.0.1.0 and I tried to insert a clob into an anydata field but got an error:

create table test (x anydata);
insert into test values (anyData.ConvertClob(to_clob('123adsf')));

ORA-22370: incorrect usage of method AnyData Insert

Is there something I am doing wrong? Or is clob not supported?

Hi Tom,

By applying a little trick, the content of Anydata
can be display in SQL using Oracle 10G.


http://oraqa.com/2007/09/09/how-to-display-the-contents-of-a-column-of-anydata-data-type-in-sql/

Thanks,

Frank

We're looking at implementing an extensible data model to support a CMS, whereby metadata for new sites (aka tenants) managed by the CMS could be deployed to the Oracle database.

Following a Software-as-a-Service (SaaS) model, we are setting up an extensible data model (multi-tenant architecture), to store metadata on each custom field for each tenant.

It seems to me like the any* types are a nice match for this kind of implementation. Instead of storing the datatype as a varchar2, for each custom field, and having to cast the values each time a query is performed etc... we consolidate all of this behing the any* object types... with a nice PL/SQL front-end to it to store and retrieve values. (PL/SQL called from a J2EE application)

I assume query performance will be as good, if not better, than suggested multi-tenant, purely relational (no objects) solutions out there...

Thanks for the info Tom... very useful article and good tips as always.

Hi Tom

Excellent article, very clear and useful. I have used ANYDATA in a few different systems, though mostly in pl/sql rather than table persistence. This is mostly for the reasons you mention about the ability compare the same types against each other efficiently.

Reading through some of the responses I get the impression that some readers may be considering using ANYDATA as a way out of having to do more complex sql or schema design. Might it be worthwhile to mention the cases where ANYDATA is the wrong thing to do? e.g. if you store dates, numbers and strings as anydata but want to query the column (e.g. BETWEEN for dates or numbers or LIKE for strings) then ANYDATA persistence may not be what you are looking for etc.

Just a thought

Conor

Tom

Is there an easy way to implement generic dynamic sql functionality using the following:

We currently have a function that returns parameters as SYS.ANYDATA.

PROCEDURE ReturnParam ( pOperationID IN NUMBER,
pParamID IN NUBER,
pParamValue OUT sys.anydata );

For each pOperationID we have a variable number of parameters (currently up to 3). Each can be either DATE, NUMBER or VARCHAR2.

We then need to call EXECUTE IMMEDIATE 'my_Operation(:1,:2:3)' USING param1, param2, param3;

param1,param2 and param3 are returned as sys.anydata.

What is the easiest way to do this?

For 1 parameter we have the following code working but as you can see it would be verbose to replicate for 2 or 3 parameters.

ReturnParam ( vGroupOperationID, 1, vParamValue );
               
CASE vParamValue.gettypeName
WHEN 'SYS.NUMBER' THEN
l_flag := vParamValue.getNumber(vGenNumber1);
EXECUTE IMMEDIATE vSqlStmnt USING vGenNumber1;
WHEN 'SYS.DATE' THEN
l_flag := vParamValue.getDate(vGenDate1);
EXECUTE IMMEDIATE vSqlStmnt USING vGenDate1;
WHEN 'SYS.VARCHAR2' THEN               
l_flag := vParamValue.getVarchar2(vGenVar1);
EXECUTE IMMEDIATE vSqlStmnt USING vGenVar1;
END CASE;

Thanks

I am using ANYDATA without problem. I have found an interesting scenario though: attempting to insert an ANYDATA value that contains another ANYDATA value results in the session hanging on the insert (Oracle 10g R2 [Enterprise] on Linux). The session waits show "SQL *Net message from client" and eventually fails with ORA-03113.

Am I missing something? Bug or "feature"?

My reason for attempting this was that I was trying to create a collection of key/value pairs (varchar2/anydata) for use with queue tables and found that this was not working. I simplified the test case to illustrate my problem and removed the AQ stuff. The type I used here only
has varchar2 values and is only there to show that object types can be inserted. I then attempt to insert a ANYDATA object that contains a reference to another ANYDATA object at which point the session crashes:

dayneo@RMSD> create table TEST_ANYDATA_TK
  2  (
  3   id        raw(16) default sys_guid(),
  4   user_data ANYDATA
  5  )
  6  /

Table created.

dayneo@RMSD> create type TEST_TYPE_TK as object
  2  (
  3   value_key   varchar2(32767),
  4   value_value varchar2(32767)
  5  )
  6  /

Type created.

dayneo@RMSD> declare
  2  
  3   l_ud ANYDATA;
  4   l_id raw(16);
  5  
  6  begin
  7  
  8   l_ud := ANYDATA.ConvertVarchar2('hello world');
  9   insert into test_anydata_tk values (sys_guid(), l_ud) returning id into l_id; -- success
 10   dbms_output.put_line(l_id);
 11  
 12  end;
 13  /
661966D705F5F635E040B80A12F05424

PL/SQL procedure successfully completed.

dayneo@RMSD> declare
  2  
  3   l_obj TEST_TYPE_TK;
  4   l_ud  ANYDATA;
  5   l_id  raw(16);
  6  
  7  begin
  8  
  9   l_obj := TEST_TYPE_TK('KEY', 'VALUE');
 10   l_ud  := ANYDATA.ConvertObject(l_obj);
 11   insert into test_anydata_tk values (sys_guid(), l_ud) returning id into l_id; -- success
 12   dbms_output.put_line(l_id);
 13  
 14  end;
 15  /
661966D705F6F635E040B80A12F05424

PL/SQL procedure successfully completed.

dayneo@RMSD> select id, ANYDATA.getTypeName(user_data) from test_anydata_tk
  2  /

ID
--------------------------------
ANYDATA.GETTYPENAME(USER_DATA)
--------------------------------------------------------------------------------
661966D705F5F635E040B80A12F05424
SYS.VARCHAR2

661966D705F6F635E040B80A12F05424
DAYNEO.TEST_TYPE_TK

dayneo@RMSD> declare
  2  
  3   l_vc  ANYDATA;
  4   l_ud  ANYDATA;
  5   l_id  raw(16);
  6  
  7  begin
  8  
  9   l_vc  := ANYDATA.ConvertVarchar2('goodbye cruel world');
 10   l_ud  := ANYDATA.ConvertObject(l_vc);
 11   insert into test_anydata_tk values (sys_guid(), l_ud) returning id into l_id; -- hangs
 12   dbms_output.put_line(l_id);
 13  
 14  end;
 15  /
ERROR:
ORA-03114: not connected to ORACLE

declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

dayneo@RMSD> 

Tom,

Why there is no such functions like

dbms_sql.define_column (c number, POSITION number, column AnyData)
and
dbms_sql.column_value (c number, POSITION number, value AnyData)

They might be very usefull as a "generic" functions among other dozens of alike.

we're using Oracle 11 but the methods don't seem to work. i can do a "sys.anydata.gettypename(my_anydata_var)" but "my_anydata_var.gettypename()" gives me a compile error. i'm thinking we haven't installed something for oracle to recognize these methods?

ops$tkyte%ORA11GR2> create table t ( x sys.anyData );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( sys.anyData.convertNumber(5) );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( sys.anyData.convertDate(sysdate) );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( sys.anyData.convertVarchar2('hello world') );

1 row created.

ops$tkyte%ORA11GR2> select t.x.gettypeName() typeName from t t;

TYPENAME
-------------------------------------------------------------------------------
SYS.NUMBER
SYS.DATE
SYS.VARCHAR2

Here's what I had to do to retrieve the sys.anydata values


function fw_get_sys_anydata
(p_sys_anydata in sys.anydata)
return varchar2 as

v_get_status varchar2(1);
v_date_field date;
v_number_field number;
v_varchar2_field varchar2(4000);
v_return_value varchar2(4000);

begin

case sys.anydata.gettypename(p_sys_anydata)
when 'SYS.DATE'
then v_get_status := p_sys_anydata.getdate(v_date_field);
v_return_value := to_char(v_date_field,'DD-MON-YYYY');
when 'SYS.NUMBER'
then v_get_status := p_sys_anydata.getnumber(v_number_field);
v_return_value := to_char(v_number_field);
when 'SYS.VARCHAR2'
then v_get_status := p_sys_anydata.getvarchar2(v_varchar2_field);
v_return_value := v_varchar2_field;
else
v_return_value := '** unknown **';
end case;

return v_return_value;

end fw_get_sys_anydata;