Oracle PL/SQL for DBAs

Defining the Result Set Structure

PL/SQL is pretty smart about resolving the result set of a table or a view. The language makes it easy for us not to worry about datatypes by substituting %TYPE and %ROWTYPE variables as follows.

    DECLARE
      v_order_row orders%ROWTYPE;
    BEGIN
      SELECT order_id,
             region_id
        INTO v_order_row
        FROM orders;
    END;

However, PL/SQL will be at a loss to decipher the structure returned by a table function because it has no basis from which to work. You must provide that basis explicitly via Oracle objects and collections. This is illustrated in this example of declaring an object and then a collection of that object.

    CREATE TYPE rowset_o AS OBJECT ( col1 NUMBER,
                                     col2 VARCHAR2(30));
    /
    CREATE TYPE rowset_t AS TABLE OF rowset_o;
    /

The “multi-recordness” of the collection is what allows it to be used as the result set of a function.

    CREATE OR REPLACE FUNCTION simple RETURN rowset_t AS
      v_rowset rowset_t := rowset_t(  );
    BEGIN
      v_rowset.EXTEND(3);
      v_rowset(1) := rowset_o(1,'Value 1');
      v_rowset(2) := rowset_o(2,'Value 2');
      v_rowset(3) := rowset_o(3,'Value 3');
      RETURN(v_rowset);
    END;

All the function does is assemble three objects into a collection and return them. Now the function can be called from a SELECT statement using the TABLE keyword to tell Oracle to treat the returned collection as if it were a set of records.

    SQL> SELECT *
      2    FROM TABLE(simple);

          COL1 COL2
    ---------- ----------------
             1 Value 1
             2 Value 2
             3 Value 3

    3 rows selected.

The full power of Oracle SQL can be applied ...