Can't update Oracle IOT table with jbdc updateRow method

Question

Hello.

I have Oracle 10gR2 database with IOT table within:

create table countries (
 id number primary key, 
 name varchar2(30) not null enable
) organization index;

I try to update table values with this Java (version 1.6) code:

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                  ResultSet.CONCUR_UPDATABLE);

ResultSet src = stmt.executeQuery("select id, name from countries");

src.next();

src.updateString("name", "__test__");
src.updateRow();

But updateRow throws SQLException (ORA-01410: invalid ROWID). If I try to update a heap (ordinary) table - all works.

I have use this code with different versions of oracle drivers (from here http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html)

After some research I have detected that IOT and HEAP table have different format of rowids:

IOT example *BAJzKgwCwRb+

HEAP example AAAbgVAAJAAMyr8AAA

But I still don't know how to solve this problem. Are you have any ideas?

Answer 1

Can you get the results of an extended SQL trace of your query to see what JDBC's doing under the covers? I suspect it's attempting to do

UPDATE COUNTRIES SET NAME = '__TEST__' WHERE ROWID = :rowid_fetched

and ROWID means something totally different in Oracle IOT's; it's not the immutable address of a row, but a guess as to the path to the row.

My recommendation as to how to do this is to propagate a system-generated timestamp field onto all of your tables, and use that for concurrency control rather than declaring an updatable recordset -- which will take and hold locks for every record in the recordset.

Then your application would fetch the rowset as normal, but issue statements like:

UPDATE COUNTRIES SET NAME = '__TEST__' WHERE MOD_TS = :mod_ts_fetched

to give stateless optimistic locking.

Answer 2

Looks like your table does not really need to be IOT. I would suggest you recreate it as a normal table and add index on both ID and name. Same performance, same logic, no ROWID problem.