1

Our application is based on Java, JPA and an Oracle database. If a database operation fails, I get a SQLException or one of it's derivates.

In my case I'd like to distinguish, whether a SQLIntegrityConstraintViolationException has been caused by a problem with a unique or a foreign key constraint.

The log shows the error code ORA-00001 for the unique constraint violation, however, I was looking for a list of the other codes in order to reacte to the right ones.

I'm aware that these codes are vendor specific and SO has an easy to find answer for MySQL. I would need the same for Oracle.

Community
  • 1
  • 1
Louise
  • 1,451
  • 1
  • 18
  • 40

2 Answers2

2

Not surprisingly, there's a list in the OracleDOcumentation:

http://docs.oracle.com/cd/B28359_01/server.111/b28278/toc.htm

but digging in from the top it's also not so easy to find. Since MySql-Answers were so much easier to find, I hope, this Q&A serves to provide an equally quick path to this information.

Unfortunately, one cant't easily search inside the list because it has been spread across multiple pages without much indication on which range contains what kinds of errors.

However, I managed to find the codes for my specific problem: UQ violation is ORA-00001, where as the foreign key errors are found under ORA-02290 (missing parent) and ORA-02291 (attempted delete with dependant children existing).

Louise
  • 1,451
  • 1
  • 18
  • 40
1

This problem might be even broader. Error handling is vendor specific and JDBC does not handle vendor specific stuff.

For example JBOSS has class in it called: ExceptionSorter see OracleExceptionSorter. This class lists all the critical error codes describing the situation that the error is on "connection level". So the connection has to be closed and (after some time) a new one has to be created.

Then there are errors on "transaction level". Obviously it is a deadlock "ORA-0060" and then some crazy error returned by concurrent "MERGE" statements. These error can be usually solved by rollback a re-trying the whole transaction.

The rest of the errors are usually related to data integrity, and these can not be re-tried.

I'm not aware of any exception sorted implementation, which covers all classes of error. Maybe it is not even possible to implement, as it can depend on application needs.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • I was never looking for an implementation that covers all cases. Such implementation would indeed be tiresome and hard to maintain properly, and no real use case comes to mind. Instead, I want to react to a specific error or a class of errors, that I can provide a recovery action for. All other errors are left alone and follow the normal path of propagation.For this filtering, knowing the error codes is essential. – Louise Dec 29 '15 at 10:43