This content has been marked as final. Show 7 replies
Welcome to the forum!
Thanks for posting all of the version information. But since you didn't post the code you are using only general comments can be made.
am using SQL developer version 3.2 and trying to debug a procedure in it. The proc returns a ref cursor on its completion.
When I debug the proc, then I am not able to view the output of the ref cursor in the output window. However when I directly execute the proc(without debugging) then I can see the output of the ref cursor in the output tab.
Can anyone explain the reason for this strange behaviour.
That is normal behavior. A REF CURSOR is not a result set so there is nothing to 'view'.
Client code has to FETCH from the cursor in order to receive data. When you execute the proc there is code that performs the FETCH and displays the results.
Here is sample code that you can test in the SCOTT schema. If you run this code then you will get output because the code FETCHes from the cursor.
Here is the object and procedure code . If you debug the procedure you won't get any output because nothing is FETCHing the cursor.
SET SERVEROUTPUT ON SIZE 1000000 DECLARE l_cursor test_refcursor_pkg.my_ref_cursor; l_ename emp.ename%TYPE; l_empno emp.empno%TYPE; BEGIN test_refcursor_pkg.test_proc (l_cursor); LOOP FETCH l_cursor INTO l_empno, l_ename; EXIT WHEN l_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno); END LOOP; CLOSE l_cursor; END; /
CREATE OR REPLACE TYPE SCOTT.local_type IS OBJECT ( empno NUMBER(4), ename VARCHAR2(10)); / CREATE OR REPLACE TYPE SCOTT.local_tab_type IS TABLE OF local_type; / CREATE OR REPLACE PACKAGE SCOTT.test_refcursor_pkg AS TYPE my_ref_cursor IS REF CURSOR; -- add more cursors as OUT parameters PROCEDURE test_proc(p_ref_cur_out OUT test_refcursor_pkg.my_ref_cursor); END test_refcursor_pkg; / CREATE OR REPLACE PACKAGE BODY SCOTT.test_refcursor_pkg AS PROCEDURE test_proc(p_ref_cur_out OUT test_refcursor_pkg.my_ref_cursor) AS l_recs local_tab_type; BEGIN -- Get the records to modify individually. SELECT local_type(empno, ename) BULK COLLECT INTO l_recs FROM EMP; -- Perform some complex calculation for each row. FOR i IN l_recs.FIRST .. l_recs.LAST LOOP DBMS_OUTPUT.PUT_LINE(l_recs(i).ename); END LOOP; -- Put the modified records back into the ref cursor for output. OPEN p_ref_cur_out FOR SELECT * from TABLE(l_recs); -- open more ref cursors here before returning END test_proc; END; /
I see what you're saying.
If you debug a stored procedure that returns a refcursor or has an OUT parameter, it's not displayed in the Log > Output Variables panel.
However, if you execute it, it works as expected.
I'm not sure if this is intentional or not. The same anonymous block is being used, which includes the bit to capture the cursor
:v_Return := v_Return;
Thanks for your responses to my question.
After your suggestion, I raised a SR with Oracle and below is the response for the same:
"After discussing this issue with development, An Internal bug was filed and has been marked to be fixed by next release.
So for time being would suggest to use Command line tool such as Sqlplus for manual debugging or use a Direct run to access the variable values.
This issue is limited only to CURSORS. So believe should not impact Debugging from SQL Developer for other codes not involving CURSORS."
Bug 14611357 CURSOR OUTPUT AFTER DEBUGGING MISSING: The bug reported is marked to be fixed in release *3.2.10.09.34*
Is Bug 14611357 CURSOR OUTPUT AFTER DEBUGGING MISSING: Include in latest release Nov 1 2012 ? Also can you direct me to find detail on Oracle support site (Please post the link)
OP was trying to see the output of a ref cursor when the procedure was debugged.
A ref cursor doesn't have any output - it is a pointer to program code. If a procedure returns a ref cursor then when you debug the procedure there won't be any output to observe.
To get output you have to FETCH the ref cursor - you can't FETCH a ref cursor from a procedure that returns the ref cursor. So OP 'problem' really isn't a problem.
Bug 14611357 was fixed (verified by QA) as of 3.2.1. It should be fixed in 3.2.2 also, barring any regression. Support can be found at: http://www.oracle.com/us/support/index.html
SQL Developer Team