Can I update the result (sys_refcursor) of an Oracle stored procedure (in Java)?

Question

Given the following function:

create or replace FUNCTION "GETADDRESSES"
    RETURN sys_refcursor
IS
    address_cursor sys_refcursor;
BEGIN
    OPEN address_cursor FOR 
        SELECT * FROM Address;
    RETURN address_cursor;
END;

I would like to be able to make changes to this result set in Java and post the changes back to the database. It is called in Java with the following:

        String genericQuery = "{ call ? := getAddresses() }";
        CallableStatement stmt = connection.prepareCall(genericQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

       stmt.registerOutParameter(1, OracleTypes.CURSOR);
       stmt.execute();
       ResultSet rs = (ResultSet)stmt.getObject(1);
       while (rs.next())
       {
           System.out.println (rs.getString(2)); 
           rs.updateString(2, "*" + rs.getString(2));
       }
       stmt.close();

which raises an exception "Invalid operation for read only resultset". Is there a way to return this cursor as something which can be updated and posted back to the db from Java? I am using Oracle 10g.

Thanks, Rob

Answer 1

The problem is that when you are specifying CONCUR_UPDATABLE, that applies to the ResultSet of the function call itself -- if there was one, which there isn't. The ResultSet object that you are getting via getObject() isn't the ResultSet of the CallableStatement, so it is by default not updatable.

My first thought was that adding FOR UPDATE to the query in the function might make the ResultSet updatable, but no luck.

I can't see any way to communicate to JDBC that the result set should be updatable in this case.

Answer 2

Ref cursors are not updatable. You would have to either perform the query directly instead of using the stored procedure or perform the updates using a separate JDBC statement.