PL/SQL exception and Java programs

Question

Hi

Business logic is coded in pl/sql packages procedures and functions. Java programs call pl/sql packages procedures and functions to do database work.

pl/sql programs store exceptions into Oracle tables whenever an exception is raised.

How would my java programs get the exceptions since the exception instead of being propagated from pl/sql to java is getting persisted to a oracle table and the procs/functions just return 1 or 0.

Sorry folks i should have added this constraint much earlier and avoided this confusion. As with many legacy projects we don't have the freedom to modify the stored procedures.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

I think that if there any possibility to re factor your code - do it ASAP. Move business logic to java code and you loose your problem. Use db only as data layer. We have some legacy project with business layer in DB. Horror thing. Was re factored ASAP

Answer 5

"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.