In the previous posts we have covered Ref Cursors and Ref Cursor examples.
Now we will examine how Ref Cursor examples are tested.
For all the examples I have used the package we have created in Ref Cursor Example post
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.