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")
