The system that I’ve been working on uses as a standard stored procedures that declare an OUT parameter through which a result cursor is “returned” to the caller. While it is easy enough to call the procedure and process the result set in Java, I occasionally want to call such a procedure from SQL*Plus. To do this, one must bind a suitably typed variable to the procedure’s output parameter, for example:
SQL> var proc_res refcursor;
SQL> exec foo.get_stuff (:proc_res);
PL/SQL procedure successfully completed.
SQL> print proc_res;
DUM
---
X
In this example package foo and procedure get_stuff are declared as:
SQL> create or replace package foo is
2 type cursor_out is ref cursor;
3 procedure get_stuff (results out cursor_out);
4 end foo;
5 /
Package created.
SQL>
SQL> create or replace package body foo as
2 procedure get_stuff (results out cursor_out) is
3 c1 cursor_out;
4 begin
5 open results for select * from dual;
6 end get_stuff;
7
8 end foo;
9 /
Package body created.