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