Since 9i the possibility to get an ORA-1555 is less compared to previous versions. But still this is possible due to:
- an undo tablespace that is too small or
- an application that keeps a cursor open for a too long time.
The last issue can be solved by:
- add an "as prepared set" to the main 4 GL query when this query has no "for update" clause.
- break the main query after processing e.g. 10.000 rows if the main query has a "for update" clause and restart the main query after these 10.000 rows. An example of the latest could be:
completed = false
repeat
db.retry.point()
counter = 0
select *
from <table> for update
|* Read from previous point onwards
where table._index1 > {:hold.field1}
|* Sort by on index to prevent problems
order by table._index1 with retry repeat last row
|* Speed up transfer of data
hint use array fetching
and buffer 100 rows
and array size 100
selectdo
update.other.tables()
|* Commit once a 50 rows
counter = counter + 1
if counter\50 = 0 then
commit.transaction()
|* When query is open longer than 10.000
|* records, than restart query
if counter >= 10000 then
|* Set all index 1 fields
hold.field1 = table.field1
break
endif
endif
selectempty
completed = true
selecteos
commit.transaction()
completed = true
endselect
|* Only complete when complete flag has been set
until completed
Hope this helps,
Dick