PL/SQL exception and Java programs

Assuming you can't change the PLSQL code, you'll have to monitor the table. And of course, that will only work if the error table stores some sort of session or use identifier.

java.sql.CallableStatement throws java.sql.SQLException. If your stored proc throws an exception, your Java code will know about it. The code that calls the stored proc will have to handle this exception.

You have a choice: you can either have the stored proc throw the exception or have the Java code check the return value and query the table for the exception if the error code is returned.

But the exception isn't "lost" either way. You get it from the JVM or the table.

I vote for the JVM because it's easier. Less PL/SQL code, less Java code.

"is getting persisted to a oracle table" You could create a trigger on that table that rejects the insert. For example, if the error table contains an 'ERROR_DESCRIPTION' column, you could have a BEFORE INSERT ON error_table FOR EACH ROW trigger which does a RAISE_APPLICATION_ERROR(-20001,:NEW.ERROR_DESCRIPTION)

When the PL/SQL code goes to log the error, that will fail with the replacement error and that will, if you are lucky, get propogated to the Java layer.

It is an ugly hack, but if you truly can't change the code, it may work.

Simply if you use a framework that supports aspects, it would be easy to make an aspect that checks for the exception in the appropriate table. If not, then you could write something similar to this code:

        ResultSet exRs = null;      

        try {
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            statement.execute(normalSql);
            exRs = statement.executeQuery(exceptionSql);
            exRs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        }

        if (null != exRs.getString(exceptionColumn))
            connection.commit();
        else
            connection.rollback();

Sorry I couldn't be more specific.