Re: Q: detect uncommited changes

joel garry (joel-garry_at_home.com) wrote:
: On Jul 31, 1:47=A0pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > "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. =
: =A0
: > 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. =A0That 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. =A0If the id is null then there a=
: re
: > no outstanding changes that may need to be committed. =A0(And hence no ne=
: ed
: > to prompt the user to save any changes).
: >
: > The exact reverse also works reliably. =A0If the called form (B) POSTs da=
: ta
: > without a commit then local_transaction_id is always non-null. =A0The cal=
: ler
: > 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. =A0It is "safe" in the sense tha=
: t
: > the user will never lose any data by committing the session, but confusin=
: g
: > 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.

: Well, I have no idea about this stuff, but just looking at the docs I
: think your assumptions may be off.

: A transaction starts when the first executable SQL is encountered. So
: I would hope that local_transaction_id would be populated at that
: time.

Yes, as soon as any form writes anything to the database then the id is non-null. After a commit the value is null again. selects don't effect it.

If the user edits the data in the form, but before the form writes the changes to the database then the id is null, but status of the form itself can be checked.

So, if the user has no unsaved edits in the form, and if the database has no local_transaction_id, then the application can safely exit without prompting the user to save their changes (since there can't be any).

 Apologies if I don't understand what that sp is about, and
: double apologies for not knowing about forms since 2.3 that I can't
: even remember, but see http://asktom.oracle.com/pls/asktom/f?p=3D100:11:0::=
: ::P11_QUESTION_ID:509146277753
I will review that, thanks. Received on Thu Jul 31 2008 - 20:08:38 CDT