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
