Testing cursor variable in SQL Plus

In Oracle stored functions and procedures, you may often return or pass out a result set using cursor variable. Here're some ways I found you can test those cusor variables using SQL Plus. It uses the Oracle hr schema for the example.

Now I created a package:

create or replace package test_refcursor as
TYPE DynamicCursorType IS REF CURSOR;
procedure test( id in number,
result out DynamicCursorType );
end test_refcursor;
/

Create test package body:

create or replace package body test_refcursor as
procedure test (
id in number,
result out DynamicCursorType
)
as
begin
open result for select department_id,department_name
from departments where department_id < id;
end;
end test_refcursor;
/

The procedure test returns all the departments with department_id lower than passed in ID.

Now the first way to test it in a PL/SQL block:

set serveroutput on
declare
p test_refcursor.DynamicCursorType;
v_id number(4);
v_name varchar2(100);
begin
test_refcursor.test( 50, p );
-- The cusor has already opened, no need to open again.
loop
fetch p into v_id, v_name;
exit when p%NOTFOUND;
dbms_output.put_line( v_id || ', ' || v_name );
end loop;
end;
/

The result you can see:

10, Administration
20, Marketing
30, Purchasing
40, Human Resources

There's another way even easier in SQL Plus:

var p refcursor
exec test_refcursor.test( 50, :p );
print p

It'll print as if you select the data from a table:

DEPARTMENT_ID DEPARTMENT_NAME
------------- ----------------------------------
10 Administration
20 Marketing
30 Purchasing
40 Human Resources