PL/SQL   «Prev  Next»

Lesson 7

Object Relational Conclusion

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

Glossary

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.
In the next module, you will learn about nested tables and varrays and techniques to query them.

Effective Management of Data as it ages

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'))
);

Note that if you rely on interval partitioning automatically creating your partitions for you, your application developers cannot rely on consistent partition names because Oracle will create a system-generated name for each partition it automatically creates.

Querying Object Tables Quiz

Click the Quiz link below to take a multiple-choice quiz about the material we’ve covered in this module.
Querying object tables - Quiz