I'm using JDBC and the Oracle JDBC driver to call a PL/SQL function that returns a SYS_REFCURSOR. I do this via a CallableStatement object and then cast the output parameter to a ResultSet object. However, I want this ResultSet object I end up with to be updateable. The following code throws an exception stating "Invalid operation for read only resultset: updateString ":
cstmt2 = con.prepareCall("{? = call get_upload_entry_for_update(?)}",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
cstmt2.registerOutParameter(1, OracleTypes.CURSOR);
cstmt2.setInt(2, newUploadId);
cstmt2.execute();
rs = (ResultSet) cstmt2.getObject(1);
rs.next();
rs.updateString("UPLOAD_FILENAME", fileName);
// . . .
So even though, I create the CallableStatement such that ResultSets should be updateable, it's not allowing me to do any updates. Also, in case you're wondering, inside the PL/SQL function, the query on which the cursor is based is a select statement which
does specify "for update" at the end.
I can get it to work as follows using a Statement object that executes the SELECT statement directly instead of a CallableStatement that executes a PL/SQL function:
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select UPLOAD_FILENAME, UPLOAD_FILE from rf_upload where upload_id = "+newUploadId+" for update");
rs.next();
rs.updateString("UPLOAD_FILENAME", fileName);
//. . .
Although this works, the project I'm working has a goal to encapsulate all SQL into Functions and Stored Procedures so I'd like to get it working that way instead.
So the bottom-line question is: Using the Oracle JDBC driver, how can I call a PL/SQL function in such a way that I can obtain an updateable ResultSet object?
Thanks for any suggestions. I'd be happy to clarify anything that's unclear.