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()DECLAREl_flag NUMBER;l_v VARCHAR2(100);l_n NUMBER;l_d DATE;l_error VARCHAR2(40);BEGINCASE anydata_type_variable.gettypeNameWHEN SYS.NUMBER THENl_flag :=anydata_type_variable.getNumber(l_n);WHEN SYS.DATE THENl_flag :=anydata_type_variable.getDate(l_d);WHEN SYS.VARCHAR2? THENl_flag :=anydata_type_variable.getVarchar2(l_v);ELSEl_error := ** unknown **;END CASE;.
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS