Using the OraRef Interface

This section covers the creation of object table named PERSON_TAB. This object table is based on the object type PERSONOBJ. Each reference to the rows of this object table is stored in an aperson REF type column of CUSTOMERS table. The following code creates database schemas.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

set hrDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)

HRDb.ExecuteSQL("create type PERSONOBJ as object ( name

varchar2(20), age number, addr ADDRESS)")

HRDb.ExecuteSQL("create table person_tab of personobj")

HRDb.ExecuteSQL("insert into person_tab values('nasser',40,

address('Wine Blvd', 'Pleasanton', 'CA', '94065'))")

HRDb.ExecuteSQL("insert into person_tab values('Maha', 25,

address('Continental Way', 'Belmont', 'CA', '94002'))")

HRDb.ExecuteSQL("insert into person_tab values('chris',30,

address('First Street', 'San Francisco', 'CA' , '94123'))")

The following code creates a CUSTOMERS table having an aperson REF column referencing rows of the object table

HRDb.ExecuteSQL("create table CUSTOMERS (account number,

aperson REF personobj)")

HRDb.ExecuteSQL("insert into customers values(10, null)")

HRDb.ExecuteSQL("insert into customers values(20, null)")

HRDb.ExecuteSQL("insert into customers values(30, null)")

HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from

person_tab p where p.name = 'nasser') where account = 10")

HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from

person_tab p where p.name = 'Maha') where account = 20")

HRDb.ExecuteSQL("update customers set aperson = (select ref(p) from

person_tab p where p.name = 'chris') where account = 30")