Lesson 3	Inserting a row into an object table
Objective	Write SQL to insert a row into an object table.

Inserting Row into Object Table

In this lesson, you will learn techniques for writing DML statements to insert records into object tables. You will also have an opportunity to write the statements on your own, through the use of an evaluative simulation.
For the purposes of this module, we will insert NULL into the columns for object tables that include varrays or nested tables, because the next module is dedicated to using DML statements on those types of tables.

INSERT statements

Let us begin by looking at the INSERT statements for two basic types of object tables:

Object tables with associated object types
Object tables with references to other objects

Associated object types

For object tables with associated object types, you must mention the name of the object type within the INSERT statement. For example, let's look at CUSTOMER_OBJ_TABLE. The FULL_ADDRESS column is based on the ADDRESS_TYPE object type. An example of an INSERT statement looks like this:

INSERT INTO customer_obj_table 
VALUES (customer_type (26 ,'Lester' ,'Lee' ,
address_type('197 Betty Lane', NULL, 'Hollywood' ,
  'CA' ,'10293'  ),
NULL, '10-JUN-99','HENRY'));

References to other objects

Now let us insert a record into an object table that has a reference to another object. To do this, the INSERT statement must include a SELECT statement, because we must query on the reference of the other object to store that association within this table. For example, the PET_CARE_LOG_OBJ_TABLE table is based on the PET_CARE_OBJ_TYPE object type, which in turn, has a reference to the PRODUCT_TYPE object. This object is maintained within the PRODUCT_OBJ_TABLE table. An example of an INSERT statement looks like the following:

INSERT INTO pet_care_log_obj_table
SELECT REF(p), to_date('15-JAN-00 12:30PM',
  'dd-mon-yy hh:miPM') ,
'JANET', 'New puppy needs shots.', to_date
  ('16-JAN-00 01:30PM','dd-mon-yy hh:miPM')
FROM product_obj_table p
WHERE product_id = 22;

In this example, we query the reference within the PRODUCT_OBJ_TABLE table for a particular PRODUCT_ID. We also include the values for other columns. Notice the use of the table alias for inserting records for reference-based object tables.
Now that you have seen a couple of techniques to insert rows into object tables, apply them through the following evaluative simulation, which requires that you write two INSERT statements.
The first INSERT statement will insert a record into the CUSTOMER_OBJ_TABLE table. The second INSERT statement will insert a record into PET_CARE_LOG_OBJ_TABLE.
Insert Rows Using SqlPlus
With SQL*Plus, connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries. If all three Variations to Insert fields are not filled in, fill in the boxes before clicking OK.

The following SlideShow offers guidelines and examples on variations for inserting records into object tables:

Insert into object tables, which have columns with associated object types. In such case, we must specify the name of the object type and its attributes within the INSERT statement.
Insert into object tables, which are associated with a nested table. In such a case we must select the reference from the other table, that is, the table to which it is referenced. When using this method, values for all the columns within the main table must be provided, even if they are NULL.
Insert into object tables with reference to other objects can be done as a sub-query for the reference column only, then listing the data for the other columns

Object Table Insertion Variations
In the next lesson, we will write an INSERT statement using a sub query.