In the case of object tables that have references to other object tables, the INSERT statement is based on a query to the referencing object.
For example, within the SALE_HEADER table, the CUST_REF column is associated with CUSTOMER_TYPE, which is maintained within CUSTOMER_OBJ_TABLE. To insert a record into the SALE_HEADER table, you must query CUSTOMER_OBJ_TABLE for values within the CUST_REF column. An example of such an INSERT statement is:
INSERT INTO sale_header
500,(SELECT REF(C) FROM customer_obj_table c WHERE
cust_id = 1),89.45, 3.58, 15, 108.03, '01-AUG-00',
In this example, the INSERT statement has a sub query, which retrieves the reference information from the CUSTOMER_OBJ_TABLE table that is
maintained within the CUST_REF column in the SALE_HEADER table.
Let us look at another example that inserts a record into the PET_CARE_LOG_OBJ_TABLE table. The PRODUCT_ID column has a reference to PRODUCT_OBJ_TABLE. One example of an INSERT statement for inserting a record into this table looks like the following:
INSERT INTO pet_care_log_obj_table VALUES(
(SELECT REF(p) FROM product_obj_table p
WHERE product_id = 34),
to_date('05-JAN-00 12:30PM','dd-mon-yy hh:miPM'),
'JANET', 'New puppy needs vaccination.’,
to_date('16-JAN-00 01:30PM','dd-mon-yy hh:miPM')
This example is just another way of inserting into the PET_CARE_LOG_OBJ_TABLE table. Here, we have a query to select a reference for the PRODUCT_ID column from the PRODUCT_OBJ_TABLE table.
This example differs from the example in the previous lesson, because this query is for getting the reference information only.
Take a look at the MouseOvers below for the syntax and an example of inserting records into object tables by using a sub query: