12 Using PL/SQL Object Types

Overview of PL/SQL Object Types

Before reading this chapter, you should be familiar with some backgroup topics:

Object-oriented programming, including the idea of abstraction.

The ideas of attributes and methods. In other languages, these are part of classes. In SQL and PL/SQL, they are part of object types.

The SQL statement CREATE TYPE.

The best place to read about all of Oracle's object-oriented features is... The remainder of this chapter focuses on issues that are specific to PL/SQL.

What Is an Object Type?

An object type is a user-defined composite datatype representing a data structure and functions and procedures to manipulate the data. With scalar datatypes, each variable holds a single value. With collections, all the elements have the same type. Only object types let you associate code with the data.

The variables within the data structure are called attributes. The functions and procedures of the object type are called methods.

We usually think of an object as having attributes and actions. For example, a baby has the attributes gender, age, and weight, and the actions eat, drink, and sleep. Object types let you represent such real-world behavior in an application.

When you define an object type using the CREATE TYPE statement, you create an abstract template for some real-world object. The template specifies the attributes and behaviors the object needs in the application environment.

Figure 12-1 Each Application Uses a Subset of Object Attributes

Description of the illustration lnpls020.gif

Although the attributes are public (visible to client programs), well-behaved programs manipulate the data only through methodsthat you provide, not by assigning or reading values directly. Because the methods can do extra checking, the data is kept in a proper state.

At run time, you create instances of an abstract type, real objects with filled-in attributes.

Figure 12-2 Object Type and Objects (Instances) of that Type

Description of the illustration lnpls021.gif

Why Use Object Types?

Object types let you break down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable across projects and teams.

By associating code with data, object types move maintenance code out of SQL scripts and PL/SQL blocks into methods. Instead of writing a long procedure that does different things based on some parameter value, you can define different object types and make each operate slightly differently. By declaring an object of the correct type, you ensure that it can only perform the operations for that type.

Object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. Object types map directly into classes defined in object-oriented languages such as Java and C++.

Structure of an Object Type

Like a package, an object type has a specification and a body (refer to Figure 12-3). The specification (or spec for short) defines the programming interface; it declares a set of attributes along with the operations (methods) to manipulate the data. The body defines the code for the methods.

Figure 12-3 Object Type Structure

Description of the illustration lnpls022.gif

All the information a program needs to use the methods is in the spec. You can change the body without changing the spec, and without affecting client programs.

In an object type spec, all attributes must be declared before any methods. If an object type spec declares only attributes, the object type body is unnecessary. You cannot declare attributes in the body. All declarations in the object type spec are public (visible outside the object type).

The following example defines an object type for complex numbers, with a real part, an imaginary part, and arithmetic operations.

CREATE TYPE Complex AS OBJECT ( 
   rpart REAL,  -- "real" attribute
   ipart REAL,  -- "imaginary" attribute
   MEMBER FUNCTION plus (x Complex) RETURN Complex,  -- method
   MEMBER FUNCTION less (x Complex) RETURN Complex,
   MEMBER FUNCTION times (x Complex) RETURN Complex,
   MEMBER FUNCTION divby (x Complex) RETURN Complex
);

CREATE TYPE BODY Complex AS 
   MEMBER FUNCTION plus (x Complex) RETURN Complex IS
   BEGIN
      RETURN Complex(rpart + x.rpart, ipart + x.ipart);
   END plus;

   MEMBER FUNCTION less (x Complex) RETURN Complex IS
   BEGIN
      RETURN Complex(rpart - x.rpart, ipart - x.ipart);
   END less;

   MEMBER FUNCTION times (x Complex) RETURN Complex IS
   BEGIN
      RETURN Complex(rpart * x.rpart - ipart * x.ipart,
                     rpart * x.ipart + ipart * x.rpart);
   END times;

   MEMBER FUNCTION divby (x Complex) RETURN Complex IS
      z REAL := x.rpart**2 + x.ipart**2;
   BEGIN
      RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z,
                     (ipart * x.rpart - rpart * x.ipart) / z);
   END divby;
END;
/

Components of an Object Type

An object type encapsulates data and operations. You can declare attributes and methods in an object type spec, but not constants, exceptions, cursors, or types. You must declare at least one attribute (the maximum is 1000). Methods are optional.

Like a variable, an attribute is declared with a name and datatype. The name must be unique within the object type (but can be reused in other object types). The datatype can be any Oracle type except:

• LONG and LONG RAW

• ROWID and UROWID

• The PL/SQL-specific types BINARY_INTEGER (and its subtypes), BOOLEAN, PLS_INTEGER, RECORD, REF CURSOR, %TYPE, and %ROWTYPE

• Types defined inside a PL/SQL package

You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Also, you cannot impose the NOT NULL constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.

The kind of data structure formed by a set of attributes depends on the real-world object being modeled. For example, to represent a rational number, which has a numerator and a denominator, you need only two INTEGER variables. On the other hand, to represent a college student, you need several VARCHAR2 variables to hold a name, address, phone number, status, and so on, plus a VARRAY variable to hold courses and grades.

The data structure can be very complex. For example, the datatype of an attribute can be another object type (called a nested object type). That lets you build a complex object type from simpler object types. Some object types such as queues, lists, and trees are dynamic, meaning that they can grow as they are used. Recursive object types, which contain direct or indirect references to themselves, allow for highly sophisticated data models.

In general, a method is a subprogram declared in an object type spec using the keyword MEMBER or STATIC. The method cannot have the same name as the object type or any of its attributes. MEMBER methods are invoked on instances, as in

instance_expression.method()

However, STATIC methods are invoked on the object type, not its instances, as in

object_type_name.method()

Like packaged subprograms, methods have two parts: a specification and a body. The specification (spec for short) consists of a method name, an optional parameter list, and, for functions, a return type. The body is the code that executes to perform a specific task.

For each method spec in an object type spec, there must either be a corresponding method body in the object type body, or the method must be declared NOT INSTANTIABLE to indicate that the body is only present in subtypes of this type. To match method specs and bodies, the PL/SQL compiler does a token-by-token comparison of their headers. The headers must match exactly.

Like an attribute, a formal parameter is declared with a name and datatype. However, the datatype of a parameter cannot be size-constrained. The datatype can be any Oracle type except those disallowed for attributes. (See "Attributes".) The same restrictions apply to return types.

CREATE TYPE Complex AS OBJECT ( 
   MEMBER FUNCTION transform (SELF IN OUT Complex) ...

CREATE FUNCTION gcd (x INTEGER, y INTEGER) RETURN INTEGER AS
-- find greatest common divisor of x and y
   ans INTEGER;
BEGIN
   IF (y <= x) AND (x MOD y = 0) THEN ans := y;
   ELSIF x < y THEN ans := gcd(y, x);
   ELSE ans := gcd(y, x MOD y);
   END IF;
   RETURN ans;
END;

CREATE TYPE Rational AS OBJECT (
   num INTEGER,
   den INTEGER,
   MEMBER PROCEDURE normalize,
   ...
);

CREATE TYPE BODY Rational AS 
   MEMBER PROCEDURE normalize IS
      g INTEGER;
   BEGIN
      g := gcd(SELF.num, SELF.den);
      g := gcd(num, den);  -- equivalent to previous statement
      num := num / g;
      den := den / g;
   END normalize;
   ...
END;

CREATE TYPE Rational AS OBJECT ( 
   num INTEGER,
   den INTEGER,
   MAP MEMBER FUNCTION convert RETURN REAL,
);

CREATE TYPE BODY Rational AS 
   MAP MEMBER FUNCTION convert RETURN REAL IS
   BEGIN
      RETURN num / den;
   END convert;
END;

CREATE TYPE Customer AS OBJECT (  
   id   NUMBER, 
   name VARCHAR2(20), 
   addr VARCHAR2(30), 
   ORDER MEMBER FUNCTION match (c Customer) RETURN INTEGER
); 

CREATE TYPE BODY Customer AS 
   ORDER MEMBER FUNCTION match (c Customer) RETURN INTEGER IS 
   BEGIN 
      IF id < c.id THEN
         RETURN -1;  -- any negative number will do
      ELSIF id > c.id THEN 
         RETURN 1;   -- any positive number will do
      ELSE 
         RETURN 0;
      END IF;
   END;
END;

CREATE TYPE Person_typ AS OBJECT
( name CHAR(20),
ssn CHAR(12),
address VARCHAR2(100));
CREATE TYPE Person_nt IS TABLE OF Person_typ;
CREATE TYPE dept_typ AS OBJECT
( mgr Person_typ,
emps Person_nt);
CREATE TABLE dept OF dept_typ;

-- Add new attributes to Person_typ and propagate the change
-- to Person_nt and dept_typ
ALTER TYPE Person_typ ADD ATTRIBUTE (picture BLOB, dob DATE)
CASCADE NOT INCLUDING TABLE DATA;

CREATE TYPE mytype AS OBJECT (attr1 NUMBER, attr2 NUMBER);
ALTER TYPE mytype ADD ATTRIBUTE (attr3 NUMBER),
DROP ATTRIBUTE attr2,
ADD ATTRIBUTE attr4 NUMBER CASCADE;

Defining Object Types

An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list. This section gives several complete examples, which teach you a lot about the design of object types and prepare you to start writing your own.

Currently, you cannot define object types in a PL/SQL block, subprogram, or package. You can define them interactively in SQL*Plus using the SQL statement CREATE TYPE.

-- Create a supertype from which several subtypes will be derived.
CREATE TYPE Person_typ AS OBJECT ( ssn NUMBER, name VARCHAR2(30), address VARCHAR2(100)) NOT FINAL;

-- Derive a subtype that has all the attributes of the supertype,
-- plus some additional attributes.
CREATE TYPE Student_typ UNDER Person_typ ( deptid NUMBER, major VARCHAR2(30)) NOT FINAL;

-- Because Student_typ is declared NOT FINAL, you can derive
-- further subtypes from it.
CREATE TYPE PartTimeStudent_typ UNDER Student_typ( numhours NUMBER);

-- Derive another subtype. Because it has the default attribute
-- FINAL, you cannot use Employee_typ as a supertype and derive
-- subtypes from it.
CREATE TYPE Employee_typ UNDER Person_typ( empid NUMBER, mgr VARCHAR2(30));


-- Define an object type that can be a supertype. Because the
-- member function is FINAL, it cannot be overridden in any
-- subtypes.

CREATE TYPE T AS OBJECT (..., MEMBER PROCEDURE Print(), FINAL MEMBER
FUNCTION foo(x NUMBER)...) NOT FINAL; 

-- We never want to create an object of this supertype. By using
-- NOT INSTANTIABLE, we force all objects to use one of the subtypes
-- instead, with specific implementations for the member functions.
CREATE TYPE Address_typ AS OBJECT(...) NOT INSTANTIABLE NOT FINAL;

-- These subtypes can provide their own implementations of
-- member functions, such as for validating phone numbers and
-- postal codes. Because there is no "generic" way of doing these
-- things, only objects of these subtypes can be instantiated.
CREATE TYPE USAddress_typ UNDER Address_typ(...);
CREATE TYPE IntlAddress_typ UNDER Address_typ(...);

-- Return REFs for those Person_typ objects that are instances of
-- the Student_typ subtype, and NULL REFs otherwise.
SELECT TREAT(REF(p) AS REF Student_typ) FROM Person_v p;

-- Example of using TREAT for assignment...

-- Return REFs for those Person_type objects that are instances of
-- Employee_type or Student_typ, or any of their subtypes.
SELECT REF(p) FROM Person_v P WHERE VALUE(p) IS OF (Employee_typ, Student_typ); 

-- Similar to above, but do not allow any subtypes of Student_typ.
SELECT REF(p) FROM Person_v p WHERE VALUE(p) IS OF(ONLY Student_typ);

-- The results of REF and DEREF can include objects of Person_typ
-- and its subtypes such as Employee_typ and Student_typ.
SELECT REF(p) FROM Person_v p;
SELECT DEREF(REF(p)) FROM Person_v p;

Declaring and Initializing Objects

Once an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result. At run time, instances of the object type are created; that is, objects of that type are instantiated. Each object can hold different values.

Such objects follow the usual scope and instantiation rules. In a block or subprogram, local objects are instantiated when you enter the block or subprogram and cease to exist when you exit. In a package, objects are instantiated when you first reference the package and cease to exist when you end the database session.

DECLARE
   r Rational;
BEGIN
   r := Rational(6, 8);
   dbms_output.put_line(r.num); -- prints 6

DECLARE
   ...
   PROCEDURE open_acct (new_acct IN OUT Account) IS ...

DECLARE
   ...
   FUNCTION get_acct (acct_id IN INTEGER) RETURN Account IS ...

DECLARE
   r Rational;  -- r becomes atomically null
BEGIN
   r := Rational(2,3);  -- r becomes 2/3

DECLARE
   r Rational;
BEGIN
   r Rational := Rational(1,2);  -- r becomes 1/2
   r := NULL;  -- r becomes atomically null
   IF r IS NULL THEN ...  -- condition yields TRUE

DECLARE
   r Rational := Rational(2,3);  -- r becomes 2/3

DECLARE
   r Rational;  -- r is atomically null
BEGIN
   IF r IS NULL THEN ...       -- yields TRUE
   IF r.num IS NULL THEN ...   -- yields TRUE
   r := Rational(NULL, NULL);  -- initializes r
   r.num := 4;  -- succeeds because r is no longer atomically null
                -- even though all its attributes are null
   r := NULL;   -- r becomes atomically null again
   r.num := 4;  -- raises ACCESS_INTO_NULL
EXCEPTION
   WHEN ACCESS_INTO_NULL THEN
      ...
END;

Accessing Object Attributes

You refer to an attribute by name. To access or change the value of an attribute, you use dot notation:

DECLARE
   r Rational := Rational(NULL, NULL);
   numerator   INTEGER;
   denominator INTEGER;
BEGIN
   denominator := r.den; -- Read value of attribute
   r.num := numerator;   -- Assign value to attribute

Attribute names can be chained, which lets you access the attributes of a nested object type. For example, suppose you define object types Address and Student, as follows:

CREATE TYPE Address AS OBJECT ( 
   street   VARCHAR2(30),
   city     VARCHAR2(20),
   state    CHAR(2),
   zip_code VARCHAR2(5)
);

CREATE TYPE Student AS OBJECT ( 
   name         VARCHAR2(20),
   home_address Address,
   phone_number VARCHAR2(10),
   status       VARCHAR2(10),
   advisor_name VARCHAR2(20),
   ...
);

The Address attribute is an object type that has a zip_code attribute. If s is a Student object, you access the value of its zip_code attribute as follows:

s.home_address.zip_code

Defining Object Constructors

By default, you do not need to define a constructor for an object type. The system supplies a default constructor that accepts a parameter corresponding to each attribute.

You might also want to define your own constructor:

• To supply default values for some attributes. You can ensure the values are correct instead of relying on the caller to supply every attribute value.

• To avoid many special-purpose procedures that just initialize different parts of an object.

• To avoid code changes in applications that call the constructor, when new attributes are added to the type. The constructor might need some new code, for example to set the attribute to null, but its signature could remain the same so that existing calls to the constructor would continue to work.

For example:

CREATE OR REPLACE TYPE rectangle AS OBJECT
(
-- The type has 3 attributes.
  length NUMBER,
  width NUMBER,
  area NUMBER,
-- Define a constructor that has only 2 parameters.
  CONSTRUCTOR FUNCTION rectangle(length NUMBER, width NUMBER)
    RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY rectangle AS
  CONSTRUCTOR FUNCTION rectangle(length NUMBER, width NUMBER)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.length := length;
    SELF.width := width;
-- We compute the area rather than accepting it as a parameter.
    SELF.area := length * width;
    RETURN;
  END;
END;
/

DECLARE
  r1 rectangle;
  r2 rectangle;
BEGIN
-- We can still call the default constructor, with all 3 parameters.
  r1 := NEW rectangle(10,20,200);
-- But it is more robust to call our constructor, which computes
-- the AREA attribute. This guarantees that the initial value is OK.
  r2 := NEW rectangle(10,20);
END;
/

Calling Object Constructors

Calls to a constructor are allowed wherever function calls are allowed. Like all functions, a constructor is called as part of an expression, as the following example shows:

DECLARE
   r1 Rational := Rational(2, 3);
   FUNCTION average (x Rational, y Rational) RETURN Rational IS
   BEGIN 
      ... 
   END;
BEGIN
   r1 := average(Rational(3, 4), Rational(7, 11)); 
   IF (Rational(5, 8) > r1) THEN 
      ...
   END IF;
END;

When you pass parameters to a constructor, the call assigns initial values to the attributes of the object being instantiated. When you call the default constructor to fill in all attribute values, you must supply a parameter for every attribute; unlike constants and variables, attributes cannot have default values. As the following example shows, the nth parameter assigns a value to the nth attribute:

DECLARE
   r Rational;
BEGIN
   r := Rational(5, 6);  -- assign 5 to num, 6 to den
   -- now r is 5/6

The next example shows that you can call a constructor using named notation instead of positional notation:

BEGIN
   r := Rational(den => 6, num => 5);  -- assign 5 to num, 6 to den

Calling Object Methods

Like packaged subprograms, methods are called using dot notation. In the following example, you call method normalize(), which divides attributes num and den (for "numerator" and "denominator") by their greatest common divisor:

DECLARE
   r Rational;
BEGIN
   r := Rational(6, 8);
   r.normalize;
   dbms_output.put_line(r.num);  -- prints 3
END;

As the example below shows, you can chain method calls. Execution proceeds from left to right. First, member function reciprocal() is called, then member procedure normalize() is called.

DECLARE
   r Rational := Rational(6, 8);
BEGIN
   r.reciprocal().normalize;
   dbms_output.put_line(r.num);  -- prints 4
END;

In SQL statements, calls to a parameterless method require an empty parameter list. In procedural statements, an empty parameter list is optional unless you chain calls, in which case it is required for all but the last call.

You cannot chain additional method calls to the right of a procedure call because a procedure is called as a statement, not as part of an expression. For example, the following statement is not allowed:

r.normalize().reciprocal;  -- not allowed

Also, if you chain two function calls, the first function must return an object that can be passed to the second function.

For static methods, calls use the notation type_name.method_name rather than specifying an instance of the type.

When you call a method using an instance of a subtype, the actual method that is executed depends on the exact declarations in the type hierarchy. If the subtype overrides the method that it inherits from its supertype, the call uses the subtype's implementation. Or, if the subtype does not override the method, the call uses the supertype's implementation. This capability is known as dynamic method dispatch.

Sharing Objects through the REF Modifier

It is inefficient to pass copies of large objects from subprogram to subprogram. It makes more sense to pass a pointer instead. A ref is a pointer to an object.

Sharing means that data is not replicated unnecessarily. When a shared object is updated, the change occurs in only one place, and any ref can retrieve the updated values instantly.

CREATE TYPE Home AS OBJECT ( 
   address    VARCHAR2(35),
   owner      VARCHAR2(25),
   age        INTEGER,
   style      VARCHAR(15),
   floor plan BLOB,
   price      REAL(9,2),
   ...
);
/
CREATE TABLE homes OF Home;

By revising object type Person, you can model families, where several people share the same home. You use the type modifier REF to declare refs, which hold pointers to objects.

CREATE TYPE Person AS OBJECT ( 
   first_name   VARCHAR2(10),
   last_name    VARCHAR2(15),
   birthday     DATE,
   home_address REF Home,  -- can be shared by family
   phone_number VARCHAR2(15),
   ss_number    INTEGER,
   mother       REF Person, -- family members refer to each other
   father       REF Person, 
   ...
);

Notice how references from persons to homes and between persons model real-world relationships.

You can declare refs as variables, parameters, fields, or attributes. You can use refs as input or output variables in SQL data manipulation statements.

You cannot navigate through refs. Given an expression such as x.attribute, where x is a ref, PL/SQL cannot navigate to the table in which the referenced object is stored. For example, the following assignment is not allowed:

DECLARE 
   p_ref    REF Person; 
   phone_no VARCHAR2(15); 
BEGIN 
   phone_no := p_ref.phone_number;  -- not allowed

You must use the function DEREF or make calls to the package UTL_REF to access the object. For some examples, see "Using Function DEREF".

CREATE TYPE Employee AS OBJECT (
   name VARCHAR2(20),
   dept REF Department,  -- not allowed
   ...
);

CREATE TYPE Department AS OBJECT (
   number  INTEGER,
   manager Employee,
   ...
);

CREATE TYPE Department;  -- forward type definition
   -- at this point, Department is an incomplete object type

CREATE TYPE Customer AS OBJECT (
   id    NUMBER,
   name  VARCHAR2(20),
   addr  Address,  -- not yet defined
   phone VARCHAR2(15)
);

Manipulating Objects through SQL

You can use an object type in the CREATE TABLE statement to specify the datatype of a column. Once the table is created, you can use SQL statements to insert an object, select its attributes, call its methods, and update its state.

Note: Access to remote or distributed objects is not allowed.

In the SQL*Plus script below, the INSERT statement calls the constructor for object type Rational, then inserts the resulting object. The SELECT statement retrieves the value of attribute num. The UPDATE statement calls member method reciprocal(), which returns a Rational value after swapping attributes num and den. Notice that a table alias is required when you reference an attribute or method. (For an explanation, see Appendix D.)

CREATE TABLE numbers (rn Rational, ...)
/
INSERT INTO numbers (rn) VALUES (Rational(3, 62))  -- inserts 3/62
/
SELECT n.rn.num INTO my_num FROM numbers n ...  -- returns 3
/
UPDATE numbers n SET n.rn = n.rn.reciprocal() ...  -- yields 62/3

When you instantiate an object this way, it has no identity outside the database table. However, the object type exists independently of any table, and can be used to create objects in other ways.

In the next example, you create a table that stores objects of type Rational in its rows. Such tables, having rows of objects, are called object tables. Each column in a row corresponds to an attribute of the object type. Rows can have different column values.

CREATE TABLE rational_nums OF Rational;

Each row in an object table has an object identifier, which uniquely identifies the object stored in that row and serves as a reference to the object.

CREATE TYPE Person AS OBJECT ( 
   first_name   VARCHAR2(15),
   last_name    VARCHAR2(15),
   birthday     DATE,
   home_address Address,
   phone_number VARCHAR2(15))
/
CREATE TABLE persons OF Person
/

BEGIN
   INSERT INTO employees  -- another object table of type Person
      SELECT * FROM persons p WHERE p.last_name LIKE '%Smith';

BEGIN
   INSERT INTO employees
      SELECT VALUE(p) FROM persons p 
          WHERE p.last_name LIKE '%Smith';

DECLARE
   p1 Person;
   p2 Person;
   ...
BEGIN
   SELECT VALUE(p) INTO p1 FROM persons p 
      WHERE p.last_name = 'Kroll';
   p2 := p1;
   ...
END;

BEGIN
   p1.last_name := p1.last_name || ' Jr';

BEGIN
   INSERT INTO person_refs
      SELECT REF(p) FROM persons p 
          WHERE p.last_name LIKE '%Smith';

DECLARE
   p_ref       REF Person;
   taxpayer_id VARCHAR2(9);
BEGIN
   SELECT REF(p), p.ss_number INTO p_ref, taxpayer_id 
      FROM persons p
      WHERE p.last_name = 'Parker'; -- must return one row
END;

DECLARE
   p_ref        REF Person;
   my_last_name VARCHAR2(15);
BEGIN
   SELECT REF(p) INTO p_ref FROM persons p
      WHERE p.last_name = my_last_name;
   UPDATE persons p
      SET p = Person('Jill', 'Anders', '11-NOV-67', ...)
      WHERE REF(p) = p_ref;
END;

UPDATE department SET manager = NULL WHERE manager IS DANGLING;

DECLARE
   p1    Person;
   p_ref REF Person;
   name  VARCHAR2(15);
BEGIN
   /* Assume that p_ref holds a valid reference
      to an object stored in an object table. */
   SELECT DEREF(p_ref) INTO p1 FROM dual;
   name := p1.last_name;

CREATE TYPE PersonRef AS OBJECT (p_ref REF Person)
/
DECLARE
   name   VARCHAR2(15);
   pr_ref REF PersonRef;
   pr     PersonRef;
   p      Person;
BEGIN
   /* Assume pr_ref holds a valid reference. */
   SELECT DEREF(pr_ref) INTO pr FROM dual;
   SELECT DEREF(pr.p_ref) INTO p FROM dual;
   name := p.last_name;
END
/

BEGIN
   p1 := DEREF(p_ref);  -- not allowed

table_alias.object_column.ref_attribute
table_alias.object_column.ref_attribute.attribute
table_alias.ref_column.attribute 

CREATE TYPE Address AS OBJECT ( 
   street   VARCHAR2(35),
   city     VARCHAR2(15),
   state    CHAR(2),
   zip_code INTEGER)
/
CREATE TYPE Person AS OBJECT ( 
   first_name   VARCHAR2(15),
   last_name    VARCHAR2(15),
   birthday     DATE,
   home_address REF Address,  -- shared with other Person objects
   phone_number VARCHAR2(15))
/
CREATE TABLE persons OF Person
/

DECLARE
   addr1 Address,
   addr2 Address,
BEGIN
   SELECT DEREF(home_address) INTO addr1 FROM persons p
      WHERE p.last_name = 'Derringer';

DECLARE
   my_street VARCHAR2(25),
BEGIN
   SELECT p.home_address.street INTO my_street FROM persons p
      WHERE p.last_name = 'Lucas';

BEGIN
   INSERT INTO persons 
      VALUES ('Jenifer', 'Lapidus', ...);

BEGIN
   INSERT INTO persons 
      VALUES (Person('Albert', 'Brooker', ...));

DECLARE
   p1_ref REF Person;
   p2_ref REF Person;
BEGIN
   INSERT INTO persons p
      VALUES (Person('Paul', 'Chang', ...))
      RETURNING REF(p) INTO p1_ref;
   INSERT INTO persons p
      VALUES (Person('Ana', 'Thorne', ...))
      RETURNING REF(p) INTO p2_ref;

BEGIN
   INSERT INTO persons2
      SELECT VALUE(p) FROM persons p 
         WHERE p.last_name LIKE '%Jones';

CREATE TABLE department (
   dept_name VARCHAR2(20),
   manager   Person,
   location  VARCHAR2(20))
/
INSERT INTO department 
   VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles')
/

BEGIN
   UPDATE persons p SET p.home_address = '341 Oakdene Ave' 
      WHERE p.last_name = 'Brody';
   UPDATE persons p SET p = Person('Beth', 'Steinberg', ...)
      WHERE p.last_name = 'Steinway';
  END;

BEGIN
   DELETE FROM persons p
      WHERE p.home_address = '108 Palm Dr';
END;