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

Cursor Parameters

As the examples in the previous section suggest, it is possible to pass cursors as parameters using straight SQL. This can also be done in a SELECT statement using the CURSOR keyword .

    SELECT count_valid(CURSOR(SELECT order_number
                                FROM orders
                               WHERE processed IS NULL))
      FROM dual;

The count_valid function might look something like this:

    CREATE OR REPLACE FUNCTION count_valid( p_curs SYS_REFCURSOR )
                      RETURN NUMBER IS
      v_on NUMBER;
      v_ret_val NUMBER := 0;
    BEGIN
      FETCH p_curs INTO v_on;
      LOOP
        EXIT WHEN p_curs%NOTFOUND;
        IF extensive_validation(v_on) THEN
          v_ret_val := v_ret_val + 1;
        END IF;
        FETCH p_curs INTO v_on;
      END LOOP;
      RETURN(v_ret_val);
    END;

The SELECT statement is passed right into the function that then loops through the records it returns, validating them and then returning a count of those deemed valid. This results in two cursors in the shared pool and the soft-closed list for the user.

    SQL_TEXT
    ----------------------------------------
    SELECT "A2"."ORDER_NUMBER" "ORDER_NUMBER
    " FROM "ORDERS" "A2" WHERE "A2"."PROCESS
    ED" IS NULL

    SELECT count_valid(CURSOR(SELECT order_n
    umber                             FROM o
    rders                            WHERE p
    rocessed IS NULL))   FROM dual
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