This discussion is archived
5 Replies Latest reply: Feb 14, 2013 2:13 AM by 789736 RSS

form calling db procedure with commit

789736 Explorer
Currently Being Moderated
Hi everyone, Just a quick question to give me a clearer understanding about something. I was under the impression that if I had a form in which I made changes too and then on that form made a call to a database procedure which contained a commit that all the changes to the form would also be committed? I thought I would have to use PRAGMA AUTONOMOUS_TRANSACTION so as the database stuff would commit without committing the stuff on the form. However, In a quick test today I did exactly this and whilst the stuff in the database procedure committed successfully the stuff on the form did not commit at all (which is what I wanted but didnt expect it to work this way). Is this the way it is suppose to work and my understanding of this has been wrong?

Thanks
  • 1. Re: form calling db procedure with commit
    HamidHelal Guru
    Currently Being Moderated
    786733 wrote:
    Hi everyone, Just a quick question to give me a clearer understanding about something. I was under the impression that if I had a form in which I made changes too and then on that form made a call to a database procedure which contained a commit that all the changes to the form would also be committed?
    Answer: NO

    For forms changes commit you may use
    commit_form;
    Hope this helps

    Hamid
  • 2. Re: form calling db procedure with commit
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,
    As indicated, the Forms changes will not be applied in the database until you explicitely use the COMMIT_FORM() built-in.

    Francois
  • 3. Re: form calling db procedure with commit
    789736 Explorer
    Currently Being Moderated
    Thanks for the responses, however I am now coming across a scenario which seems to back up what I originally thought. Basically in the form the user can upload a photo to a table called photo, when they do this but dont save it they can then press a button which calls a database procedure which then copies that photo to a temporary table called temp_photo and commits (the photo has not been saved at form level yet so there should be nothing in the photo table). When this commit in the database procedure is performed my photo on the form is also committed to the photo table as well as the copy being committed to the temp_photo table.

    Why is this happening?

    Thanks again for any help. I appreciate the time you take to help me out and im aware my questions may be silly but I just want to get a better understanding of this scenario.
  • 4. Re: form calling db procedure with commit
    Andreas Weiden Guru
    Currently Being Moderated
    If you issue a commit in a database procedure, all outstanding "changes" in the database will be committed. Means, if you issue an INSERT from forms (which i guess happens when uploading a photo), this insert sill also be committed when you do a database-commit. What is not committed are outstanding changes in the forms-blocks, which have not been already send to the database (e.g. using POST). So, a COMMIT in a db-procedure commits ALL db-changes, a COMMIT in the forms-side applies all block-changes to the db, then does a DB-commit.
  • 5. Re: form calling db procedure with commit
    789736 Explorer
    Currently Being Moderated
    Got it! thanks for the help :)

    Edited by: 786733 on 14-Feb-2013 02:12

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points