Java Programming with Oracle JDBC

Object Tables

Now that you’ve seen the object view solution, let’s examine the use of object tables. In this section, we’ll take the four example entities: person, location, person location, and person identifier type, and create object tables to represent those entities as objects. We’ll create the user-defined database types, and the object tables to implement them, in order of the dependence.

To begin, we create an object table corresponding to the person identifier type entity. First, we need to define a type:

create type PERSON_IDENTIFIER_TYPE_typ as object (
code           varchar2(30),
description    varchar2(80),
inactive_date  date )
/

Now that we have the type definition, we create the person_identifier_type_ot object table using the following DDL:

create table PERSON_IDENTIFIER_TYPE_ot of 
             PERSON_IDENTIFIER_TYPE_typ
tablespace USERS pctfree 20
storage (initial 100 K next 100 K pctincrease 0)
/
alter  table PERSON_IDENTIFIER_TYPE_ot add 
constraint   PERSON_IDENTIFIER_TYPE_ot_PK
primary key ( 
code )
using index
tablespace USERS pctfree 20
storage (initial 10 K next 10 K pctincrease 0)
/

Notice that we also created a primary key constraint on the table using the code attribute.

Next, we define the location type:

create type LOCATION_typ as object ( location_id number, parent_location_id number, code varchar2(30), name varchar2(80), start_date date, end_date date, map member function get_map return varchar2, static function get_id return number ); / create type body LOCATION_typ as map member function ...