7 Replies Latest reply: Nov 5, 2012 12:19 PM by Gary Graham-Oracle RSS

    Viewing the ref cursor output in debug mode in sql developer

    user8936847
      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
          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-Oracle
            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-Oracle
              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
                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
                  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
                    >
                    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-Oracle
                      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