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:

Describe the Structured Query Language (SQL) enhancements that allow you to query related object tables
Detect dangling references
Write a query using a DEREF data type to retrieve related data
Determine when PL/SQL is appropriate or required for querying object tables
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.

Object References used in Oracle Database 11g

Object references are used in Oracle Database 11g to create and manage relationships between objects in the database. In Oracle Database 11g, an object reference is a pointer or reference to an object stored in the database. These references can be used to link objects together, allowing them to be accessed and manipulated as a group. For example, a table may contain a column that references a row in another table. This reference allows the two tables to be linked together and enables queries and operations that involve both tables. Object references can also be used to create hierarchical relationships between objects, such as parent-child relationships or tree structures. Object references are created using the REF data type, which stores a pointer to an object in the database. The object being referenced must have a unique identifier, which is used to retrieve the object using the REF pointer. Oracle Database 11g provides a number of features for working with object references, including support for querying and manipulating objects using SQL, PL/SQL, and Java, and the ability to define and enforce referential integrity constraints between objects.

Oracle Database 11g Release 1 - 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

DECLARE
myvar student_typ := student_typ(100, 'Sam', '6505556666', 100, 'Math');
name VARCHAR2(100);
BEGIN
name := (myvar AS person_typ).show; --Generalized invocation
END;
/

Ad Oracle PL/SQL