REF CURSORs: the bigger picture

One of the driving requirements for the introduction of ref cursors was the ability to declare a PL/SQL procedure for function with a ref cursor formal parameter or return type. This feature, together with the ability to make the appropriate binds from the client programming environment (eg OCI or client-PL/SQL) allows client code to be written to handle results from a cursor returning a specified record type (ie a strong ref cursor) while delegating the opening of the cursor (and hence the specifics of the select list) to server-sdie code.

We can demonstate this using SQL*Plus. (SQL*Plus is an OCI client application which exposes a conveient command-oriented UI for the sophisticated SQL-literate end user.) Run this...

create or replace function F return sys_refcursor is
  v_cur sys_refcursor;
begin
  open v_cur for select last_name from employees where rownum < 11;
  return v_cur;
end F;
/

Variable host_cur refcursor
Call F() into :host_cur;
Print host_cur

This shows that it's possible to write even more elaborate client-side code to process the results of a weak ref cursor) whose record type is not known until run-time.

Of course in such a situation it would be wrong for the PL/SQL system to close the ref cursor when F exits. Thus if you observe open_cursor_sql_text from another session you'll see there's an open cursor for select last_name from employees where rownum < 11. The cursor will be closed either by explicit client-side code or when the client calls a server-side PL/SQL procedure to do this having bound the appropriate actual parameter. If the application is badly written and does not implement closing the cursor, then it will be closed by ORACLE when the session ends.

Note: You cannot currently (up to Oracle9i) declare a ref cursor as a package global as the following sample shows...

package P is
  procedure Do_Open;
  -- ...
end P;

package body P is
  g_cur sys_refcursor
     /* 
     PLS-00994:
     Cursor Variables cannot be declared as part of a package
     */;


  procedure Do_Open is begin
    open g_cur for 'select * from emp';
  end Do_Open;

  -- ...
end P;