Using Object Types to Define Column Objects and Object Tables

You can use an object type to define a column in a table, and the column is known as a column object . Also, when an object type contains an embedded object type, that embedded object type is also a column object. An example of this is person_typ , which contains an embedded address_typ column object named address .

The following example creates a table named products that contains a column object of product_typ . Notice that this table also contains a NUMBER column named quantity_in_stock , which is used to store the number of those products currently in stock:

 CREATE TABLE products (product product_typ,  quantity_in_stock NUMBER); 

You can also use an object type to define an entire table, and the table is known as an object table . The following examples create two object tables, named object_products and object_ customers, which are defined using product_typ and person_typ , respectively. Notice the use of the OF clause to identify each table as an object table:

 CREATE TABLE object_products OF product_typ; CREATE TABLE object_customers OF person_typ; 

One difference between a table containing a column object and an object table is that the former can have more than one column. For example, I added the additional quantity_in_stock column to the products table.

Object References and Object Identifiers

Another difference of object tables is that you use object references to model relationships between object tables, rather than foreign keys. Object references are defined using the REF type and are basically pointers to objects in an object table. Each object in an object table has a unique object identifier (OID) that you can then store in a REF column. The following example creates a table named purchases that contains two REF columns :

 CREATE TABLE purchases (id NUMBER PRIMARY KEY,  customer REF person_typ SCOPE IS object_customers,  product REF product_typ SCOPE IS object_products); 

The SCOPE IS clause restricts the object reference to point to objects in a specific table. For example, the customer column is restricted to point to objects in the object_customers table; similarly, the product column is restricted to point to objects in the object_products table.

In the following sections, you ll learn how to perform SQL DML operations on the products , object_products , object_customers , and purchases tables.