Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

12 Using PL/SQL Object Types


... It next will be right
To describe each particular batch:
Distinguishing those that have feathers, and bite,
From those that have whiskers, and scratch. —Lewis Carroll

Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database. You might find object types helpful when writing programs that interoperate with Java or other object-oriented languages.

This chapter contains these topics:

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 lnpls020.gif follows
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 lnpls021.gif follows
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 lnpls022.gif follows
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.


Attributes

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:

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.


Methods

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.

What Languages can I Use for Methods of Object Types?

Oracle lets you implement object methods in PL/SQL, Java or C. You can implement type methods in Java or C by providing a call specification in your type. A call spec publishes a Java method or external C function in the Oracle data dictionary. It publishes the routine by mapping its name, parameter types, and return type to their SQL counterparts. To learn how to write Java call specs, see Oracle Database Java Developer's Guide. To learn how to write C call specs, see Oracle Database Application Developer's Guide - Fundamentals.

How Object Types Handle the SELF Parameter

MEMBER methods accept a built-in parameter named SELF, which is an instance of the object type. It is always the first parameter passed to a MEMBER method. If you do not declare it, it declared automatically.

For example, the transform method declares SELF as an IN OUT parameter:

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

You must specify the same datatype for SELF as the original object.

In MEMBER functions, if SELF is not declared, its parameter mode defaults to IN.

In MEMBER procedures, if SELF is not declared, its parameter mode defaults to IN OUT.

You cannot specify the OUT parameter mode for SELF.

STATIC methods cannot accept or reference SELF.

In the method body, SELF denotes the object whose method was invoked. You can refer to SELF.attribute_name or SELF.member_name, to make clear that you are referring to that object rather than something in a supertype. As the following example shows, methods can reference the attributes of SELF without a qualifier:

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;

From a SQL statement, if you call a MEMBER method on a null instance (that is, SELF is null), the method is not invoked and a null is returned. From a procedural statement, if you call a MEMBER method on a null instance, PL/SQL raises the predefined exception SELF_IS_NULL before the method is invoked.

Overloading

Like packaged subprograms, methods of the same kind (functions or procedures) can be overloaded. You can use the same name for different methods if their formal parameters are different enough to tell apart. When you call one of the methods, PL/SQL finds it by comparing the actual parameters with each list of formal parameters.

A subtype can also overload methods it inherits from its supertype. In this case, the methods can have exactly the same formal parameters.

You cannot overload two methods whose formal parameters differ only in their mode. You cannot overload two member functions that differ only in return type. For more information, see "Overloading Subprogram Names".


MAP and ORDER Methods

Instances of an object type have no predefined order. To put them in order for comparisons or sorting, PL/SQL calls a MAP method supplied by you. In the following example, the keyword MAP indicates that method convert() orders Rational objects by mapping them to REAL values:

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;

PL/SQL uses the map method to evaluate Boolean expressions such as x > y, and to do comparisons implied by the DISTINCT, GROUP BY, and ORDER BY clauses. MAP method convert() returns the relative position of an object in the ordering of all Rational objects.

An object type can contain only one MAP method. It accepts the built-in parameter SELF and returns one of the following scalar types: DATE, NUMBER, VARCHAR2, or an ANSI SQL type such as CHARACTER or REAL.

Alternatively, you can supply PL/SQL with an ORDER method. An object type can contain only one ORDER method, which must be a function that returns a numeric result. In the following example, the keyword ORDER indicates that method match() compares two objects:

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;

Every ORDER method takes two parameters: the built-in parameter SELF and another object of the same type. If c1 and c2 are Customer objects, a comparison such as c1 > c2 calls match() automatically. The method returns a negative number, zero, or a positive number signifying that SELF is less than, equal to, or greater than the other parameter. If either parameter passed to an ORDER method is null, the method returns a null.


Guidelines

A MAP method, acting like a hash function, maps object values into scalar values, which are then compared using operators such as <, =, and so on. An ORDER method simply compares one object value to another.

You can declare a MAP method or an ORDER method but not both. If you declare either method, you can compare objects in SQL and procedural statements. If you declare neither method, you can compare objects only in SQL statements and only for equality or inequality. (Two objects of the same type are equal only if the values of their corresponding attributes are equal.)

When sorting or merging a large number of objects, use a MAP method. One call maps all the objects into scalars, then sorts the scalars. An ORDER method is less efficient because it must be called repeatedly (it can compare only two objects at a time). You must use a MAP method for hash joins because PL/SQL hashes on the object value.


Constructor Methods

Every object type has a constructor method, a function with the same name as the object type that initializes and returns a new instance of that object type.

Oracle generates a default constructor for every object type, with formal parameters that match the order, names, and datatypes of the object attributes.

You can define your own constructor methods, either overriding a system-defined constructor, or defining a new function with a different signature.

PL/SQL never calls a constructor implicitly. You must call it explicitly.

For more information, see "Defining Object Constructors".

Changing Attributes and Methods of an Existing Object Type (Type Evolution)

You can use the ALTER TYPE statement to add, modify, or drop attributes, and add or drop methods of an existing object type:

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;

When a procedure is compiled, it always uses the current version of any object types it references. Existing procedures on the server that reference an object type are invalidated when the type is altered, and are automatically recompiled the next time the procedure is called. You must manually recompile any procedures on the client side that reference types that are altered.

If you drop a method from a supertype, you might have to make changes to subtypes that override that method. You can find if any subtypes are affected by using the CASCADE option of ALTER TYPE; the statement is rolled back if any subtypes override the method. To successfully drop the method from the supertype, you can:

  • Drop the method permanently from the subtype first.

  • Drop the method in the subtype, then add it back later using ALTER TYPE without the OVERRIDING keyword.

For more information about the ALTER TYPE statement, see Oracle Database SQL Reference. For guidelines about using type evolution in your applications, and options for changing other types and data that rely on those types, see Oracle Database Application Developer's Guide - Object-Relational Features.

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.

Overview of PL/SQL Type Inheritance

PL/SQL supports a single-inheritance model. You can define subtypes of object types. These subtypes contain all the attributes and methods of the parent type (or supertype). The subtypes can also contain additional attributes and additional methods, and can override methods from the supertype.

You can define whether or not subtypes can be derived from a particular type. You can also define types and methods that cannot be instantiated directly, only by declaring subtypes that instantiate them.

Some of the type properties can be changed dynamically with the ALTER TYPE statement. When changes are made to the supertype, either through ALTER TYPE or by redefining the supertype, the subtypes automatically reflect those changes.

You can use the TREAT operator to return only those objects that are of a specified subtype.

The values from the REF and DEREF functions can represent either the declared type of the table or view, or one or more of its subtypes.

See the Oracle Database Application Developer's Guide - Object-Relational Features for more detail on all these object-relational features.

Examples of PL/SQL Type Inheritance

-- 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.

Declaring Objects

You can use object types wherever built-in types such as CHAR or NUMBER can be used. In the block below, you declare object r of type Rational. Then, you call the constructor for object type Rational to initialize the object. The call assigns the values 6 and 8 to attributes num and den, respectively.

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

You can declare objects as the formal parameters of functions and procedures. That way, you can pass objects to stored subprograms and from one subprogram to another. In the next example, you use object type Account to specify the datatype of a formal parameter:

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

In the following example, you use object type Account to specify the return type of a function:

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

Initializing Objects

Until you initialize an object by calling the constructor for its object type, the object is atomically null. That is, the object itself is null, not just its attributes. Consider the following example:

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

A null object is never equal to another object. In fact, comparing a null object with any other object always yields NULL. Also, if you assign an atomically null object to another object, the other object becomes atomically null (and must be reinitialized). Likewise, if you assign the non-value NULL to an object, the object becomes atomically null, as the following example shows:

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

A good programming practice is to initialize an object in its declaration, as shown in the following example:

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

How PL/SQL Treats Uninitialized Objects

In an expression, attributes of an uninitialized object evaluate to NULL. Trying to assign values to attributes of an uninitialized object raises the predefined exception ACCESS_INTO_NULL. When applied to an uninitialized object or its attributes, the IS NULL comparison operator yields TRUE.

The following example illustrates the difference between null objects and objects with null attributes:

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;

Calls to methods of an uninitialized object raise the predefined exception NULL_SELF_DISPATCH. When passed as arguments to IN parameters, attributes of an uninitialized object evaluate to NULL. When passed as arguments to OUT or IN OUT parameters, they raise an exception if you try to write to them.

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:

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".

Forward Type Definitions

You can refer only to schema objects that already exist. In the following example, the first CREATE TYPE statement is not allowed because it refers to object type Department, which does not yet exist:

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

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

Switching the CREATE TYPE statements does not help because the object types are mutually dependent. Object type Employee has an attribute that refers to object type Department, and object type Department has an attribute of type Employee. To solve this problem, you use a special CREATE TYPE statement called a forward type definition, which lets you define mutually dependent object types.

To debug the last example, simply precede it with the following statement:

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

The object type created by a forward type definition is called an incomplete object type because (until it is defined fully) it has no attributes or methods.

An impure incomplete object type has attributes but causes compilation errors because it refers to an undefined type. For example, the following CREATE TYPE statement causes an error because object type Address is undefined:

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

This lets you defer the definition of object type Address. The incomplete type Customer can be made available to other application developers for use in refs.

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.

Selecting Objects

Assume that you have run the following SQL*Plus script, which creates object type Person and object table persons, and that you have populated the table:

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
/

The following subquery produces a result set of rows containing only the attributes of Person objects:

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

To return a result set of objects, you must use the function VALUE, which is discussed in the next section.

Using Function VALUE

As you might expect, the function VALUE returns the value of an object. VALUE takes as its argument a correlation variable. (In this context, a correlation variable is a row variable or table alias associated with a row in an object table.) For example, to return a result set of Person objects, use VALUE as follows:

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

In the next example, you use VALUE to return a specific Person object:

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

At this point, p1 holds a local Person object, which is a copy of the stored object whose last name is 'Kroll', and p2 holds another local Person object, which is a copy of p1. As the following example shows, you can use these variables to access and update the objects they hold:

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

Now, the local Person object held by p1 has the last name 'Kroll Jr'.

Using Function REF

You can retrieve refs using the function REF, which, like VALUE, takes as its argument a correlation variable. In the following example, you retrieve one or more refs to Person objects, then insert the refs into table person_refs:

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

The next example retrieves a ref and attribute at the same time:

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;

This example, updates the attributes of a Person object:

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;

Testing for Dangling Refs

If the object to which a ref points is deleted, the ref is left dangling, pointing to a nonexistent object. To test for this condition, you can use the SQL predicate IS DANGLING. For example, suppose column manager in relational table department holds refs to Employee objects stored in an object table. You can use the following UPDATE statement to convert any dangling refs into nulls:

UPDATE department SET manager = NULL WHERE manager IS DANGLING;

Using Function DEREF

You cannot navigate through refs within PL/SQL procedural statements. Instead, you must use the function DEREF in a SQL statement to dereference a pointer, and get the value to which it points. DEREF takes a reference to an object, and returns the value of that object. If the ref is dangling, DEREF returns a null object.

The following example dereferences a ref to a Person object. You can select from the dummy table DUAL because each object stored in an object table has a unique object identifier, which is part of every ref to that object.

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;

You can use DEREF in successive SQL statements to dereference refs:

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
/

The next example shows that you cannot use function DEREF within procedural statements:

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

Within SQL statements, you can use dot notation to navigate through object columns to ref attributes and through one ref attribute to another. You can also navigate through ref columns to attributes by using a table alias. For example, the following syntax is valid:

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

Assume that you have run the following SQL*Plus script, which creates object types Address and Person and object table persons:

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
/

Ref attribute home_address corresponds to a column in object table persons that holds refs to Address objects stored in some other table. After populating the tables, you can select a particular address by dereferencing its ref:

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

In the example below, you navigate through ref column home_address to attribute street. In this case, a table alias is required.

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

Inserting Objects

You use the INSERT statement to add objects to an object table. In the following example, you insert a Person object into object table persons:

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

Alternatively, you can use the constructor for object type Person to insert an object into object table persons:

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

In the next example, you use the RETURNING clause to store Person refs in local variables. Notice how the clause mimics a SELECT statement.You can also use the RETURNING clause in UPDATE and DELETE statements.

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;

To insert objects into an object table, you can use a subquery that returns objects of the same type. An example follows:

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

The rows copied to object table persons2 are given new object identifiers. No object identifiers are copied from object table persons.

The script below creates a relational table named department, which has a column of type Person, then inserts a row into the table. Notice how constructor Person() provides a value for column manager.

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

The new Person object stored in column manager cannot be referenced because it is stored in a column (not a row) and therefore has no object identifier.

Updating Objects

To modify the attributes of objects in an object table, you use the UPDATE statement, as the following example shows:

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;

Deleting Objects

You use the DELETE statement to remove objects (rows) from an object table. To remove objects selectively, use the WHERE clause:

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