16.2 How do I…Use constructor methods to populate object relational tables?

Problem

I have created some relational tables that contain object columns. The standard SQL INSERT statement fails when I issue it against this type of table. How do I populate relational tables that contain object data types?

Technique

A bit of terminology will enhance understanding of this How-To. A method in Oracle8 is a PL/SQL procedure or function that developers declare as part of the object type declaration. The method operates on the attributes of the object. Oracle8 creates at least one method for every object type even if the developer does not specify any methods as part of the object type declaration. This method is called a constructor method and always has the same name as the object.

When a user issues an object type declaration in Oracle8, as in Step 1 of the previous How-To, Oracle8 only creates a template for the object. An instance of the object does not exist until some user or application stores data in the object’s attributes. When this happens, the object is instantiated.

So, when users want to instantiate an object, they can use a constructor method.

Steps

1. Start SQL*Plus, connect as the WAITE user, and use the START command to load and execute the script CHP16_4.SQL. The script and its output appears in Figure 16.4.

The CREATE TYPE statement creates an object type called ADDRESS_TYPE16 to act as a template for address data. The CREATE TABLE statement creates a table called PERSON16, which uses the ADDRESS_TYPE16 object type.

2. Use the START command to load and execute the script CHP16_5.SQL, which stores a single row in the PERSON16 table. Figure 16.5 contains the script and its output.

In lines 3 through 5 the script invokes the constructor method for object type ADDRESS_TYPE16.

How It Works

Step 1 creates an object type and embeds it in a relational table. Step 2 populates the object relational table by using a standard INSERT statement with the constructor method for the object type created in Step 1.

Comments

Users cannot create their own constructor methods, nor can they create a method with the same name as the object type. Oracle8 reserves the name of the object type for the constructor method, which must be system defined.