Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page