Using PL/SQL With Object Types

This chapter describes how to use object types with PL/SQL

This chapter contains these topics:

• Declaring and Initializing Objects in PL/SQL
• Manipulating Objects in PL/SQL
• Using Overloading in PL/SQL with Inheritance
• Using Dynamic SQL With Objects

Declaring and Initializing Objects in PL/SQL

Using object types in a PL/SQL block, subprogram, or package is a two-step process.

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

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

Defining Object Types

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;

Declaring Objects in a PL/SQL Block

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

How PL/SQL Treats Uninitialized Objects

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.

Manipulating Objects in PL/SQL

This section describes how to manipulate object attributes and methods in PL/SQL.

This section includes the following topics:

• Accessing Object Attributes With Dot Notation
• Calling Object Constructors and Methods
• Updating and Deleting Objects
• Manipulating Objects Through Ref Modifiers

Accessing Object Attributes With Dot Notation

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;

/

Calling Object Constructors and Methods

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"

Updating and Deleting Objects

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;

Manipulating Objects Through Ref Modifiers

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 .

Using Overloading in PL/SQL with Inheritance

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.

Rules of Substitution

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;

/

Using Dynamic SQL With Objects

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.

See Also:

Oracle Database PL/SQL Language Reference

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;

/