ansaurus

Question

JDBC result set from an Oracle PL/SQL stored procedure

Answer 1

+4 A:

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.

BalusC 2010-09-17 15:51:30

Thanks for your response. The mothod returns Resultset and is being used later after the finally block is executed which is not a good practice but worked with Oracle JDBC Driver version - "10.2.0.1.0". And this code is Jpublisher generated java code from the stored procedure. So does this mean we need to change the stored procedure.

Lalitha 2010-09-17 16:25:52

I am not sure what JPublisher is all doing, but passing around the `ResultSet` outside the methods like that is a bad idea. You don't need to change the stored procedure. You just need to map the `ResultSet` to a `List<Entity>` as demonstrated in the answer.

BalusC 2010-09-17 20:08:10

Answer 2

A:

Thanks for your response. The mothod returns Resultset and is being used later after the finally block is executed which is not a good practice but worked with Oracle JDBC Driver version - "10.2.0.1.0". And this code is Jpublisher generated java code from the stored procedure. So does this mean we need to change the stored procedure.

Lalitha 2010-09-17 16:32:53

ansaurus

tags:

views:

answers:

JDBC result set from an Oracle PL/SQL stored procedure

related questions