Trap Constraint Errors in PL/SQL? [message #669504] |
Thu, 26 April 2018 08:37 |
|
whdyck
Messages: 25 Registered: May 2017
|
Junior Member |
|
|
I'd like to be able to log all constraint errors when users are running an application having Oracle as the back-end. That means inserting a record into an Errors Log table in Oracle.
Is that even possible? In an exception clause, I could do that; however, when a constraint fails, I don't know where I'd be able to run such code to do the insert.
Thanks for any help you can give.
Wayne
(Cross-posted on club-oracle.com)
|
|
|
Re: Trap Constraint Errors in PL/SQL? [message #669505 is a reply to message #669504] |
Thu, 26 April 2018 08:42 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well if the insert is in PL/SQL then an exception clause will catch constraint errors same as any other error.
There's also the log errors clause for DML statements.
And FORALL has SAVE EXCEPTIONS.
If you're not using PL/SQL or log errors then you have to use the exception handling capability of the language you are using.
|
|
|
|
|
Re: Trap Constraint Errors in PL/SQL? [message #669509 is a reply to message #669507] |
Thu, 26 April 2018 10:06 |
|
whdyck
Messages: 25 Registered: May 2017
|
Junior Member |
|
|
Thanks, cookiemonster. You articulated my two concerns about Oracle's error logging: (1) one log table per table updated via DML, and (2) need to add the LOG ERRORS clause to every DML statement.
I'm looking for a more general solution that would also work when a front-end app tied to Oracle back-end tries to save a record to Oracle. In my case, I have an MS Access front-end saving records to Oracle tables linked via ODBC.
Your first response suggests that I'd need to trap the error in Access, then initiate my own error-record insertion process manually from within Access.
More desirable would be if Oracle had a more generic error-table solution where no matter the source table of the error, Oracle auto-inserts a record into the error table with nothing more than (1) the source table name (being manipulated by the DML), (2) Oracle error number, (3) Oracle error description (text) that would have displayed, (4) call stack.
When a user of my MS Access app tries to save a record such that a check constraint would fail, I'd like the app to be able to find the error in an error table and display a user-friendly error, rather than seeing something like "Check constraint CHKUSRUNTASNDATEOVERLAPS failed". Yes, I know I can create VBA code in the front-end that would detect that condition before trying to save. Buy why should I have to for every constraint? I'd prefer that Oracle be the one-stop shop for detecting and recording errors, and Access would merely find the error record in an error table, then map that error to a user-friendly error message, perhaps stored in another linked Oracle table. I'd also like to record such errors so IT has a tool to investigate such errors.
Alas, I suspect that to do this, I'll need to parse out the error in Access then go from there. Parsing ODBC errors in Access seems painful.
Thanks for any suggestions you can offer.
Wayne
[Updated on: Thu, 26 April 2018 10:07] Report message to a moderator
|
|
|
|