Oracle debugging techniques

Firstly, don't "start from here", or more specifically, don't use triggers. Triggers are going to force switching to row-level processing if the triggers are going to fire for each row. It's better to put the logic in a stored procedure which you call. You've then got a start (where you validate inputs) and an end and a logic path all the way through. Stored procedures are a lot easier to debug as you follow one path.

Second, never test for an error you don't know how to handle. If you don't catch it, it bubbles up to the client who gets an error report saying what went wrong (error message) and where (i.e. the error/call stack). If you try to catch it, you have to know what to do with it (and if you don't know the tendency is to ignore it - which is BAD).

Finally, you can't readily see each 'layer' of a select. The explain plan will generally tell you how its going about things. v$session_longops MAY indicate what it is currently doing. The current wait event MAY give clues as to what table/block/row it is currently working on.

A rough-and-ready simple method if you must debug triggers is to use DBMS_OUTPUT.

e.g.

SQL> CREATE OR REPLACE TRIGGER mytrigger
     BEFORE UPDATE ON mytable
     FOR EACH ROW
     ...
     BEGIN
       DBMS_OUTPUT.put_line('mytrigger STARTING');
       ... do some logic ...
       DBMS_OUTPUT.put_line('old=' || :OLD.mycolumn);
       DBMS_OUTPUT.put_line('new=' || :NEW.mycolumn);
       DBMS_OUTPUT.put_line('mytrigger FINISHED');
     END;
     /

SQL> SET SERVEROUT ON
SQL> UPDATE mytable SET mycolumn = mycolumn + 1;
2 rows updated.

mytrigger STARTING
old=10
new=11
mytrigger FINISHED
mytrigger STARTING
old=20
new=21
mytrigger FINISHED