|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
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. A special kind of method called the constructor creates a new instance of the object type and fills in its attributes.
Object types must be created through SQL and stored in an Oracle database, where they can be shared by many programs. When you define an object type using the
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.
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, so that the data is kept in a proper state.
For information on using object types, see Chapter 12, " Using PL/SQL Object Types".
Keyword and Parameter Description
Any Oracle datatype except
UROWID, the PL/SQL-specific types
BINARY_INTEGER (and its subtypes),
%ROWTYPE, and types defined inside a PL/SQL package.
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. You cannot impose the
NULL constraint on an attribute.
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 "Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)".
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 Oracle Database Application Developer's Guide - Fundamentals.
STATIC function. For the syntax of
function_body, see "Functions".
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
Declares a subprogram or call spec as a method in an object type spec. A constructor method must have the same name as the object type, while member and static methods must have names that are different from the object type or any of its attributes.
MEMBER methods are invoked on instances of objects, and read or change the attributes of that particular instance:
CONSTRUCTOR methods create new instances of objects, and fill in some or all of the attributes:
object_instance := new object_type_name(attr1 => attr1_value, attr2 => attr2_value);
The system defines a default constructor method with one parameter for each object attribute, so you only need to define your own constructor methods if you want to construct the object based on a different set of parameters.
STATIC methods are invoked on the object type, not any specific object instance, and thus must limit themselves to "global" operations that do not involve the object attributes:
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. The headers must match word for word. Differences in whitespace are allowed.
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
CREATE TYPE Complex AS OBJECT ( MEMBER FUNCTION transform (SELF IN OUT Complex) ...
You cannot specify a different datatype for
SELF. In constructor functions, SELF always has the parameter mode
IN OUT. In
MEMBER functions, if
SELF is not declared, its parameter mode defaults to
MEMBER procedures, if
SELF is not declared, its parameter mode defaults to
OUT. You cannot specify the
OUT parameter mode for
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.
RESTRICT_REFERENCES, which checks 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 or write database tables or package variables. For more information about the purity rules and pragma
RESTRICT_REFERENCES, see Oracle Database Application Developer's Guide - Fundamentals.
The schema containing the object type. If you omit
schema_name, Oracle assumes the object type is in your schema.
Declares the interface to a
STATIC function or procedure. Its syntax is like that of
procedure_spec without the terminator. See "Functions" and/or "Procedures".
A user-defined object type that was defined using the datatype specifier
Once an object type is created in your 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 object attribute, table column, PL/SQL variable, bind variable, record field, collection element, formal procedure 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. 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. 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 default 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. You can also define your own constructor methods that accept different sets of parameters. PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.
This SQL*Plus script defines an object type for a stack. 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:
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, );