[SOLVED] Catching RAISE_APPLICATION_ERROR with springframework SimpleJdbcCall

Catching RAISE_APPLICATION_ERROR with springframework SimpleJdbcCall

I have a PL/SQL procedure where I call

RAISE_APPLICATION_ERROR (-20001, 'Illegal id');

if something goes bad.

This seems to work, because the procedure quits and throws the error when it should.

I call this procedure using

simpleJdbcCall.execute(myargs)

But when I catch the exception, it is of type DataAccessException. The cause is ORA-01403: no data found

There doesn't seem to be a way for me to get the number or message from my RAISE_APPLICATION_ERROR call. How can I get these values?

Solution

We do this same thing in our application; however, ORA-20001 exceptions should come to Spring as UncategorizedSQLException. If you are seeing ORA-01403 then I suspect perhaps your procedure is failing in a different way: are you sure Spring is handing in the same arguments you are unit testing with?

What if you make your PL/SQL procedure do nothing other than raise a ORA-20001, do you still get ORA-01403 from Spring? Are you sure you are unit testing your PL/SQL and attaching your Java application to the same database?