OCI 8 - problem selecting/updating

Hi

I am having a problem running consecutive select/update/select/update/etc statements in my OCI application.

I am given these statements by an SQL engine which breaks down one update statements in to this series so that each update is only updating one row and then the select is finding the next row to update and so on. I have no control over this and cannot change it.

However, the 2nd and subsequent selects do not appear to be picking up the changes that the update made. Using OCI_COMMIT_ON_SUCCESS makes no difference.

Queries that work:
update tablex set col2 = y where col1 = z
(where col1 is the primary key and hence unique)

Queries that partially work:
update tablex set col2 = y where col2 = z
(only the first incidence of col2 = z is updated)

Queries that don't work at all:
update tablex set col2 = y where col1 = z
(where there is no key/index on the table - OCIStmtPrepare &
OCIStmtExecute are successful but I never see the change).

I appreciate that this is convoluted and I am going to try to replicate this is sample code BUT if anyone knows something extra about update statements in OCI I'd love to hear of it.

TIA
Alex Received on Fri Jun 07 2002 - 11:57:34 CEST