How to debug a trigger in Oracle SQL developer (version 20.4.1.407)?

Question

I have the following before-insert trigger, that assigns a sequence number to a record of an intersection table:

before insert on psln
for each row 
declare v_seqnr number;
begin
  select nvl(max(psln_seqnr),0) into v_seqnr from psln where pers_id = :new.pers_id;
  :new.psln_seqnr := v_seqnr + 1;
end;

Now i want to debug this trigger to see if v_seqnr gets the proper value, depending on the value of :new.pers_id. These are the steps i take:

• i configure the Start Debugging Option (Tools - Preferences - Debugger) as follows:

(when i choose a different option, my stack window remains empty while debugging, see below)

• i compile the trigger for debugging (Ctrl-Shift-F8)
• i place a breakpoint on the begin-statement
• i start debugging the trigger (Ctrl-Shift-F10) and get the following debug popup window, where i enter the values 1,1 for the insert-statement, that will cause the trigger to fire:

• i press OK and see the following (note the green V on the red breakpoint, which i think is the point of execution):

• when i now click on the Step-Into debug-icon with the red arrow (or press F7), i expect to jump to the first line of code in the begin-block, but nothing happens ...
• i also expect to see the value 1, when holding my mouse over the :new.pers_id expression, but this also does not work

Can someone please explain me what i am doing wrong or what i have forgotten?

Answer 1

I think this is a known bug, in SQLDEVELOPER, one fix for it, could be instead of use an ANNONIMOUS PROCEDURE (which is created when you type in the "lady bug"), you can try to fire the trigger by a normal STORE PROCEDURE, that means, you can create a "dummy", procedure which emulates the insert, compile it for debug, an run it, if all work well, your execution should stops in the trigger's break point, is like to do a simulation of debug a procedure which has a trigger in.