|PL/SQL User's Guide and Reference
Part Number A89856-01
PL/SQL Language Elements, 32 of 52
An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods.
Currently, you cannot define object types within PL/SQL. They must be
CREATEd and stored in an Oracle database, where they can be shared by many programs. When you define an object type (in SQL*Plus for example) using the
TYPE statement, you create an abstract template for some real-world object. The template specifies only those attributes and behaviors the object will need in the application environment.
The data structure formed by the set of attributes is public (visible to client programs). However, well-behaved programs do not manipulate it directly. Instead, they use the set of methods provided. That way, the data is kept in a proper state. At run time, when the data structure is filled with values, you have created an instance of an object type. You can create as many instances (usually called objects) as you need. For more information, see Chapter 10.
This is any Oracle datatype except
UROWID, the PL/SQL-specific types
BINARY_INTEGER (and its subtypes),
%ROWTYPE, and types defined inside a PL/SQL package.
This identifies an object attribute. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or
DEFAULT clause. Also, you cannot impose the
NULL constraint on an attribute.
This determines whether all member methods execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. For more information, see "Invoker Rights Versus Definer Rights".
This 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 Oracle9i Java Stored Procedures Developer's Guide. To learn how to write C call specs Oracle9i Application Developer's Guide - Fundamentals.
This defines the underlying implementation of a
MEMBER function. For the syntax of
function_body, see "Functions".
This keyword indicates that a method orders objects by mapping them to values of a scalar datatype such as
REAL, which have a predefined order. PL/SQL uses the ordering to evaluate Boolean expressions such as
x > y, and to do comparisons implied by the
BY clauses. A map method returns the relative position of an object in the ordering of all such objects.
An object type can contain only one map method, which must be a parameterless function having the return type
VARCHAR2, or an ANSI SQL type such as
This keyword lets you declare a subprogram or call spec as a method in an object type spec. The method cannot have the same name as the object type or any of its attributes.
MEMBER methods are invoked on instances, as in
STATIC methods are invoked on the object type, not its instances, as in
For each subprogram spec in an object type spec, there must be a corresponding subprogram body in the object type body. To match specs and bodies, the compiler does a token-by-token comparison of their headers. So, the headers must match word for word.
MEMBER methods accept a built-in parameter named
SELF, which is an instance of the object type. Whether declared implicitly or explicitly, it is always the first parameter passed to a
MEMBER method. However,
STATIC methods cannot accept or reference
In the method body,
SELF denotes the object whose method was invoked. For example, method
SELF as an
You cannot specify a different datatype for
MEMBER functions, if
SELF is not declared, its parameter mode defaults to
IN. However, in
MEMBER procedures, if
SELF is not declared, its parameter mode defaults to
OUT. You cannot specify the
OUT parameter mode for
This keyword indicates that a method compares two objects. An object type can contain only one order method, which must be a function that returns a numeric result.
Every order method takes just two parameters: the built-in parameter
SELF and another object of the same type. If
Customer objects, a comparison such as
c1 > c2 calls method
match automatically. The method returns a negative number, zero, or a positive number signifying that
SELF is respectively 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.
This is pragma
RESTRICT_REFERENCES, which lets you check for violations of "purity" rules. To be callable from SQL statements, a member function must obey those rules, which are meant to control side effects. If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). For the syntax of the pragma, see "RESTRICT_REFERENCES Pragma" (in this context, omit the pragma terminator).
The pragma asserts that a member function does not read and/or write database tables and/or package variables. For more information about the purity rules and pragma
RESTRICT_REFERENCES, see Oracle9i Application Developer's Guide - Fundamentals.
This qualifier identifies the schema containing the object type. If you omit
schema_name, Oracle assumes the object type is in your schema.
This defines the underlying implementation of a
STATIC function or procedure. Its syntax is like that of
procedure_body without the terminator. See "Functions" and/or "Procedures".
This identifies a user-defined object type that was defined using the datatype specifier
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.
Like a package, an object type has two parts: a specification and a body. The specification (spec for short) is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body fully defines the methods, and so implements the spec.
All the information a client program needs to use the methods is in the spec. Think of the spec as an operational interface and of the body as a black box. You can debug, enhance, or replace the body without changing the spec.
An object type encapsulates data and operations. So, you can declare attributes and methods in an object type spec, but not constants, exceptions, cursors, or types. At least one attribute is required (the maximum is 1000); methods are optional.
In an object type spec, all attributes must be declared before any methods. Only subprograms have an underlying implementation. So, if an object type spec declares only attributes and/or call specs, 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).
You can refer to an attribute only by name (not by its position in the object type). To access or change the value of an attribute, you use dot notation. Attribute names can be chained, which lets you access the attributes of a nested object type.
In an object type, methods can reference attributes and other methods without a qualifier. 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.
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.
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. However, 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.
Like packaged subprograms, methods of the same kind (functions or procedures) can be overloaded. That is, you can use the same name for different methods if their formal parameters differ in number, order, or datatype family.
Every object type has a constructor method (constructor for short), which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type. PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.
In the SQL*Plus script below, an object type for a stack is defined. The last item added to a stack is the first item removed. The operations push and pop update the stack while preserving last in, first out (LIFO) behavior. The simplest implementation of a stack uses an integer array. Integers are stored in array elements, with one end of the array representing the top of the stack.
CREATE TYPE IntArray AS VARRAY(25) OF INTEGER; CREATE TYPE Stack AS OBJECT ( max_size INTEGER, top INTEGER, position IntArray, MEMBER PROCEDURE initialize, MEMBER FUNCTION full RETURN BOOLEAN, MEMBER FUNCTION empty RETURN BOOLEAN, MEMBER PROCEDURE push (n IN INTEGER), MEMBER PROCEDURE pop (n OUT INTEGER) ); CREATE TYPE BODY Stack AS MEMBER PROCEDURE initialize IS -- fill stack with nulls BEGIN top := 0; -- call constructor for varray and set element 1 to NULL position := IntArray(NULL); max_size := position.LIMIT; -- use size constraint (25) position.EXTEND(max_size - 1, 1); -- copy element 1 END initialize; MEMBER FUNCTION full RETURN BOOLEAN IS -- return TRUE if stack is full BEGIN RETURN (top = max_size); END full; MEMBER FUNCTION empty RETURN BOOLEAN IS -- return TRUE if stack is empty BEGIN RETURN (top = 0); END empty; MEMBER PROCEDURE push (n IN INTEGER) IS -- push integer onto stack BEGIN IF NOT full THEN top := top + 1; position(top) := n; ELSE -- stack is full RAISE_APPLICATION_ERROR(-20101, 'stack overflow'); END IF; END push; MEMBER PROCEDURE pop (n OUT INTEGER) IS -- pop integer off stack and return its value BEGIN IF NOT empty THEN n := position(top); top := top - 1; ELSE -- stack is empty RAISE_APPLICATION_ERROR(-20102, 'stack underflow'); END IF; END pop; END;
pop, the built-in procedure
raise_application_error issues user-defined error messages. That way, you can report errors to the client program and avoid returning unhandled exceptions to the host environment. In an object type, methods can reference attributes and other methods without a qualifier, as the following example shows:
CREATE TYPE Stack AS OBJECT ( top INTEGER, MEMBER FUNCTION full RETURN BOOLEAN, MEMBER PROCEDURE push (n IN INTEGER), ... ); CREATE TYPE BODY Stack AS ... MEMBER PROCEDURE push (n IN INTEGER) IS BEGIN IF NOT full THEN top := top + 1; ... END push; END;
The following example shows that you can nest object types:
CREATE TYPE Address AS OBJECT ( street_address 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 Address, -- nested object type phone_number VARCHAR2(15), ss_number INTEGER, );
Functions, Packages, Procedures