Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to obtain updateable ResultSet via call to PL/SQL function

843859Jul 22 2005 — edited Jul 22 2005
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 19 2005
Added on Jul 22 2005
1 comment
143 views