Unable to update Oracle IOT table using jbdc updateRow method

Question

I have an Oracle 10gR2 database with an IOT table inside:

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

I am trying to update table values ​​with this Java code (version 1.6):

 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 raises a SQLException (ORA-01410: invalid ROWID). If I try to update a bunch of (regular) table - everything works.

I 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 found that the IOT and HEAP table has a different rowids format:

IOT Example * BAJzKgwCwRb +

HEAP Example AAAbgVAAJAAMyr8AAA

But I still do not know how to solve this problem. Do you have any ideas?

Answer 1

Can you get the extended SQL trace results of your query to see what JDBC does under the covers? I suspect he's trying to do

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

and ROWID means something completely different in Oracle IOT; this is not a fixed line address, but an assumption of the path to the line.

My recommendation on how to do this is to distribute the system-generated timestamp on all of your tables and use this to control concurrency instead of declaring an updatable record set - which will take and hold locks on each record in the record set.

Then your application will retrieve the rowset as usual, but invoke statements like:

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

to optimize the lock without saving.

Answer 2

It looks like your table really should not be IOT. I would advise you to recreate it as a regular table and add an index both by ID and by name. Same performance, same logic, not ROWID issue.