Home » SQL & PL/SQL » SQL & PL/SQL » Trap Constraint Errors in PL/SQL? (11g Release 11.2.0.3.0)
Trap Constraint Errors in PL/SQL? [message #669504] Thu, 26 April 2018 08:37 Go to next message
whdyck
Messages: 19
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 Go to previous messageGo to next message
cookiemonster
Messages: 13777
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 #669506 is a reply to message #669505] Thu, 26 April 2018 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
IMO, the effort will not be worth the gain, but it can be done "automatically" if every table had ERROR LOGGING feature enabled

https://docs.oracle.com/database/121/ADMIN/tables.htm#GUID-4E5F45D4-DA96-48AE-A3DD-7AC5C1C11076
Re: Trap Constraint Errors in PL/SQL? [message #669507 is a reply to message #669506] Thu, 26 April 2018 09:18 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
You make it sound like it's just a property of the table.
You need an error logging table for each table you want to do this for.
You also need to add an log errors clause to the DML.
Re: Trap Constraint Errors in PL/SQL? [message #669509 is a reply to message #669507] Thu, 26 April 2018 10:06 Go to previous messageGo to next message
whdyck
Messages: 19
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

Re: Trap Constraint Errors in PL/SQL? [message #669510 is a reply to message #669504] Thu, 26 April 2018 10:10 Go to previous message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Depending on what you want to log, activating AUDIT WHENEVER NOT SUCCESSFUL on the table(s) may be sufficient without changing anything in your code.

Previous Topic: Table select show blank
Next Topic: Executing the run time file at SQLPLUS
Goto Forum:
  


Current Time: Sun Jan 19 21:52:50 CST 2020