Oracle debugging techniques [closed]

Question

I'm having difficulty debugging triggers in Oracle. Currently I'm using Oracle's Sql Developer tool.

To test a trigger I write inserts or deletes in a test window but I can't see what is happening inside the trigger. I would like to step through the trigger and see what is happening as the trigger fires. Is there any way to use select statements to display variable values inside the triggers?

Answer 1

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.

Answer 2

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

Answer 3

Application I use a program from Quest called TOAD available at www.quest.com/toad/toad-for-oracle.aspx.

As mentioned above, DBMS_OUTPUT is very handy. In your editor, make sure you enable the Output window.

PL/SQL works on "blocks" of code and you can catch it with an EXCEPTION keyword.

(Please forgive my formatting, not sure how to format for web)

DECLARE
    C_DATE_FORMAT VARCHAR2(20) := 'DD-Mon-YYYY';
    C_TIME_FORMAT VARCHAR2(20) := 'HH24:MI:SS';
    C_NOT_IMPLEMENTED_CODE CONSTANT NUMBER(5) := -20200;
    C_NOT_IMPLEMENTED_MESSAGE CONSTANT VARCHAR2(255) := 'Not implemented';
    not_implemented EXCEPTION; -- user defined exception
BEGIN
    --RAISE not_implemented; -- raise user defined exception
    RAISE_APPLICATION_ERROR(C_NOT_IMPLEMENTED_CODE, C_NOT_IMPLEMENTED_MESSAGE); -- user defined exception
EXCEPTION -- exception block
    WHEN not_implemented THEN -- catch not_implemented exception
        DBMS_OUTPUT.PUT_LINE('Error: Not implemented');
    WHEN OTHERS THEN -- catch all other exceptions
        DBMS_OUTPUT.PUT_LINE('Error occured.');
        DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(SYSDATE, C_DATE_FORMAT));
        DBMS_OUTPUT.PUT_LINE('Time: ' || TO_CHAR(SYSDATE, C_TIME_FORMAT));
        DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM); --deal with error
        RAISE; -- raise to calling object
END;