Friday 2 January 2009

Free your debug Session

Recently I had to build, test and debug some Pl/Sql. I use my all time favourite tool Pl/Sql Developer. But it happens when debugging Pl/Sql that for no apparent reason the debug-session hangs. Especially when hitting the Break button in Pl/Sql Developer, it may happen.
When it happens you'll have to kill Pl/Sql Developer the hard way, using Taskmanager. Because it refuses to quit while there is a session running.

Fortunately, there is a pretty simple solution.

When your test script looks like:
-- Created on 1/2/2009 by MAKKER
declare
  -- Local variables here
  i integer;
begin
  -- Test statements here
  :result := sos_log.log_run(p_schedule_id => :p_schedule_id,
  p_status => :p_status);
end;

Then just declare an extra variable l_time_out of data type number. Then set a time of for example 5 seconds. using the dbms_debug.set_timeout:
declare
  l_time_out number;
-- Local variables here
  i integer;
begin
  l_time_out := dbms_debug.set_timeout(5);
  -- Call the function
  :result := sos_log.log_run(p_schedule_id => :p_schedule_id,
  p_status => :p_status);
end;

When Pl/Sql Developer looses the connection to the debug session the debug session will be freed after the timeout. Pl/Sql Developer gets the control back and you can go on with compiling and debugging.

You can add it easily to the default test template. Unfortunately Pl/Sql Developer does not use the template when you do right-click->test on a program-unit.
For that you could create a macro that adds the code to your test script, and connect it to a key-stroke.

No comments :