Mike Christensen Mike Christensen - 5 months ago 18
SQL Question

How to log all exceptions in Oracle package?

I'm trying to log all exceptions in an Oracle package. Here's what I have at the end of the procedure:

EXCEPTION
WHEN OTHERS THEN
INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception');


This works fine, however I also want to log the error code and message. I've tried:

EXCEPTION
WHEN OTHERS THEN
INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception: Error ' || SQLCODE || ', Msg: ' || SQLERRM);


But this gives me the error:

490/7 PL/SQL: SQL Statement ignored
490/100 PL/SQL: ORA-00984: column not allowed here


What's the correct way to do this? Thanks!

Answer

You can't use SQLERRM directly - you have to assign it to an intermediate variable. Note that Oracle 9i would let you get away with it, but that has always been the documented behavior. See here for some sample code.

You could also consider wrapping this bit in an autonomous transaction, so it gets logged even if your PL/SQL code's transaction gets rolled back.

Comments