Lesson 7

You now have an even more in-depth understanding of the object-relational approach within Oracle8i. Now that you have completed this module, you should be able to:

1. Describe the SQL enhancements that allow you to query related object tables
2. Detect dangling references
3. Write a query using a DEREF data type to retrieve related data
4. Determine when PL/SQL is appropriate or required for querying object tables
5. Write a PL/SQL block to retrieve data from an object table

In this module, you were introduced to the following glossary terms:

1. Correlation argument: A correlation argument is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation argument.
2. Correlation variable: A correlation variable is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation variable.
3. Pre-delete trigger: A pre-delete trigger is a trigger which is fired before a record is deleted from a table.

As time goes by, you will need to be able to age related data from multiple tables in a consistent fashion. You will need to be able to remove data without worrying that there are dangling references that will invalidate referential integrity along the way. By using reference partitions, you are unifying the ways in which the data is divided across multiple tables, and that significantly eases the maintenance burden. Another new partition type available with Oracle 11g is the interval partition. In interval partitioning, you do not specify the specific range values for each partition; instead, you specify the duration of the interval. That is, instead of specifying that partition 1 ends on January 31 and partition 2 ends on February 29, you specify that each partition is one month long. When a new row is inserted, Oracle will determine which partition to place the row in based on the interval definition. If you have not created a partition for that month, the database will automatically create a new one. Use interval partitions with caution. You will need to perform the data value constraint checks yourself prior to inserting the row, if you do not, you may end up with unwanted partitions that were created simply because a data-entry person mistakenly entered "2098" for a year instead of "2008".
The interval partitioned version of the INVOICE_HEADERS table is shown in the following listing:

create table INVOICE_HEADERS
(
InvoiceNum number,
CustomerNum number,
Invoice_Date date
)
partition by range (Invoice_Date)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values
less than (to_date('2007-02-01','yyyy-mm-dd'))
);

Click the Quiz link below to take a multiple-choice quiz about the material we have covered in this module.
Querying Object Tables - Quiz
In the next module, you will learn about nested tables and varrays and techniques to query them.