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