This discussion is archived
7 Replies Latest reply: Nov 5, 2012 10:16 AM by rp0428 RSS

Viewing the ref cursor output in debug mode in sql developer

user8936847 Newbie
Currently Being Moderated
Hi,
I 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. Below are the details of the versions I'm using


•Version of SQL Developer: 3.2.09
•Version of the JDK : JDK 1.6.0_11
•The OS : Windows 7
•The version of Oracle : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE     10.2.0.3.0     Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Regards
Deepak Aggarwal
  • 1. Re: Viewing the ref cursor output in debug mode in sql developer
    rp0428 Guru
    Currently Being Moderated
    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.
    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;
    /
    Here is the object and procedure code . If you debug the procedure you won't get any output because nothing is FETCHing the cursor.
    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;
    /
  • 2. Re: Viewing the ref cursor output in debug mode in sql developer
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    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;
  • 3. Re: Viewing the ref cursor output in debug mode in sql developer
    Vadim Tropashko Pro
    Currently Being Moderated
    After debugging is finished I see empty Structured Log panel with no list of variables at all, neither scalar, nor refcursor. I assume this is the problem... Fixed.
  • 4. Re: Viewing the ref cursor output in debug mode in sql developer
    user8936847 Newbie
    Currently Being Moderated
    Dear All,
    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*

    Regards
    Deepak
  • 5. Re: Viewing the ref cursor output in debug mode in sql developer
    972610 Newbie
    Currently Being Moderated
    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)

    thank you
  • 6. Re: Viewing the ref cursor output in debug mode in sql developer
    rp0428 Guru
    Currently Being Moderated
    >
    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.
  • 7. Re: Viewing the ref cursor output in debug mode in sql developer
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    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

    Regards,
    Gary
    SQL Developer Team

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points