Problem: execute sql in debug session...

[fraenkg]

Hello Marco,
I'm having the following problem debugging PL/SQL:
When I try to execute an SQL statement within a debug session using ["execute sql in debug session..."], the entire IDE freezes. Even a second (parallel) IDE instance becomes unusable in this case.
Killing the session (Action = Debug script for ...) on the database doesn't change anything. Only killing the "Secondary session" of the PLSD causes the IDE to return with an ORA-03114 error.
When I then log back into the database, I can see the "Debugger result." However, debugging is no longer possible because the script is always executed completely, and step-by-step execution is not possible.

By the way, after killing the "Secondary Sessions" from the debug instance, the second IDE instance is now working again. I don't need to log in again there either.
And I can debug step-by-step there again... until the next ["execute sql in debug session..."].
I've noticed this problem for some time now. I'm currently dealing with complex code where it would be extremely helpful to track changes in the tables. Therefore, I hope there's a solution to this problem.

Best regards, Frank

I'm using version v16.0.7 on Windows 10 22H2
Here's my test-debug script.

create table test_debug (a integer);

create or replace procedure test_debug2 is
fn      pls_integer;
begin
  for i in 1..100 loop
    insert into test_debug (a) values (i);
    if (i=50) then
      fn := i; -- select count(1) from test_debug;
    end if;
  end loop;
end test_debug2;
/

drop procedure test_debug2;
drop table test_debug purge;

 

[Marco Kalter]

Can you let me know which SQL statement you tried to execute in the debug session? Is it the "select count(1) from test_debug"? I tested this (after removing the erroneous "begin" from the code) and it seems to work fine.

If so, then to obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 16\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 16 directory (e.g. C:\Users\<User>\AppData\Roaming\PLSQL Developer 16).

 

[fraenkg]

Hi Marco,
Thank you for the quick response.
That's right, the 'begin' is not correct in this context. I've corrected that in above code section. And, of course, the procedure also has to be compiled with debug information.
I ran the scenario again with the 'DebugSQL' parameter. For me, however, the 'debug.txt' was created in the program directory. I have also attached this file.
Hopefully, this will help in finding a solution to the problem.
Thank you very much for your efforts in advance and best regards, Frank

 

[fraenkg]

Brief update:
I successfully debugged the procedure on the same machine with versions 15.0.4.2064 and 14.0.4.1982. The problem only occurs with version 16, and this is independent of the database version (12.2 or 19.7). Maybe this helps.

 

[Marco Kalter]

In the log file we can see the an "ORA-03114: not connected to ORACLE" error after 73 seconds when the TEST_DEBUG table name is resolved. This indicates that the server process for the session may have crashed.

Maybe the difference between 15.0 and 16.0 is caused by a client configuration setting. Can you start PL/SQL Developer 16 (where this fails) and PL/SQL Developer 15 (where this works), connect to this database, go to Help > Support Info, and send the text from the "Info" page to support@allroundautomations.com?