Oracle debugging techniques

Question

I don't know where error information goes when a trigger doesn't work correctly.

My tool for writing triggers has been Oracle's Sql Developer tool, and my knowledge of how to debug it is pretty much nonexistent. What are some pointers for being able to find useful information about things happening "behind the scenes"? Also, are there better tools that Sql Developer for connecting, testing, debugging, etc?

My method so far has been to write something (a trigger, for instance), test it with single inserts/deletes, and then hope it works from them on. Are there better ways to make sure it's doing exactly what you want? For instance, with a select statement, is there anyway to see (in a debug state or something) each level of the select and how it whittles down results? Any tips greatly appreciated.

Answer 1

Firstly, all code works correctly. It just doesn't do what you expected it to do.

Secondly, "Don't start from here", or specifically don't use triggers. It is basically going to force switching to row-level processing if triggers are going to fire for each row. Better to actually 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. They are a lot easier to debug as you follow one path.

Three, 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 (ie 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;

Answer 4

FORMAT_ERROR_BACKTRACE will give you a full backtrace from a line that produced an error. Some people don't know this one, but it's really invaluable if you spend a lot of time debugging PL/SQL:

CREATE OR REPLACE TRIGGER mytrigger
     BEFORE UPDATE ON mytable
     FOR EACH ROW
     ...
     BEGIN
       ...
     EXCEPTION
       WHEN OTHERS THEN -- Appended for debugging purposes only!
         DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
     END;
 /

For logic errors, INSERTs into debugging tables, DBMS_OUTPUT.PUT_LINEs, etc.

Answer 5

SQL Developer has a nice PL/SQL debugger: http://www.packtpub.com/article/debugging-pl-sql-in-oracle-sql-developer