Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, mathew.

Asked: March 18, 2002 - 10:44 pm UTC

Last updated: January 30, 2013 - 12:37 pm UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

hi,
Oracle9i seems to have a new datatype anydata type. How and where could you use this? If someone puts date values and someone else uses number values in such columns how can this be extracted and interpreted?
thanks,
mathew

and Tom said...

Here is an example. It could be potentially useful in an application that stores generic attributes -- attributes you don't KNOW what the datatypes are until you actually run the code. In the past, we would have stuffed everything into a VARCHAR2 -- dates, numbers, everything. Now, you can put a date in and have it stay as a date (and the system will enforce it is in fact a valid date and let you perform date operations on it -- if it were in a varchar2 -- someone could put "hello world" into your "date" field)




tkyte@TKYTE9I.US.ORACLE.COM> create table t ( x sys.anyData );
Table created.

tkyte@TKYTE9I.US.ORACLE.COM> insert into t values ( sys.anyData.convertNumber(5) );

1 row created.

tkyte@TKYTE9I.US.ORACLE.COM> insert into t values ( sys.anyData.convertDate(sysdate) );

1 row created.

tkyte@TKYTE9I.US.ORACLE.COM> insert into t values ( sys.anyData.convertVarchar2('hello world') );

1 row created.

=================================================================


Use the getTypeName method of the ANYDATA function to see whats
in there....


tkyte@TKYTE9I.US.ORACLE.COM> select t.x.gettypeName() typeName from t t;

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

=================================================================


Unfortunately, they don't have a method to display the contents of
ANYDATA in a query (most useful in programs that will fetch the data,
figure out what it is and do something with it -- eg: the application
has some intelligence as to how to handle the data)

Fortunately we can write one tho:


tkyte@TKYTE9I.US.ORACLE.COM> create or replace function getData( p_x in sys.anyData ) return varchar2
2 as
3 l_num number;
4 l_date date;
5 l_varchar2 varchar2(4000);
6 begin
7 case p_x.gettypeName
8 when 'SYS.NUMBER' then
9 if ( p_x.getNumber( l_num ) = dbms_types.success )
10 then
11 l_varchar2 := l_num;
12 end if;
13 when 'SYS.DATE' then
14 if ( p_x.getDate( l_date ) = dbms_types.success )
15 then
16 l_varchar2 := l_date;
17 end if;
18 when 'SYS.VARCHAR2' then
19 if ( p_x.getVarchar2( l_varchar2 ) = dbms_types.success )
20 then
21 null;
22 end if;
23 else
24 l_varchar2 := '** unknown **';
25 end case;
26
27 return l_varchar2;
28 end;
29 /

Function created.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> select getData( x ) getdata from t;

GETDATA
--------------------
5
19-MAR-02
hello world

tkyte@TKYTE9I.US.ORACLE.COM>

Rating

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

anydata type

Paul Nguyen, March 19, 2002 - 12:26 pm UTC

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



Great!

DV, March 19, 2002 - 1:32 pm UTC

Thanks Tom for the wonderful explaination.

simple explanation

A reader, March 19, 2002 - 10:36 pm UTC


Best way of explanation

Rajender, March 20, 2002 - 12:14 am UTC


more complicated reflection

Mikito harakiri, May 10, 2002 - 3:54 pm UTC

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...




Tom Kyte
May 10, 2002 - 4:37 pm UTC

Not going to happen. Fetch cursor into ..... -- the ..... has to be an EXPLICIT list of variables.

You have to at least KNOW the number of columns -- then you could fetch everything into a varchar2(4000) and everything that can be converted into a varchar2 -- will be.



Too good way of Explanation.

Parag, May 11, 2002 - 4:30 am UTC

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

Enlightening!!!

Ashok, May 13, 2002 - 5:21 am UTC


Sagi, May 13, 2002 - 7:56 am UTC

Hi Tom!

You are simply great with a pool full of knowledge.

Cheers,



Finally the mud settles from the water

Richard Goulet, July 17, 2002 - 2:14 pm UTC

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.

Thanks for concise and useful description.

Paul O'Neill, July 18, 2002 - 6:03 am UTC

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

Oracle 9i Date Data type

Siva, August 13, 2002 - 4:55 am UTC

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

       

Tom Kyte
August 13, 2002 - 4:56 pm UTC

You wrote them and cannot understand them??

But anyway, these questions have nothing to do with the original question -- so, I'll ignore it.

Ask this as a question sometime....

(or get my book "expert one on one" where I spend a good 40 pages on analytic functions and how to use them, how to write them)..



can I query ANYDATA?

mikito harakiri, November 21, 2002 - 9:33 pm UTC

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?


Tom Kyte
November 22, 2002 - 7:16 am UTC

I would be using strings (varchar2's) to store binds -- period. pretty much everything can be cast to a string that you would need.

then, it is queryable as well.

Negative Numbers in VARCHAR2

Jan, May 26, 2003 - 5:37 am UTC

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?



Tom Kyte
May 26, 2003 - 9:43 am UTC

No you don't -- please don't do that, you'll be so sorry you did. Over and over you'll be sorry, or worse yet, you'll move onto a different project and the person that inherits your stuff will be cursing you up and down constantly.


NULLS are very compact, they take at most a single byte! If they are trailing columns on a table, they take ZERO bytes.

store numbers as numbers, dates as dates, strings as strings.

you'll be fast
you'll be correct
you'll be happier
your ancestors will not curse you




Negative Numbers in VARCHAR2 - not just about NULL

Jan, May 26, 2003 - 10:08 am UTC

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




Tom Kyte
May 26, 2003 - 10:45 am UTC

sorry but by going down this generic model, your system is already doomed to be very slow.


forget about conversions -- you would need NO conversions, if you use the specific type.

You can dream up a way to store negative numbers and have them sort "properly" in ASCII -- but everything I said here and above will continue to be true. I know I would never want to pick up your system and have to maintain it.


Think about the positive numbers even -- you have to store them in a fixed width field (else they do NOT sort).

So, you have to figure out, hmm, what is the maximum width this will EVER be. Then make sure to store EACH AND EVERY STRING that is a number in a field of that width (talk about wasted space -- you have to be able to store numbers between 1 and 10000 say -- EACH number will take 6 characters nomimally -- even the number 1 since it must be stored as:

1
^^^^^

in order to compare properly.


Sorry -- I so strongly disagree with your approach that I'm not going to come up with a fancy encoding scheme to make negative numbers "sort" in a string.


I've said it so many times

compare numbers to numbers
dates to dates
strings to strings

and do NOTHING else.




A reader, May 26, 2003 - 3:55 pm UTC

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? ;)

Tom Kyte
May 26, 2003 - 4:08 pm UTC

depends.

my approach behaves like select sysdate from dual would. it uses the sessions default date format to format the date.

Yours imposes your format on everyone.

Dave, May 26, 2003 - 11:44 pm UTC

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.


some new question

Hannibal, November 20, 2003 - 6:38 am UTC

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.

Tom Kyte
November 21, 2003 - 4:26 pm UTC

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>

 

still some unanswered questions

Hannibal, November 24, 2003 - 6:32 am UTC

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.




Tom Kyte
November 24, 2003 - 8:30 am UTC

it is going to be LOB storage and once the LOB gets bigger then about 4,000 bytes -- it goes out of line.

there is no capability beyond what I've shown for "getting the size" (not really sure why you "need" the size)

anydata and clob

Jasbir Kular, May 10, 2006 - 12:50 pm UTC

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?

How to display the contents of a column of ANYDATA data type in SQL

Frank Zhou, September 10, 2007 - 10:56 am UTC

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

Considering using this type to implement a SaaS

Stephane, October 22, 2007 - 11:53 am UTC

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.
Tom Kyte
October 22, 2007 - 1:14 pm UTC

one word for you:

benchmark.


prototype it, test it out, stress it, test it to destruction in a manner you envision using it.

Danger of Anydata

Conor, January 21, 2009 - 7:19 am UTC

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 Kyte
January 21, 2009 - 1:14 pm UTC

correct, but if you are going to "where on it", then you know what it is already - and a generic type like this would not be considered....

But yes, I agree, use these things as little as possible - generic sounds so cool, so good - but it'll burn you almost every time.

Generic Code using ANYDATA

Steve, February 26, 2009 - 3:08 pm UTC

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
Tom Kyte
March 03, 2009 - 8:14 am UTC

yeah, I wouldn't go there.

Pass all as strings using a commented format (commented by you), eg: dates are yyyymmddhh24miss, numbers are 999999999.9999 or whatever.


Storage of ANYDATA inside ANYDATA

DayneO, March 27, 2009 - 8:43 am UTC

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 Kyte
March 30, 2009 - 3:39 pm UTC

if you get an ora-600, ora-7445, ora-3113 - it is a bug. Something "crashed"


whether the code "should be valid" or not - it is a bug. It might be a bug that the code was permitted to execute, or it might be a bug that the code failed during execution - but either way - it is not correct behavior.

please contact support with your very simple (nice) test case.

DBMS_SQL and AnyData

Renat, October 22, 2010 - 9:02 am UTC

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.

methods not working

dan murphy, January 23, 2013 - 10:49 am UTC

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?
Tom Kyte
January 30, 2013 - 12:37 pm UTC

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



please post example with error messages and everything. show me how to reproduce or at least what you are seeing yourself.

methods not working but did get it to work

dan murphy, January 23, 2013 - 2:39 pm UTC

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;

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library