Using a Cursor Expression as an actual parameter to a PL/SQL function

A cursor variable (i.e. a variable of type ref cursor) points to an actual cursor, and may be used as a formal parameter to a PL/SQL procedure or function. A cursor expression defines an actual cursor, and as we have seen is a construct that’s legal in a SQL statement. (Both these statements are true pre-Oracle9i.) So we would expect that it would be possible to invoke a PL/SQL procedure or function which has a formal parameter of type ref cursor with a cursor expression as its actual parameter, thus…

My_Function ( cursor ( select my_col from my_tab ) )

In fact, this was not allowed under any circumstances pre-Oracle9i (ORA-22902 ). New at Oracle9i it is now allowed under certain circumstances: when the function (it cannot be a procedure) is invoked in a top level SQL statement. Given a function that can be invoked thus…

declare
  the_cursor sys_refcursor;
  n number;
begin
  open the_cursor for

    select my_col from my_tab;
  n := My_Function ( the_cursor );
  close the_cursor;
end;
/

…it can now be invoked…

select 'My_Function' My_Function from dual
  where My_Function ( cursor ( select my_col from my_tab ) ) = 1;

…or…

select 'My_Function' My_Function from dual
  order by My_Function ( cursor ( select my_col from my_tab ) );

Most significantly, this syntax is now allowed in the invocation of a table function in the FROM list of a SELECT statement.

Note: the following syntax…

begin
  My_Function ( cursor ( select my_col from my_tab ) );
end;

…is not allowed. (It fails with “PLS-00405: subquery not allowed in this context”.)