sinistral.sourcery

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.