How to handle cursor if it throws exception in EJBs

Question

I have a callableStatment, upon execution I get the below error

    try{
callableStatement.executeQuery()
}
    catch (Throwable t) {
                throw new Exception(t);

            } 
            finally {
                closeResultSet(rs);
                closeCallableStatement(callableStatement);
                closeConnection(dbConnection);

            }
javax.ejb.EJBTransactionRolledbackException: ORA-30926: unable to get a stable set of rows in the source tables

After some time I get Database Error of "Too man open cursors"

I know that the procedure is throwing the error due to duplication, I want to know why the Cursors aren't closed as it seems finally block is not doing what I need and what is the proper way to close them

Answer 1

I think you are executing a merge query and there is more than 1 row id returned. And this is the reason for that exception. Also when the first duplicate row is returned, there itself the exception is thrown. And before the query is even executed you are trying to close it. That should be the reason for the cursor not closing properly. Try adding a distinct keyword in your query to avoid duplicates. Eg: MERGE INTO table_1 a USING (SELECT distinct ta.ROWID row_id FROM table_1 a ,table_2 b ,table_3 c WHERE ) src ON ( a.ROWID = src.row_id ) WHEN MATCHED THEN ;As there is a rollback involved, it comes under System Exception. And Application Exceptions are not rolled back.