Q: detect uncommited changes
Date: 31 Jul 2008 12:47:54 -0800
"Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production" and forms (fmb) that come with the that version of developer.
Say I have a form A that calls another form B and that the called form B may post data without committing it, and then exit without rolling back. The caller (A) will see the changes (i.e. if it selects the data it gets the updated values) and can then decide to commit them or rollback as it chooses. That is all pretty standard stuff.
In the case that the called form (B) has committed its changes then the caller (A) can detect that with 100% reliability by examining dbms_transaction. local_transaction_id. If the id is null then there are no outstanding changes that may need to be committed. (And hence no need to prompt the user to save any changes).
The exact reverse also works reliably. If the called form (B) POSTs data without a commit then local_transaction_id is always non-null. The caller form (A) will always detect that a change was made, and that the user needs to be prompted to commit those changes before it exits.
The problem I am examing is that the local_transaction_id will sometimes be non-null even though the called form (B) did not post any changes at all. In that case the caller form (A) will prompt the user to save the changes, but there were no changes made. It is "safe" in the sense that the user will never lose any data by committing the session, but confusing as they may have made no changes in either form.
I am looking for the best way for the caller form (A) to detect that the called form (B) really did post data to the database.
Suggestions welcome. Received on Thu Jul 31 2008 - 15:47:54 CDT