Updatable oracleDataSet with Views - no rowid

[tseck]

Hello,

we use DOA 4.0.7 - having the folowing problem:

Normally we use datasets to allow changes to data in a table, i.e. t1 with columns id, a, b, c, d.

The sql of the dataset is 'select id, a, b, c, d, rowid from t1'. We use autorefresh, cacheupdates, etc.

Now datamodel changes - i.e. instead of table t1 the data migrates to 2 new tables t2 and t3, with an fk in t2:

t2(id, a, b)
t3(id, t2_id, c, d)

I built a view t1 (i.e. select t2.id, t2.a, t2.b, t3.c, t3.d from t2, t3 where t2.id=t3.t2_id) that joins t2 and t3 and has the same columns as the previous table t1. Some instead-of-triggers.handle insert/updates/deletes.

But: dataset is not updatable, if the rowid-column is ommitted in the sql.

And: there is no rowid and refresh queries like 'select id, a, b, c, d, rowid from t1 where rowid=:doa_rowid' raise an ora-14410 invalid rowid.

So is there a general way, to modify the dataset-objects in such a way, that the dataset still is updatable and that cached-updates and so on still work?

I know the pk-columns, i know that triggers will translate dml, but doa seems to need the rowids to identify the dataset as writeable.

Regards,
Thomas

 

[Marco Kalter]

If your view does not have a rowid at all, you will need to handle all DML actions through an OnApplyRecord event handler.