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

REF Cursors

REF cursors provide placeholders for eventual real cursors. Using REF cursors, a program may utilize Oracle’s cursor features without being terribly explicit about what data is to be accessed until runtime. Here is a really simple example:

    CREATE OR REPLACE PROCEDURE ref_curs AS
      v_curs SYS_REFCURSOR;
    BEGIN
      OPEN v_curs FOR 'SELECT order_number ' ||
                       ' FROM orders';
      CLOSE v_curs;
    END;

At compile time, Oracle has no idea what the query text will be—all it sees is a string variable. But the REF cursor tells it to be ready to provide cursor functionality in some manner.

The most useful application of REF cursors is to provide “black box” data access to other applications with functions building and returning REF cursors as shown here:

    CREATE OR REPLACE FUNCTION all_orders ( p_id NUMBER )
                      RETURN SYS_REFCURSOR  IS
      v_curs SYS_REFCURSOR;
    BEGIN
      OPEN v_curs FOR 'SELECT * ' ||
                       ' FROM orders ' ||
                      ' WHERE order_number = ' || p_id;
      RETURN v_curs;
    END;

The calling program simply passes an order_number value to the function and is returned access to the underlying data without having to know anything about it beforehand. External applications, such as Microsoft’s .NET, can interrogate the returned REF cursor to determine attributes such as column names and datatypes to decide how to display them.

Here is how the all_orders function might be issued from PL/SQL:

 DECLARE v_curs SYS_REFCURSOR; v_order_rec ORDERS%ROWTYPE; BEGIN v_curs := all_orders(1); FETCH v_curs INTO v_order_rec; IF v_curs%FOUND ...
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