13 Replies Latest reply on Mar 13, 2017 9:22 PM by Gary Graham-Oracle

    I can not debug a procedure

    3412515

      I can't debug this procedure on SQLDeveloper(ver 4.1.3.20).

      DB:Oracle Database Express Edition 11g Release 2.
      There seems to be a problem on line 17.
      I want to know the solution.
      Please give me an answer.

      1   CREATE OR REPLACE PROCEDURE test
      2   IS
      3       cnt NUMBER(10) := 0;
      4
      5       TYPE type_struct IS RECORD (
      6           item1 VARCHAR2(8) := NULL,
      7           item2 VARCHAR2(80) := NULL
      8       );
      9       t_struct type_struct;
      10
      11       TYPE list_struct IS TABLE OF t_struct%TYPE;
      12       tmp_list_struct list_struct := list_struct();
      13
      14   BEGIN
      15       tmp_list_struct.EXTEND(1);
      16       cnt := cnt + 1;
      17       tmp_list_struct(cnt).item1 := '12345678';
      18   END test;
      19   /

       

      update:2017/3/12 by 3412515

        • 1. Re: I can not debug a procedure
          Gbenga Ajakaye

          Where is your error?

          • 2. Re: I can not debug a procedure
            3412515

            Even if I set a breakpoint on line 15, it will be in a nonresponsive state.

            • 4. Re: I can not debug a procedure
              3412515

              01 create or replace
              02 PROCEDURE test
              03 IS
              04     cnt NUMBER(10) := 0;
              05
              06     TYPE type_struct IS RECORD (
              07         item1 VARCHAR2(8) := NULL,
              08         item2 VARCHAR2(80) := NULL
              09     );
              10     t_struct type_struct;
              11
              12     TYPE list_struct IS TABLE OF t_struct%TYPE;
              13     tmp_list_struct list_struct := list_struct();
              14
              15 BEGIN
              16     tmp_list_struct.EXTEND(1);
              17     cnt := cnt + 1;
              18     tmp_list_struct(cnt).item1 := '12345678';
              19     dbms_output.put_line(tmp_list_struct(cnt).item1);
              20 END test;

               

              I added 'dbms_output.put_line' to line 19.
              And when executing without debug, '12345678' was output normally.
              However, when debugging is done, it becomes non-responsive state.

              • 5. Re: I can not debug a procedure
                Pavel Vetesnik

                I tried to test your problem and I got the same result.

                Debugger doesn't even seem to start, it shows no message and no error. It is "launching"...

                My environment is Developer 4.1.3.20, attached to DB through instant client 12c, target DB is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                • 6. Re: I can not debug a procedure
                  MuhammadFaisal

                  the first question is that why do you want to use this  procedure , may be there is more effective idea for do like a prodecure .

                   

                  i read you complete procedure . you have issue in  use of  Variables .

                  • 7. Re: I can not debug a procedure
                    Gary Graham-Oracle

                    Your test case works for me if I set Tools > Preferences... > Debugger > Start Debugging Option > Step Over ,

                    which is one of the suggestions from Jeff's blog referenced in reply 3 (Gbenga Ajakaye) above.

                     

                    Using Run Until a Breakpoint Occurs, however, causes "non-responsiveness", as you stated with various debugger toolbar icons left disabled and other enabled icons not functioning.  It seems the Java main event thread is left in a BLOCKED state for some reason.

                     

                    Alternately, commenting out "tmp_list_struct.EXTEND(1);" and recompiling, I see that..

                    1. The non-responsiveness reproduces when a breakpoint is set on the next line (cnt := cnt + 1;)

                    2. Using the Step Over preference with no breakpoint set, we get an Oracle RDBMS (ORA-00600) error...

                    Connecting to the database local_scott.

                    Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE

                    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.56.1', '54767' )

                    Debugger accepted connection from database on port 54767.

                    ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [], [], [], [], []

                    ORA-06544: PL/SQL: internal error, arguments: [pbjmlt - line out of range], [], [], [], [], [], [], []

                    ORA-06553: PLS-801: internal error [pbjmlt - line out of range]

                    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()

                    Closed Connection

                    Process exited.

                    Disconnecting from the database local_scott.

                    Debugger connection to debuggee process has been lost.

                    Debugger disconnected from database.

                    This was all done against an 11g XE database using the default (thin) JDBC driver that ships with SQL Developer.  The behavior is the same for the ojdbc6 driver in 4.1.3 and the ojdbc8 driver in 4.2 EA2, except I cannot recall which one the ORA-00600 occurred on -- I only did that particular test once, accidentally, as I forgot to set the breakpoint before clicking the debug icon. 

                     

                    This is one of those cases where extra work is required to place blame, and is best done by logging an SR with MOS.  It could be in the JDeveloper code that SQL Developer relies on, or the JDBC driver, or the database (PL/SQL or RDBMS).

                    • 8. Re: I can not debug a procedure
                      3412515

                      Since I can not disclose everything,

                      I extract problems and simplify the procedure to describe them.

                      • 9. Re: I can not debug a procedure

                        I don't have any problem stepping line by line no matter where I set the breakpoint. Using 4.1.1.19.59 and 12c

                        BEGIN

                          dbms_output.enable();

                          TEST();

                        --rollback;

                        END;

                        I added the enable to the block the debugger creates otherwise you won't see the dbms_output.

                         

                        Works fine for me.

                        • 10. Re: I can not debug a procedure
                          MuhammadFaisal

                          I check your procedure , there is no error in this .

                          kindly try this query

                           

                           

                          declare

                          cnt NUMBER(10) := 0;

                           

                                 TYPE type_struct IS RECORD (

                                     item1 VARCHAR2(8) := NULL,

                          item2 VARCHAR2(80) := NULL

                                 );

                                 t_struct type_struct;

                           

                           

                                 TYPE list_struct IS TABLE OF t_struct%TYPE;

                                 tmp_list_struct list_struct := list_struct();

                           

                           

                             BEGIN

                                 tmp_list_struct.EXTEND(1);

                                 cnt := cnt + 1;

                                 tmp_list_struct(cnt).item1 := '12345678';

                                dbms_output.put_line(tmp_list_struct(cnt).item1);

                          END test;

                          • 11. Re: I can not debug a procedure
                            Gary Graham-Oracle

                            I don't have any problem stepping line by line no matter where I set the breakpoint. Using 4.1.1.19.59 and 12c

                            Thanks for the feedback.

                             

                            Accessing a remote 12.2 Beta DB on Linux from Windows 7 via VPN, I see the same "Launching.." hang (which eventually times out) as reported by Pavel Vetesnik in response 5.  If I try the same thing locally on that Linux box, stepping line by line works fine. I have heard other complaints regarding this, so perhaps it is a Windows only issue.  I believe others are investigating, or at least are aware that this needs investigation.

                             

                            Edit: As for the dbms_output.enable(), that is not necessary with the most recent releases, or if you use View > Dbms Output in releases where it is still required.

                            • 12. Re: I can not debug a procedure

                              Edit: As for the dbms_output.enable(), that is not necessary with the most recent releases, or if you use View > Dbms Output in releases where it is still required.

                              It is necessary with the version I used. The dbms_output window doesn't show any output when you debug.

                               

                              I'm assuming that's because the 'debug' connection is using a different session so the output goes to it.

                              • 13. Re: I can not debug a procedure
                                Gary Graham-Oracle

                                I'm assuming that's because the 'debug' connection is using a different session so the output goes to it.

                                It is definitely a different session.  I was obviously mistaken on the View > Dbms Output comment. Anyway, in 4.2 production you should see that dbms_output.enable() is automatic for Run (Ctrl-F10) and Debug... (Ctrl-Shift-F10) from the PL/SQL code editor.  Even in the Run case there is no dependency on the worksheet's serveroutput setting (if a worksheet should even be opened at the time).