PL/SQL   «Prev  Next»

Lesson 1

Querying related Object Tables in Oracle

Object references are useful for uniquely identifying and locating an object. Within Oracle, a reference to an object within an object table may be obtained by using the REF pointer.
An object type may be declared as a reference (REF) to another object type.

Module objectives

When you have completed this module, you will be able to:
  1. Describe the Structured Query Language (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 the next lesson, we will begin describing the SQL enhancements that allow you to query related object tables.

Oracle Database 11g Release 1 - New Features in Object-Relational Database

Support for Generalized Invocation

Generalized invocation syntax is now supported. Therefore, a member method in a subtype can statically invoke (dispatch) a member method in any supertype in the supertype hierarchy of the current subtype, including the subtype's immediate supertype.

Generalized Invocation

Generalized invocation provides a mechanism to invoke a method of a supertype or a parent type, rather than the specific subtype member method. Example 2 below demonstrates this using the following syntax:
(SELF AS person_typ).show

The student_typ show method first calls the person_typ show method to do the common actions and then does its own specific action, which is to append '--Major:' to the value returned by the person_typ show method. This way, overriding subtype methods can call corresponding overriding parent type methods to do the common actions before doing their own specific actions.
Methods are invoked just like normal member methods, except that the type name after AS should be the type name of the parent type of the type that the expression evaluates to. In Example 2, there is an implicit SELF argument just like the implicit self argument of a normal member method invocation. In this case, it invokes the person_typ show method rather than the specific student_typ show method.
Example 2: Using Generalized Invocation
myvar student_typ := student_typ(100, 'Sam', '6505556666', 100, 'Math');
name VARCHAR2(100);
name := (myvar AS person_typ).show; --Generalized invocation

Oracle PL/SQL