ANYDATA Example - Take a walk on the wild side!

View All Scripts Login to Run Script

Script Name ANYDATA Example - Take a walk on the wild side!
Description An ANYDATA TYPE contains an instance of a given type, plus a description of the type. In this sense, an ANYDATA is self-describing. An ANYDATA can be persistently stored in the database. As Tom Kyte says, "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)". "Doc: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_anydat.htm#BEHEICHI
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday December 16, 2015

Statement 1

CREATE TYPE pet_t IS OBJECT ( 
   tag_no   INTEGER 
  ,NAME     VARCHAR2 (60) 
  ,breed    VARCHAR2 (100) 
)

Type created.

Statement 2

Nested Table of Object Types

CREATE TYPE zoo_t IS TABLE OF pet_t

Type created.

Statement 3
Tables don't get more generic than this. Store some stuff in a row that is identified by a primary key.
A Walk on the Wild Side
```
CREATE TABLE wild_side (  
   ID NUMBER PRIMARY KEY  
 , DATA SYS.ANYDATA  
) 
```
```
Table created.
```

Statement 4

I use methods of the ANYDATA object type to convert various types of data into an ANYDATA container, which is then inserted into the table.

Stuff Just About Anything Into That Table!

DECLARE  
   my_cat    pet_t  
          := pet_t (50, 'Sister', 'Siamese');  
   my_bird   pet_t  
      := pet_t (100  
               ,'Mercury'  
               ,'African Grey Parrot'  
               );  
   my_pets   zoo_t := zoo_t (my_cat, my_bird);  
BEGIN  
   INSERT INTO wild_side  
        VALUES (1  
               ,SYS.ANYDATA.convertnumber  
                                         (5));  
  
   INSERT INTO wild_side  
        VALUES (3  
               ,SYS.ANYDATA.convertvarchar2  
                      ('Pretty crazy stuff!'));  
  
   INSERT INTO wild_side  
        VALUES (5  
               ,SYS.ANYDATA.convertdate  
                                    (SYSDATE));  
  
   INSERT INTO wild_side  
        VALUES (2  
               ,SYS.ANYDATA.convertobject  
                                    (my_bird));  
  
   INSERT INTO wild_side  
        VALUES (7  
               ,SYS.ANYDATA.convertcollection  
                                    (my_pets));  
  
   COMMIT;  
END;

1 row(s) inserted.

Statement 5

SELECT ID, ws.DATA.gettypename () thetype 
  FROM wild_side ws


ID THETYPE
1 SYS.NUMBER
3 SYS.VARCHAR2
5 SYS.DATE
2 SQL_RHMNFZMBVXMXUDWPPFNBLLUVE.PET_T
7 SQL_RHMNFZMBVXMXUDWPPFNBLLUVE.ZOO_T

5 rows selected.

ID	THETYPE
1	SYS.NUMBER
3	SYS.VARCHAR2
5	SYS.DATE
2	SQL_RHMNFZMBVXMXUDWPPFNBLLUVE.PET_T
7	SQL_RHMNFZMBVXMXUDWPPFNBLLUVE.ZOO_T

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database