Can I update the result (sys_refcursor) of an Oracle stored procedure (in Java)?
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 push 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 the "Invalid operation for read-only results" exception. Is there a way to return this cursor as something that can be updated and sent back to the db from Java? I am using Oracle 10g.
Thanks Rob
source to share
The problem is that when you specify CONCUR_UPDATABLE, it refers to the ResultSet of the function call itself - if one exists and which does not. The ResultSet that you get via getObject () is not the ResultSet for the CallableStatement, so it is not updated by default.
My first thought was that adding FOR UPDATE to the query in a function could make the ResultSet updatable, but no luck.
I don't see any way to communicate with JDBC so that the result set should be updatable in this case.
source to share
Reference cursors cannot be updated. You will have to either execute the query directly, the stored procedure, or perform the updates using a separate JDBC statement.
source to share