CREATE TABLE pets OF Pet_t
(PRIMARY KEY (tag_no))
NESTED TABLE vaccinations STORE AS
pet_vaccinations_tab;
Using this separate persons table and the REF attribute will allow the existence of people outside the
context of their pets (something the pet-obsessed may not envision, but probably a good idea from a
design point of view). In this context, REF is called a type modifier.
Does a REF sound a lot like a foreign key? While there are important differences between REFs and
foreign keys (see
Table 18.2), Oracle actually claims that REFs, are "more reliable and persistent"
than foreign keys -- probably because REFs do not refer to user-changeable values, but rather to
invisible internal values.
In fact, the problem with REFs is that they are too persistent. Oracle currently allows you to delete an
object that is the target of a REF without deleting the reference to it. They even dignify this state with
a name: a dangling REF. This is roughly equivalent to what would happen if you delete a department
record without changing the records of employees in that department. There is no declarative way to
prevent dangling REFs, but it should not be too challenging to do so by implementing pre-delete
triggers on the table that contains the "parent" objects.[
11] To make life somewhat easier, Oracle
provides a predicate, IS DANGLING, to test for this condition:
[11] It is also possible to use a foreign key in combination with a REF. To do so, you
would include an attribute for the foreign key in the Pet_t specification and include a
FOREIGN KEY clause in the CREATE TABLE statement.
UPDATE pets
SET owner_ref = NULL
WHERE owner_ref IS DANGLING;
Table 18.2: Chief Differences between Foreign Keys and REFs
Characteristic Foreign Key REF
Who defines the value used as the
"pointer?"
User (programmer) System

if children exist?
Yes No
Can cascade deletions of the parent
to child (objects)?
Yes, with ON DELETE
CASCADE
No
Default type of relationship
between parent and child when
joined via SQL
Equi-join Outer join (when using
dot navigation)
Parent and child can be on
different databases?
No; must be enforced with table-
level triggers
Not in Oracle 8.0.3
NOTE: In
Table 18.2, we use the terminology "parent" and "child" only for
convenience; these terms are not always accurate descriptions of objects linked via
REFs.
Oracle has a special syntax for retrieving and modifying data in both SQL and PL/SQL using the
REF operator; they also provide a DEREF operator (can you guess why?). We'll look at those
operators a bit later.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 18.1 Introduction
to Oracle8 Objects
Oracle PL/SQL
Programming, 2nd Edition
Next: 18.3 Syntax for

The default constructor, supplied automatically when you create an object type, allows you to create
an object of the corresponding type. You have no direct control over this function (aside from how
you have defined the attributes of the object type). The constructor is the only type of method that
does not operate on an existing object.
Comparison methods are either MAP or ORDER methods (see
Section 18.3.6, "Comparing Objects"
later in this chapter). They allow you to establish rules so that SQL statements and PL/SQL programs
can order, group, and otherwise compare object instances. Comparison methods are always functions.
Member methods are either member functions or member procedures. These are where programmers
define the bulk of the object's behavior.
18.3.2 CREATE TYPE and DROP TYPE: Creating and Dropping Types
The CREATE TYPE statement has the following general format:
CREATE [ OR REPLACE ] TYPE <type name> AS OBJECT
<attribute name> datatype, ...,
MEMBER PROCEDURE | FUNCTION <procedure or function
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
spec>, ...,
[ MAP | ORDER MEMBER FUNCTION <comparison function
spec>, ... ]
[ PRAGMA RESTRICT_REFERENCES (<what to restrict>,
restrictions) ]
);
As you would expect, you can drop a type using a DROP statement as follows:
DROP TYPE <type name> [ FORCE ] ;
Parameters have the following meanings:
OR REPLACE
Tells Oracle that you want to rebuild the type if it should happen to already exist. This will
preserve grants. (See "Schema Evolution" later in the chapter for information about the effect
this option has on the object type's metadata.)
type name

For example:
CREATE TABLE foos OF Foo_t
(bar NOT NULL);
or, if you wish to name a constraint:
CREATE TABLE foos OF Foo_t
(CONSTRAINT bar_not_null CHECK (bar IS NOT NULL));
18.3.3 CREATE TYPE BODY: Creating a Body
The syntax for the CREATE TYPE BODY statement is the following:
CREATE [ OR REPLACE ] TYPE BODY <type name> AS | IS (
MEMBER PROCEDURE | FUNCTION <procedure or function
body>, ...,
[ MAP | ORDER MEMBER FUNCTION <comparison function
body> ]
END;
Strictly speaking, type bodies are optional; you need a body only if you have created any methods in
the specification. Similar to the rules for package specifications and bodies, the methods declared in
the specification must match one for one the methods implemented in the body. Methods can be
overloaded (see
Chapter 15), and the standard rules about overloading apply.
18.3.4 Dot Notation
Even if you don't use the object extensions to Oracle, dot notation can be confusing. In SQL, for
example, you may have references such as basil.meals.calories, referring to a column called calories
in a meals table owned by basil. Add in remote database references, and you might get something like
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In PL/SQL Version 2 and up, dots are found in record
datatypes, table datatype operators, packaged procedure or function references, and elsewhere.
In the objects option, there are at least two new opportunities to get confused with dots: object data
structures and object methods. (And the discussion below ignores the fact that object names can be
preceded by the schema name, as in schema_name.object_name.)
18.3.4.1 Dots in data structures

attributes and member functions that are defined with the appropriate RESTRICT_REFERENCES
pragma. But what do we do in PL/SQL? Is this legal?
DECLARE
the_dalmatian Pet_t;
BEGIN
SELECT VALUE(p) INTO the_dalmatian
FROM pets p
WHERE name = 'Cerberus';...
IF the_dalmatian.owner_ref.first_name = 'Persephone'
-- invalid
THEN...
It won't work! In Oracle 8.0.3, you cannot navigate the database through PL/SQL REF variables.
Repeat this to yourself like a mantra. Dot notation doesn't help us in this case. For now, you can
instead use DEREF, described in detail later on; a future version of Oracle will likely include a built-
in package called UTL_REF that supports navigation in PL/SQL.
18.3.4.2 Dots in method invocations
When you invoke an object's member function or procedure, the dot syntax is straightforward, as in
the following:
object_instance_name.function_name (args)
object_instance_name.procedure_name (args)
If you want to use the output from one method as the input to another, you don't have to use a
temporary variable. You can actually chain methods together with dots, as long as they are type
compatible:
object_name.function_name(args).function_name(args).
procedure_name(args)
Before we can take a look at an example that chains our Pet_t methods, we'll want to change the
specification of print_me. Instead of using the default IN OUT mode of the SELF parameter in a
member procedure, we are going to make it an IN. That is, instead of:
MEMBER PROCEDURE print_me
we want to use:

Be sure that you don't try to use a function's return value (which is read-only) as an IN OUT
input to the next method in the chain.
18.3.4.3 Attribute or method?
In PL/SQL, there is no automatic visual distinction between an object attribute and an object method
unless the method has arguments. That is, in this code fragment:
IF my_pet.whatever = 'a value' THEN...
we can't immediately determine if "whatever" is an attribute or a method! In some cases, this
ambiguity could be a feature, since one day we might want to replace an attribute by a method of the
same name.
If we want to make our code less mysterious, we can add a trailing empty parameter list to method
calls which have no parameters, as in the following:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
my_pet.print_me();
The empty parentheses notation works for both member functions and member procedures.
NOTE: The situation is different in SQL statements. If you call a member function
without parameters in a SQL statement, you must use empty parentheses notation. That
is, if somefun is a function, don't do this:
SELECT p.somefun FROM pets p; -- invalid
The statement above fails with an ORA-00904, "invalid column name." The correct
syntax is:
SELECT p.somefun() FROM pets p;
18.3.5 SELF: The Implied Parameter
Because a method can only be called within the context of a particular object instance, it always has
an object of the corresponding type as a "parameter." This makes sense because the method will
(almost) always need access to that object's attributes. This implied parameter is called SELF. By
default, SELF is an IN parameter in member functions, and an IN OUT parameter in member
procedures.
If we create an object to hold American Kennel Club papers:
CREATE TYPE Akc_paper_t AS OBJECT(
pet_ref REF Pet_t,

issued_on);
NOTE: Including SELF explicitly can improve program clarity.
18.3.5.1 Forward type definitions
What would you do if you wanted to define object types that depend on each other? Suppose that we
want to implement the following relationships:
●
Each pet has an owner of type Person_t; owners can have one or more pets.
●
A person can have one and only one favorite pet.
The solution is a forward type definition, similar to forward declarations in PL/SQL packages (see
Chapter 16). A forward definition allows you to declare your intention to create a type before you
actually define it:
/* Here is the incomplete type definition */
CREATE TYPE Person_t;
/* Now owner_ref can make a "forward" reference to the
|| Person_t type
*/
CREATE TYPE Pet_t AS OBJECT (
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
tag_no INTEGER,
owner_ref REF Person_t,
...the rest of the attributes and methods...
);
/* Now we can complete the type definition we started
|| earlier.
*/
CREATE TYPE Person_t AS OBJECT (
name VARCHAR2(512),
favorite_pet REF Pet_t,
...

Let's say that we have created an object type Appointment_t that will help us in scheduling visits to
the veterinary offices of Relative Pets. We might need an application to compare appointments:
DECLARE
my_appointment Appointment_t;
your_appointment Appointment_t;
BEGIN
...initialize the appointments...
IF my_appointment > your_appointment THEN ...
To perform this greater-than comparison, you'll need to define either a MAP or an ORDER function.
MAP and ORDER methods are mutually exclusive; a given object type may have exactly one MAP
method, or exactly one ORDER method (or zero comparison methods of either type).
18.3.6.1.1 MAP member functions
The MAP method simply translates or "maps" each object into a scalar datatype space that Oracle
knows how to compare. For example, suppose we had a simple rule that says appointments are
"greater than" others if they occur later in time. Then the MAP method is trivial:
CREATE TYPE Appointment_t AS OBJECT (
pet REF Pet_t,
scheduled_date DATE,
with_whom REF Doctor_t,
MAP MEMBER FUNCTION compare RETURN DATE
);
CREATE TYPE BODY Appointment_t
AS
MAP MEMBER FUNCTION compare RETURN DATE
IS
BEGIN
RETURN scheduled_date;
END compare;
END;
MAP functions accept no parameters and must return a date, character, or number -- that is,

IS
-- A very lame attempt at comparing geographic
locations
BEGIN
IF the_location.latitude = SELF.latitude
AND the_location.longitude = SELF.longitude
AND the_location.altitude = SELF.altitude THEN
RETURN 0;
ELSIF SELF.latitude > the_location.latitude
OR SELF.longitude > the_location.longitude
OR SELF.altitude > the_location.altitude THEN
RETURN 1;
ELSE
RETURN -1;
END IF;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
END;
END;
This ORDER member function will allow us to make simple comparisons such as:
IF location1 > location2 THEN
plant_a_flag;
END IF:
Although not recommended, your ORDER method can return NULL under certain situations, and the
object comparison itself will evaluate to NULL. That is, if our object type body were rewritten as
follows:
CREATE TYPE BODY Location_t
AS
ORDER MEMBER FUNCTION compare (the_location IN
Location_t)
RETURN INTEGER

●
A MAP method is particularly appropriate if you are sequencing a large series of objects,
while an ORDER method is more useful if you are comparing two objects.
18.3.6.2 Equality comparisons
If you don't create a MAP or ORDER method, Oracle allows you to test only for equality of two
different objects. Two Oracle objects are "equal" if and only if they (1) are of the same object type;
and (2) both have attributes with identical values. Object attributes get compared one at a time, in
order, and the testing stops when the first mismatch is discovered.
Here is an example of testing for equality:
DECLARE
the_1997_spec Marketing_spec_t;
the_1998_spec Marketing_spec_t;
BEGIN
...
IF the_1997_spec = the_1998_spec THEN ...
Or, if we had one table of marketing specs per year:
CREATE TABLE marketing_1997 OF Marketing_spec_t;
CREATE TABLE marketing_1998 OF Marketing_spec_t;
then we could compare from within SQL by using the VALUE operator:
SELECT s97.make, s97.model
FROM marketing_1997 s97,
marketing_1998 s98
WHERE VALUE(s97) = VALUE(s98);
NOTE: Default equality comparisons work only if the object table contains attributes
that Oracle knows how to compare. For example, they will work on objects with scalar
attributes, but they will not work on objects with collection attributes, embedded object
types, REFs, or LOBs. Also, if you create a MAP or ORDER member function, you
override Oracle's ability to perform the default equality test by comparing all the
attributes.
18.3.7 Privileges

methods. As with all new technology, we will simply have to wait and see whether such a change
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
comes about, and if it does, what sort of impact it will have on existing applications.
Previous: 18.2 Oracle
Objects Example
Oracle PL/SQL
Programming, 2nd Edition
Next: 18.4 Manipulating
Objects in PL/SQL and
SQL
18.2 Oracle Objects Example
Book Index
18.4 Manipulating Objects in
PL/SQL and SQL
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 18.3 Syntax for
Creating Object Types
Chapter 18
Object Types
Next: 18.5 Modifying
Persistent Objects

18.4 Manipulating Objects in PL/SQL and SQL
In this section we look more deeply into the constructs and concepts you will need in order to master
to use objects in your applications. There are three different ways you can initialize an object:
●

In the example below, notice that the object itself is null, as well as the object's attributes:
DECLARE
cerberus_house Address_t; -- cerberus_house is not
initialized here
BEGIN
IF cerberus_house IS NULL ... -- will evaluate
to TRUE
IF cerberus_house.street IS NULL... -- also TRUE
The nullity of the elements in PL/SQL follows somewhat unpredictable rules; uninitialized RECORD
variables have null elements (as with objects), but uninitialized collections have elements whose
nullity is not defined. As with collections, when an object is null, you cannot simply assign values to
its attributes; if you do, PL/SQL will raise an exception. Before assigning values to the attributes, you
must initialize the entire object.
Let's turn now to the three different ways a PL/SQL program can initialize an object.
18.4.1.1 Constructors
A constructor is a special method that allows the creation of an object from an object type. Invoking a
constructor is a way to instantiate (create) an object. In Oracle 8.0, each object has a single default
constructor that the programmer cannot alter or supplement.
The default constructor:
●
Has the same name as the object type
●
Is a function rather than a procedure
●
Accepts attributes in named or positional notation
●
Returns an object
●
Must be called with a value, or the non-value NULL, for every attribute; there is no
DEFAULT clause for object attributes