Test REF CURSOR in PL/SQL Developer

[oneworld95]

Hi, all. We've got a procedure that returns two REF CURSORS, and we'd like to figure out how to test them in PL/SQL Developer 6.05.931. The two REF CURSORS will return results to a Web page, but we'd like to test it before we build the page. Thanks.

 

[Flieger]

Here is how i tested ref cursors in pl/sql developer:
============================
CREATE OR REPLACE PACKAGE test_ref_cur IS

-- Author : CRUEPPRICH
-- Created : 10/20/2005 8:48:38 AM
-- Purpose :
TYPE g_emp_rec_type IS RECORD(
empno NUMBER(4)
,ename VARCHAR(10));

TYPE g_emp_cur IS REF CURSOR RETURN g_emp_rec_type;

PROCEDURE get_emp_records(p_deptno IN NUMBER
,p_emp_cur IN OUT g_emp_cur);

END test_ref_cur;
/
CREATE OR REPLACE PACKAGE BODY test_ref_cur IS

PROCEDURE get_emp_records(p_deptno IN NUMBER
,p_emp_cur IN OUT g_emp_cur) IS
BEGIN
OPEN p_emp_cur FOR
SELECT empno
,ename
FROM scott.emp
WHERE deptno = p_deptno;

END get_emp_records;
END test_ref_cur;
/
=======================================

Now create a test script by right clicking on the get_emp_records procedure:

========================================
begin
-- Call the procedure
test_ref_cur.get_emp_records(p_deptno => _deptno,
p_emp_cur => _emp_cur);
end;

========================================

After you execute the test script, the 'value' column on the p_emp_cur row will contain the value ''. You will also see a little button in that grid box. Click the button and a new window will pop up with the contents of the cursor.

Pretty nifty.

Christoph

 

[oneworld95]

Thanks, Flieger. I'm about to test your suggestion.

 

[ScottMattes]

You also need to go to the lower area of the Test Window (where you see Variable, Type and Value), right click and then click on Scan Variables

 

[Flieger]

I'm not familiar with 'Scan Variables.' What does it do?

 

[Marco Kalter]

It searches for bind variables in the PL/SQL Block of the test script (variables prefixed with a colon).

However, you don't need this if you right-click on a program unit and select "Test" from the popup menu. This will automatically create the bind variables for all parameters and for a return value in case of a function.

 

[oneworld95]

By the way, I used the right-click > Test to test my procedure. I'd tried using the Test, but never knew how. The trick is to give the input variables values in the area at the bottom of the screen.

Our whole department uses PL/SQL Developer, and they didn't know about it either. Many have used the SQL*Plus tool in the past for testing. This made my work so much easier. Thank you