Mike Christensen Mike Christensen - 1 year ago 80
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:

INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception');

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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download