Best way to return "expected" Oracle exceptions to Java Groovy/Grails

Question

Background:

In my Oracle database, I have plenty of database calls which can cause exceptions. I currently have exception handlers for all these, which call an error package. To cut a long story short, a raise_application_error is eventually raised, for expected errors, or a raise for unexpected errors, and this is sent back to the calling Java Groovy/Grails application layer.

So, for example, if a user enters an id and clicks search, I run a select query from the database. If the id doesn't exist, I have a NO_DATA_FOUND exception which performs a raise_application_error with a custom error message (i.e. "ID entered cannot be found.")

However, the application development team say they're struggling with this. They are trying to perform unit testing in Groovy and ideally want a variable returned. The SQL exceptions I am currently returning cause all tests to fail as it is an exception. Their code looks like this:

void nameOfProcedure() {
    String result = storedProcedure.callDBProcedure(ConnectionType.MSSQL, val1, val2)
    log.info "SQL Procedure query result value: "+ result
    assertEquals("1", result)
}

They can add something like this above the test:

@Test (expected = SQLException.class)

But this means all returning SQLExceptions will pass, regardless of whether they are the right exceptions for the issue at hand.

Question:

What is the best solution to this issue? I'm being pressed to return variables from my exception blocks, rather than raise_application_errors - but I'm very reluctant to do this, as I've always been told this is simply terrible practice. Alternatively, they could make changes on their end, but are obviously reluctant to.

What's the next step? Should I be coding to return "expected" errors as variables, as opposed to exceptions? For example, if someone enters an ID that isn't found:

BEGIN
  SELECT id
  FROM   table
  WHERE  id = entered_id
EXCEPTION
WHEN NO DATA FOUND THEN
  RETURN 'ID cannot be found';
END

Or alternatively, should they be following a guide like this which advises using Hamcrest matchers to create their own custom exception property, which they can check against in their JUnit testing. What is best practice here?

Answer 1

You're right, it's terrible practice. It just 'wagging the dog'; they're being lazy to work good and wish you to spoil application design in order to please them.

Generally, unit test with exception returned should looks something like this:

try {
  String result = callDBProcedure();
  fail("Result instead of exception");}
catch (OracleSQLException e) {
  assertEquals(e.errorCode, RAISE_APPLICATION_ERROR_CODE);}
catch (Throwable t) {
  fail("Unexpected error");
}

They can upgrade this as they wish. For example, they can develop procedure 'call the SP and convert exception to anything they wish' and use it in their tests. But they should not affect application design outside testing. Never.