5 different ways to test Oracle Ref Cursor results

Ref Cursor Testing With Examples

create or replace package pkg_refcur 

is
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);
………………………………………….
………………………………………….
End pkg_refcur;
/

create or replace package body pkg_refcur
is
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select e.empno,e.empname,d.deptno,d.deptname from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

………………………………………….
………………………………………….
End pkg_refcur;

1. Using sqlplus 

— Declare a variable for refcursor
SQL> variable v_refcur refcursor;

— Execute the package
SQL> exec pkg_refcur.p_get_employees(2,:v_refcur);

PL/SQL procedure successfully completed.

— Print the refcursor variable
SQL> print v_refcur;

EMPNO EMPNAME                  SALARY     DEPTNO    MANAGER
—————————————————————–
16 Henry                      8500          2         11
17 Robert                     9500          2         16
18 Paul                       7700          2         17

Please note that v_refcur is a bind variable here.

2. Using %rowtype 

This will be useful only when the ref cursor is based on a single table

SQL> set serveroutput on;
SQL> declare
rec_emp  emp%rowtype;
v_refcur sys_refcursor;
Begin
pkg_refcur.p_get_employees(2,v_refcur);
loop
fetch v_refcur into rec_emp;
exit when v_refcur%notfound;
dbms_output.put_line(‘Empno   –>’ || rec_emp.empno);
dbms_output.put_line(‘Empname –>’ || rec_emp.empname);
dbms_output.put_line(‘Salary   –>’ || rec_emp.salary);
dbms_output.put_line(‘deptno –>’ || rec_emp.DEPTNO);
end loop;
End;
/
Empno   –>16
Empname –>Henry
Salary   –>8500
deptno –>2
Empno   –>17
Empname –>Robert
Salary   –>9500
deptno –>2
Empno   –>18
Empname –>Paul
Salary   –>7700
deptno –>2

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose

3. Using PL/SQL table of type and record 

Procedure p_get_dept_emp cannot be tested with using (2). So declare a record and type on it.

SQL> Declare
2  Type rec_emp_dept is record
3  (
4  empno    emp.empno%type,
5  empname  emp.empname%type,
6  deptno   dept.deptno%type,
7  deptname dept.deptname%type
8  );
9  rec      rec_emp_dept;
10  v_refcur sys_refcursor;
11  Begin
12  pkg_refcur.p_get_dept_emp(2,v_refcur);
13   loop
14       fetch v_refcur into rec;
15       exit when v_refcur%notfound;
16       dbms_output.put_line(‘Empno    –>’ || rec.empno);
17       dbms_output.put_line(‘Empname  –>’ || rec.empname);
18       dbms_output.put_line(‘Deptno   –>’ || rec.deptno);
19       dbms_output.put_line(‘Deptname –>’ || rec.deptname);
20   end loop;
21  End;
22  /
Empno    –>16
Empname  –>Henry
Deptno   –>2
Deptname –>RESEARCH
Empno    –>17
Empname  –>Robert
Deptno   –>2
Deptname –>RESEARCH
Empno    –>18
Empname  –>Paul
Deptno   –>2
Deptname –>RESEARCH

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose.
This is not ideal for a Ref Cursor returning many columns.

4. Using a wrapper function to call 

This is a pretty much easy method. But may not work if you have a procedure having DML and ref cursor return

SQL> create or replace function f_refcur return sys_refcursor
2  is
3  v_refcur sys_refcursor;
4  Begin
5    pkg_refcur.p_get_dept_emp(2,v_refcur);
6    return v_refcur;
7  End;
8  /

Function created.

SQL> select f_refcur from dual;

F_REFCUR
——————–
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

EMPNO EMPNAME                  DEPTNO DEPTNAME
———- ——————– ———- ——————–
16 Henry                         2 RESEARCH
17 Robert                        2 RESEARCH
18 Paul                          2 RESEARCH

5. Using Developer tools like TOAD, Sql Developer etc

Normally professional developers use any of the development tools like TOAD or SQL Developer etc.

a) Using TOAD

Step 1

Connect to TOAD

Open Schema Browser

Click “Package” tab

Locate the package and choose “Execute Procedure” from right click menu

Test Ref Cursor TOAD Step 1

Step 2

Click right most icon of the opened window as shown below

Test ref cursor from TOAD step 2

Step 3

Choose the last radio button “Load into grid from memory (strong and weak)”

Test ref cursor from TOAD step 3

Click OK

Click OK

Now you can see the ref cursor results in another window as shown below

Test ref cursor from TOAD Step 4

In similar manner you can test any ref cursor within package/procedure/function

b) SQL Developer

SQL Developer is a Oracle Corporation product.

Step 1

Connect to SQL Developer ( I Have used version 3.0.4)

Click “Package” node and locate the package

Test red cursor from SQL Developer Step 1

Step 2

Click Run button or Ctrl-F10

Test red cursor from SQL Developer Step 2

Click OK

Step 3

Test red cursor from SQL Developer Step 3

(In the message window you have to choose “Output variables”)

Conclusion :- Most of the ref cursors can be tested with either of the method. Also you can use the base query directly in SQL window or SQL plus to test the ref cursors.