This chapter describes how to use object types with PL/SQL
This chapter contains these topics:
Using object types in a PL/SQL block, subprogram, or package is a two-step process.
You must define object types using the SQL statement CREATE TYPE, in SQL*Plus or other similar programs.
See Also:
"About Object Types"After an object type is defined and installed in the schema, you can use it in any PL/SQL block, subprogram, or package.
In PL/SQL, you then declare a variable whose data type is the user-defined type or ADT that you just defined.
Objects or ADTs follow the usual scope and instantiation rules.
Example 3-1 provides two object types, and a table of object types. Subsequent examples show how to declare variables of those object types in PL/SQL and perform other operations with these objects.
Example 3-1 Working With Object Types
CREATE TYPE address_typ AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
postal_code VARCHAR2(6) );
/
CREATE TYPE employee_typ AS OBJECT (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
address address_typ,
MAP MEMBER FUNCTION get_idno RETURN NUMBER,
MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) );
/
CREATE TYPE BODY employee_typ AS
MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
BEGIN
RETURN employee_id;
END;
MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(first_name || ' ' || last_name);
DBMS_OUTPUT.PUT_LINE(address.street);
DBMS_OUTPUT.PUT_LINE(address.city || ', ' || address.state || ' ' ||
address.postal_code);
END;
END;
/
CREATE TABLE employee_tab OF employee_typ;
You can use objects or ADTs wherever built-in types such as CHAR or NUMBER can be used.
Example 3-2 declares object emp of type employee_typ. Then, the constructor for object type employee_typ initializes the object.
Example 3-2 Declaring Objects in a PL/SQL Block
-- Requires Ex. 3-1
DECLARE
emp employee_typ; -- emp is atomically null
BEGIN
-- call the constructor for employee_typ
emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
'415.555.0100', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,
address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details
emp.display_address(); -- call object method to display details
END;
/
The formal parameter of a PL/SQL subprogram can have data types of user-defined types. Therefore, you can pass objects to stored subprograms and from one subprogram to another. In the next example, the object type employee_typ specifies the data type of a formal parameter:
PROCEDURE open_acct (new_acct IN OUT employee_typ) IS ...
In the following example, object type employee_typ specifies the return type of a function:
FUNCTION get_acct (acct_id IN NUMBER) RETURN employee_typ IS ...
User-defined types, just like collections, are atomically null, until you initialize the object by calling the constructor for its object type. That is, the object itself is null, not just its attributes.
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.
In an expression, attributes of an uninitialized object evaluate to NULL. When applied to an uninitialized object or its attributes, the IS NULL comparison operator yields TRUE.
See Example 2-1 for an illustration of null objects and objects with null attributes.
This section describes how to manipulate object attributes and methods in PL/SQL.
This section includes the following topics:
You refer to an attribute by name. 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.
Example 3-3 uses dot notation and generates the same output as Example 3-2.
Example 3-3 Accessing Object Attributes
-- Requires Ex. 3-1
DECLARE
emp employee_typ;
BEGIN
emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
'415.555.0100', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,
address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name);
DBMS_OUTPUT.PUT_LINE(emp.address.street);
DBMS_OUTPUT.PUT_LINE(emp.address.city || ', ' ||emp. address.state || ' ' ||
emp.address.postal_code);
END;
/
Calls to a constructor are allowed wherever function calls are allowed. Like all functions, a constructor is called as part of an expression, as shown in Example 3-3 and Example 3-4.
Example 3-4 Inserting Rows in an Object Table
-- Requires Ex. 3-1
DECLARE
emp employee_typ;
BEGIN
INSERT INTO employee_tab VALUES (employee_typ(310, 'Evers', 'Boston', 'EBOSTON',
'617.555.0100', '01-AUG-04', 'SA_REP', 9000, .15, 101, 110,
address_typ('123 Main', 'San Francisco', 'CA', '94111')) );
INSERT INTO employee_tab VALUES (employee_typ(320, 'Martha', 'Dunn', 'MDUNN',
'650.555.0150', '30-SEP-04', 'AC_MGR', 12500, 0, 101, 110,
address_typ('123 Broadway', 'Redwood City', 'CA', '94065')) );
END;
/
SELECT VALUE(e) from employee_tab e;
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. You can call a constructor using named notation instead of positional notation.
Like packaged subprograms, methods are called using dot notation. In Example 3-5, the display_address method is called to display attributes of an object. Note the use of the VALUE function which 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.
Example 3-5 Accessing Object Methods
-- Requires Ex. 3-1 and Ex. 3-4
DECLARE
emp employee_typ;
BEGIN
SELECT VALUE(e) INTO emp FROM employee_tab e WHERE e.employee_id = 310;
emp.display_address();
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. Also, if you chain two function calls, the first function must return an object that can be passed to the second function.
If a PL/SQL function is used in place of an ADT constructor during a DML operation, the function may execute multiple times as part of the DML execution. For the function to execute only once per occurrence, it must be a deterministic 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 declarations in the type hierarchy. If the subtype overrides the method that it inherits from its supertype, the call uses the subtype implementation. Otherwise, the call uses the supertype implementation. This capability is known as dynamic method dispatch.
See Also:
"Dynamic Method Dispatch"From inside a PL/SQL block you can modify and delete rows in an object table.
Example 3-6 Updating and Deleting Rows in an Object Table
-- Requires Ex. 3-1 and 3-4
DECLARE
emp employee_typ;
BEGIN
INSERT INTO employee_tab VALUES (employee_typ(370, 'Robert', 'Myers', 'RMYERS',
'415.555.0150', '07-NOV-04', 'SA_REP', 8800, .12, 101, 110,
address_typ('540 Fillmore', 'San Francisco', 'CA', '94011')) );
UPDATE employee_tab e SET e.address.street = '1040 California'
WHERE e.employee_id = 370;
DELETE FROM employee_tab e WHERE e.employee_id = 310;
END;
/
SELECT VALUE(e) from employee_tab e;
You can retrieve REFs using the function REF, which takes as its argument a correlation variable or alias.
Example 3-7 Updating Rows in an Object Table With a REF Modifier
-- Requires Ex. 3-1, 3-4, and 3-6 DECLARE emp employee_typ; emp_ref REF employee_typ; BEGIN SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.employee_id = 370; UPDATE employee_tab e SET e.address = address_typ('8701 College', 'Oakland', 'CA', '94321') WHERE REF(e) = emp_ref; END; /
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 in PL/SQL. For example, the assignment in Example 3-8 using a REF is not allowed. Instead, use the function DEREF or make calls to the package UTL_REF to access the object. For information on the REF function, see Oracle Database SQL Language Reference.
Example 3-8 Trying to Use DEREF in a SELECT INTO Statement, Incorrect
-- Requires Ex. 3-1, 3-4, and 3-6 DECLARE emp employee_typ; emp_ref REF employee_typ; emp_name VARCHAR2(50); BEGIN SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.employee_id = 370; -- the following assignment raises an error, not allowed in PL/SQL emp := DEREF(emp_ref); -- cannot use DEREF in procedural statements emp_name := emp.first_name || ' ' || emp.last_name; DBMS_OUTPUT.PUT_LINE(emp_name); END; /
This assignment raises an error as described below:
not allowed in PL/SQL -- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name; -- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
For detailed information on the DEREF function, see Oracle Database SQL Language Reference.
Overloading allows you to substitute a subtype value for a formal parameter that is a supertype. This capability is known as substitutability. This section is about this aspect of overloading.
If more than one instance of an overloaded procedure matches the procedure call, the following substitution rules determine which procedure, if any, is called:
If the signatures of the overloaded procedures only differ in that some parameters are object types from the same supertype-subtype hierarchy, the closest match is used. The closest match is one where all the parameters are at least as close as any other overloaded instance, as determined by the depth of inheritance between the subtype and supertype, and at least one parameter is closer.
If instances of two overloaded methods match, and some argument types are closer in one overloaded procedure while others are closer in the second procedure, a semantic error occurs.
If some parameters differ in their position within the object type hierarchy, and other parameters are of different data types so that an implicit conversion would be necessary, then a semantic error occurs.
Example 3-9 creates a type hierarchy that has three levels starting with super_t. There is a package with two overloaded instances of a function that are the same except for the position of the argument type in the type hierarchy. The invocation declares a variable of type final_t, and then calls the overloaded function.
The instance of the function that executes is the one that accepts a sub_t parameter, because sub_t is closer to final_t than super_t in the hierarchy. This follows the rules of substitution.
Note that because determining which instance to call happens at compile time, the fact that the argument passed in was also a final_t is ignored. If the declaration was v super_t := final_t(1,2,3), the overloaded function with the argument super_t would be called.
Example 3-9 Resolving PL/SQL Functions With Inheritance
CREATE OR REPLACE TYPE super_t AS OBJECT (n NUMBER) NOT final; / CREATE OR REPLACE TYPE sub_t UNDER super_t (n2 NUMBER) NOT final; / CREATE OR REPLACE TYPE final_t UNDER sub_t (n3 NUMBER); / CREATE OR REPLACE PACKAGE p IS FUNCTION func (arg super_t) RETURN NUMBER; FUNCTION func (arg sub_t) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY p IS FUNCTION func (arg super_t) RETURN NUMBER IS BEGIN RETURN 1; END; FUNCTION func (arg sub_t) RETURN NUMBER IS BEGIN RETURN 2; END; END; / DECLARE v final_t := final_t(1,2,3); BEGIN DBMS_OUTPUT.PUT_LINE(p.func(v)); -- prints 2 END; /
In Example 3-10, determining which instance to call happens at run time because the functions are overriding member functions of the type hierarchy. This is dynamic method dispatch, described in "Dynamic Method Dispatch".
Though v is an instance of super_t, because the value of final_t is assigned to v, the sub_t instance of the function is called, following the rules of substitution.
Example 3-10 Resolving PL/SQL Functions With Inheritance Dynamically
-- Perform the following drop commands if you created these objects in Ex. 3-9
-- DROP PACKAGE p;
-- DROP TYPE final_t;
-- DROP TYPE _sub_t;
-- DROP TYPE super_t FORCE;
CREATE OR REPLACE TYPE super_t AS OBJECT
(n NUMBER, MEMBER FUNCTION func RETURN NUMBER) NOT final;
/
CREATE OR REPLACE TYPE BODY super_t AS
MEMBER FUNCTION func RETURN NUMBER IS BEGIN RETURN 1; END; END;
/
CREATE TYPE sub_t UNDER super_t
(n2 NUMBER,
OVERRIDING MEMBER FUNCTION func RETURN NUMBER) NOT final;
/
CREATE OR REPLACE TYPE BODY sub_t AS
OVERRIDING MEMBER FUNCTION func RETURN NUMBER IS BEGIN RETURN 2; END; END;
/
CREATE OR REPLACE TYPE final_t UNDER sub_t
(n3 NUMBER);
/
DECLARE
v super_t := final_t(1,2,3);
BEGIN
DBMS_OUTPUT.PUT_LINE('answer:'|| v.func); -- prints 2
END;
/
Dynamic SQL is a feature of PL/SQL that enables you to enter SQL information at run time, such as: a table name, the full text of a SQL statement, or variable information.
Example 3-11 illustrates the use of objects and collections with dynamic SQL. First, the example defines the object type person_typ and the VARRAY type hobbies_var, then it defines the package, teams, that uses these types. You need AUTHID CURRENT_USER to execute dynamic package methods; otherwise, these methods raise an insufficient privileges error when you run Example 3-12.
Example 3-11 A Package that Uses Dynamic SQL for Object Types and Collections
CREATE OR REPLACE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);
/
CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);
/
CREATE OR REPLACE PACKAGE teams
AUTHID CURRENT_USER AS
PROCEDURE create_table (tab_name VARCHAR2);
PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);
PROCEDURE print_table (tab_name VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY teams AS
PROCEDURE create_table (tab_name VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
' (pers person_typ, hobbs hobbies_var)';
END;
PROCEDURE insert_row (
tab_name VARCHAR2,
p person_typ,
h hobbies_var) IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
' VALUES (:1, :2)' USING p, h;
END;
PROCEDURE print_table (tab_name VARCHAR2) IS
TYPE refcurtyp IS REF CURSOR;
v_cur refcurtyp;
p person_typ;
h hobbies_var;
BEGIN
OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;
LOOP
FETCH v_cur INTO p, h;
EXIT WHEN v_cur%NOTFOUND;
-- print attributes of 'p' and elements of 'h'
DBMS_OUTPUT.PUT_LINE('Name: ' || p.name || ' - Age: ' || p.age);
FOR i IN h.FIRST..h.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Hobby(' || i || '): ' || h(i));
END LOOP;
END LOOP;
CLOSE v_cur;
END;
END;
/
From an anonymous block, you might call the procedures in package TEAMS:
Example 3-12 Calling Procedures from the TEAMS Package
DECLARE
team_name VARCHAR2(15);
BEGIN
team_name := 'Notables';
TEAMS.create_table(team_name);
TEAMS.insert_row(team_name, person_typ('John', 31),
hobbies_var('skiing', 'coin collecting', 'tennis'));
TEAMS.insert_row(team_name, person_typ('Mary', 28),
hobbies_var('golf', 'quilting', 'rock climbing', 'fencing'));
TEAMS.print_table(team_name);
END;
/