CODEWITHSUNDEEP
javaoracle-databasejdbcoracle11g
Lalitha
Lalitha

Reputation: 21

JDBC result set from an Oracle PL/SQL stored procedure

What does oracleClose() and oracleCloseQuery() do in sqlj.runtime.ExecutionContext.OracleContext.

Since we upgraded jdbc driver jar to ojdbc5.jar with the oracleClose() in the finally block we get the below exception when using resultset.next() and not with oracleCloseQuery(). Is it safe to use oracleCloseQuery(). The database is Oracle 11g and WAS 6.1.X.X. Appreciate your response. Here is the error message :

java.sql.SQLException: Closed Statement: next at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269) at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:205) at com.westgroup.pubsvc.rms.models.ResultSetSRC.getNextResult(ResultSetSRC.java:112)

Upvotes: 2

Views: 2877

Answers (1)

BalusC
BalusC

Reputation: 1108702

The exception is telling you that the Statement which has returned this ResultSet is been closed while you're attempting to iterate over the ResultSet. This indicates that you're using ResultSet outside the try block where the Statement is been executed and that you're probably using the ResultSet as return value of the method. This is a bad practice.

I'd suggest you to rewrite your JDBC code so that the ResultSet is been processed in the very same try block as the Statement is been executed, or that the methods returns something like as List<Entity> instead of a ResultSet.

Here's a kickoff example of the correct JDBC idiom:

public List<Entity> list() throws SQLException {
    // Declare resources.
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<Entity> entities = new ArrayList<Entity>();

    try {
        // Acquire resources.
        connection = database.getConnection();
        statement = connection.createStatement("SELECT id, name, value FROM entity");
        resultSet = statement.executeQuery();

        // Gather data.
        while (resultSet.next()) {
            Entity entity = new Entity(); 
            entity.setId(resultSet.getLong("id"));
            entity.setName(resultSet.getString("name"));
            entity.setValue(resultSet.getInteger("value"));
            entities.add(entity);
        }
    } finally {
        // Close resources in reversed order.
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    // Return data.
    return entities;
}

By the way, you don't need Oracle JDBC driver specific classes/methods here. It's all just java.sql.*. This way you keep the JDBC code portable among databases.

Upvotes: 6

Related Questions