In the previous posts we have covered Ref Cursors and Ref Cursor examples. For all the examples I have used the package we have created in Ref Cursor Example post create or replace package pkg_refcur procedure p_get_employees(pi_deptno in integer, procedure p_get_dept_emp(pi_deptno in integer, create or replace package body pkg_refcur procedure p_get_dept_emp(pi_deptno in integer, …………………………………………. 1. Using sqlplus — Declare a variable for refcursor — Execute the package PL/SQL procedure successfully completed. — Print the refcursor variable EMPNO EMPNAME SALARY DEPTNO MANAGER 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; 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 PL/SQL procedure successfully completed. The above logic you can implement inside your calling program or procedure for debug purpose. 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 Function created. SQL> select f_refcur from dual; F_REFCUR CURSOR STATEMENT : 1 EMPNO EMPNAME DEPTNO DEPTNAME 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 Step 2 Click right most icon of the opened window as shown below Step 3 Choose the last radio button “Load into grid from memory (strong and weak)” Click OK Click OK Now you can see the ref cursor results in another window as shown below 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
Step 2 Click Run button or Ctrl-F10 Click OK 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. Related |
|