18 December 2006

Power Of Object Oriented Mechanism With Object Views In Oracle

In Oracle database, it is possible to create virtual object tables from relational tables. With this fashion you can select as if you are selecting an object table. So, you need not to convert relational data to object oriented data if you tend to use it. Follow the example:

Connected to Oracle Database 10g Express Edition Release
Connected as hr

SQL> create type t_telephone_number is object( no varchar2(12));
  2  /

Type created
SQL> create type t_telephone_number_list is varray(5) of t_telephone_number;
  2  /

Type created
SQL> create type worker_typ is object( id number, name varchar2(16), telephone_list t_telephone_number_list );
  2  /

Type created
SQL> alter type worker_typ add attribute x date;
Type altered
SQL> describe worker_typ;
Element        Type                   
-------------- -----------------------
ID             NUMBER                 
NAME           VARCHAR2(16)           
X              DATE                   

SQL> alter type worker_typ drop attribute x ;
Type altered
SQL> create table workers(i number, n varchar2(16) );
Table created
SQL> create table tels(i number, nm varchar2(12) );
Table created
SQL> create view vw_workers of worker_typ with object identifier(id) as
  2    select w.i, w.n,cast(
  3       multiset(select nm from tels t where t.i=w.i) as t_telephone_number_list)
  4     from workers w;

View created
SQL> insert into workers values(1,'Mennan');
1 row inserted
SQL> insert into tels values(1, '2122122122');
1 row inserted
SQL> insert into tels values(1, '2122221133');
1 row inserted
SQL> select v.id,v.name, v.telephone_list from vw_workers v where v.id = 1;
        ID NAME             TELEPHONE_LIST
---------- ---------------- --------------
         1 Mennan           [object]

No comments: