How to create arbitrary error with RDBMS DML

Background

• I am writing a Java application work with RDBMS.
• The application uses Oracle and PostgreSQL as datastore.

Problem

Now I am testing an error-handling routine for some SQLException. For example, I want to treat ORA-00001 (Unique constraint violation) or ORA-00054 (Failed to acquire an exclusive lock) as a recoverable error. On the other hand, I want to treat ORA-00060 (Deadlock) as a non-recoverable error.

Now I am trying to write JUnit test cases for this error-handling routine. To make the test cases as portable as possible, I am looking for a way to generate arbitrary errors (ORA-00001, ORA-00054, ...) with a simple DML like SELECT RAISE_ERROR(-54) FROM DUAL. Also, I want to avoid mocking SQLException itself in test cases, because the content of SQLException could be changed between the version of RDBMS.

Question

Is there any way to create arbitrary SQL errors with a DML in RDBMS (especially Oracle and PostgreSQL)? Or, is there any testing framework like DBUnit that has function to create arbitrary SQLException? It is desirable if it can occur an error without specific tables and records.

There is no portable way to artificially cause a given error in SQL, you'll have to write code for each database.

But I want to give you some general advice:

• Use the standardized SQLSTATE rather than a system specific error code like Oracle's ORA-xxxxx numbers. That should be portable.

  All RDBMS should have a way to retrieve the SQLSTATE for an error message.

• I don't know what you mean with a "recoverable error", but a deadlock is clearly not a persistent error - is you repeat the transaction, it will probably succeed.

  On the other hand, a constraint violation is a persistent error. Retry as often as you want, you will always get the same error. The only way to "recover" would be to either run a different statement or modify the database to avoid the conflict.