100. OBJECT VIEWS WITH REFERENCES

To implement the objects and the ref constraints to the existing tables, what we can do? Simply drop the both tables and recreate with objects and ref constrains.

But you can achieve this with out dropping the tables and without losing the data by creating object views with references.

Ex:

     a) Create the following tables

          SQL> Create table student1(no number(2) primary key,name varchar(2),marks

                  number(3));

          SQL> Create table student2(no number(2) primary key,hno number(3),city

                  varchar(10),id number(2),foreign Key(id) references student1(no));

     b) Insert the records into both tables

          SQL> insert into student1(1,’a’,100);

          SQL> insert into student1(2,’b’,200);

          SQL> insert into student2(11,111,’hyd’,1);

          SQL> insert into student2(12,222,’bang’,2);

          SQL> insert into student2(13,333,’bombay’,1);

     c) Create the type

         SQL> create or replace type stud as object(no number(2),name varchar(2),marks

                 number(3));/

     d) Generating OIDs

          SQL> Create or replace view student1_ov of stud with object identifier(or id) (no) as

                  Select * from Student1;

     e) Generating references

          SQL> Create or replace view student2_ov as select no,hno,city,

                  make_ref(student1_ov,id) id from Student2;

     d) Query the following

          SQL> select *from student1_ov;

          SQL> select ref(s) from student1_ov s;

          SQL> select values(s) from student1_ov;

          SQ> select *from student2_ov;

          SQL> select deref(s.id) from student2_ov s;