Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Jul 2, 2007

Using ANYDATA in Oracle 9i

First introduced in Oracle 9i, ANYDATA is a "self-describing data instance type" which means it not only holds the value, but it also holds its own data type within each instance of it. An ANYDATA can be persistently stored in the database. It also contains many methods that allow you to perform various functions.

So how and where do you use it? Suppose you need to design a table with a column which should store any type of data, and you'll only know what kind of data when the application is actually run. In each row, that column can hold any type of data.

In the past, what you would normally do is declare it as VARCHAR2. While inserting, you'd convert each value to VARCHAR2. The problem with this approach is that you wouldn't know what kind of data type is stored. Even if you did know, you'd need to write a conversion for it to convert it into the original value. This is where ANYDATA type comes to the rescue.

These kinds of fields in Oracle 9i can be declared as SYS.ANYDATA. After that, you can use whatever type you want and it will stay that type. For example, if you use date, it stays date and you will be able to perform any date operation on it without any need for conversion.

Here's some examples:


SQL> CREATE TABLE tab1( col1 SYS.ANYDATA );

Table created.

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

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

SQL> INSERT INTO tab1 VALUES(SYS.ANYDATA.convertNumber(1972));
Now you know how to create a table using ANYDATA you know how to put in values.

The next question is how to get back a value. The bad news is that there is no straight-forward method to do so. The good news is that the ANYDATA type provides a bunch of methods to help you perform this function (among others).

For example, by using the following methods, each exposed in the ANYDATA type, you can get the data you desire in its original data type:


gettypeName()
getNumber()
getDate()
getVarchar2()



DECLARE
l_flag NUMBER;
l_v VARCHAR2(100);
l_n NUMBER;
l_d DATE;
l_error VARCHAR2(40);
BEGIN
CASE anydata_type_variable.gettypeName
WHEN SYS.NUMBER THEN
l_flag :=anydata_type_variable.getNumber(l_n);
WHEN SYS.DATE THEN
l_flag :=anydata_type_variable.getDate(l_d);
WHEN SYS.VARCHAR2′ THEN
l_flag :=anydata_type_variable.getVarchar2(l_v);
ELSE
l_error := ** unknown **;
END CASE;
.

Shraddha Pandya
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap