Menu
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!
380
You can create the error log as a global temporary table. But you must do this with the "on commit preserve rows" option: When using bulk processing in PL/SQL, you can also the SAVE EXCEPTIONS clause of FORALL to collect an array of the failing rows. There is another method to continue past errors.
The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete.
To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.
There are three types of exceptions: Predefined exceptions are error conditions that are defined by PL/SQL. Non-predefined exceptions include any standard TimesTen errors. User-defined exceptions are exceptions specific to your application.
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.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
