This discussion is archived
8 Replies Latest reply: Mar 21, 2013 1:55 AM by Sachinmrt RSS

I have confusion on form commit

Sachinmrt Explorer
Currently Being Moderated
Dear All,

I want to know that when i use commit_form then form commit but what happened when any error message come??

In my programe many procedure written they are trying to insert the record in another table. When any error occured then insert statement commit. I think when any error occured then whole programe rollback automatically but my form doesn't the same.

Please help me. I am confused why data is commit.


Oracle AS 10g.
Data Base 10g.
  • 1. Re: I have confusion on form commit
    HamidHelal Guru
    Currently Being Moderated
    Sachinmrt wrote:
    Dear All,

    I want to know that when i use commit_form then form commit but what happened when any error message come??
    Commit_form commit or finilaize all the transaction or change in the forms..
    Oracle Forms first validates the form, then, for each block in the form, deletes, inserts, and updates to the database, and performs a database commit.
    if forms raise any error commit doesn't occur.
    In my programe many procedure written they are trying to insert the record in another table. When any error occured then insert statement commit. I think when any error occured then whole programe rollback automatically but my form doesn't the same.
    where is the procedure stay? at database server or at forms procedure ? Check is there any commit command in the procedure..

    Hope this helps

    Hamid
  • 2. Re: I have confusion on form commit
    GregorM Explorer
    Currently Being Moderated
    Hi.

    Basically commit_form (looking from database perspective) does commit on database. What you are describing here makes no sense. Can you clarify what type of an error do you get (FRM, ORA...)? What do you mean by " trying to insert the record in another table"?

    If you try this example (on scott.dept table) from sqlplus :
    -- insert OK
    insert into dept values (11,'TEST','NY');
    -- insert with error
    insert into dept values ('AA','TEST','NY');
    commit;

    After doing query on scott.dept table you will see dept 11 was inserted and commited. In this scenario there were two transactions (each insert it's own transaction) so it is logical the first insert was commited. My point here is you have to understand transactions.

    Explaining you about transactions is only my guess what is bothering you as I do not quite understand your problem.

    Best regards
  • 3. Re: I have confusion on form commit
    Sachinmrt Explorer
    Currently Being Moderated
    Sir,
    where is the procedure stay? at database server or at forms procedure ? Check is there any commit command in the procedure..
    My procedure stay on form and check there is no another commit or commit_form;

    sir form already save. now i click on authorized button and this button have following code
    IF :MS_BILL_PASS_HD.STATUS  = 'E' THEN
          alertid := FIND_ALERT('ENTER');
          BUTTON := SHOW_ALERT(alertid);
         IF ALERT_BUTTON1 = button THEN               
                   IF NOT FUNC_FA_VC THEN
                        :MESSAGE_LINE := 'Error occured while updating voucher header';
                        raise form_trigger_failure;
                   END IF;           
                   :MS_BILL_PASS_HD.STATUS  := 'A';
                   :MS_BILL_PASS_HD.APPROVED_BY := :GLOBAL.USER_CODE;
                   :MS_BILL_PASS_HD.approved_date:=sysdate;
    
         ELSIF ALERT_BUTTON2 = button THEN
                    :MS_BILL_PASS_HD.STATUS  := 'C';
         ELSIF ALERT_BUTTON3 = button THEN
                    NULL;  
           END IF;
    END IF;
    
    IF FORM_SUCCESS THEN
              MESSAGE('voucher generated');
              MESSAGE('');     
              COMMIT_FORM;
    ELSE     
              MESSAGE('voucher not generated');
              MESSAGE('');
              RAISE FORM_TRIGGER_FAILURE;
    END IF;
    in this code i use form function FUNC_FA_VC and this function code is
    FUNCTION func_fa_vc RETURN boolean IS
    begin
         insert into fa_vc_m () values ();
         insert into fa_vc_d1 ()     values ();          
         insert into fa_vc_d1 ()     values ();
              
    return(true);
    EXCEPTION
    WHEN OTHERS THEN
    MESSAGE(SQLERRM);PAUSE;
    return(FALSE);
    END;
    My problem occur when two user open the form and same data and try to authorized it.
    User One and two both show the alert and select the button1 now user1 voucher generate and second user got the form error code-
     FRM-40654 Record has been updated by another user.Re-query to see change.
    means second user data not saved but after this error i got the same msg voucher generated.
    and when i check in voucher table then really data insert two times.

    Now I confused why data saved.

    Please help me sir.
  • 4. Re: I have confusion on form commit
    HamidHelal Guru
    Currently Being Moderated
    >

    means second user data not saved but after this error i got the same msg voucher generated.
    and when i check in voucher table then really data insert two times.

    Now I confused why data saved.
    What you are getting is true.. now why true ?

    Say our example data is AA. when user one and two hit on AA, time is calculate in fraction and any one user would be the first and he/she locked the data and second user get the message.

    When lock is free from winner user (who made the lock) rest user command is executed. And you will get two record..
    My problem occur when two user open the form and same data and try to authorized it.
    User One and two both show the alert and select the button1 now user1 voucher generate and second user got the form error code-
    FRM-40654 Record has been updated by another user.Re-query to see change.
    this cause.. your procedure try to changes data in the current form block. for example
    ...........
                   :MS_BILL_PASS_HD.STATUS  := 'A';
                   :MS_BILL_PASS_HD.APPROVED_BY := :GLOBAL.USER_CODE;
                   :MS_BILL_PASS_HD.approved_date:=sysdate;
    ................
    when first/ winner user changes the above and commit the forms data shows to 2nd user isn't correct and forms through the message to re-query to corrent the state of current forms data.


    What can you do ?
    Before generating the data check of it's exist and if exist then return message already done... else go...




    Hope this helps


    Hamid


    Mark correct/helpful to help others to get right answer(s).*

    Edited by: HamidHelal on Mar 20, 2013 4:45 PM

    Edited by: HamidHelal on Mar 20, 2013 5:05 PM [ try gregor suggestion, would be better one ]
  • 5. Re: I have confusion on form commit
    GregorM Explorer
    Currently Being Moderated
    Hi.

    This makes more sense now. Error FRM-40654 is related to the database block on the form. I suppose you have some database blocks on your form. What commit_form() does is to try to commit changed records from database blocks. So indeed you have two distinct transactions here. One is "manual" insert you mentioned and the second is the transaction executed automatically based on form's database blocks.

    I think in the situation like this you will have to do it yourself to emulate pessimistic locking. Problem here is the insert statements (just in rare cases) does not produce locks. So the simplest thing I can think of right now is to immediately lock queryed record so no two users can authorize simultaniously. Try to set property "Locking Mode" to Immediate on your database block. This solution is a little tricky as you have to understand that as soon the record is queried by user1 none else can change it until user1 releases lock. To fully implement the solution I think you would be better of doing your own logic.


    Best regards.

    Edited by: gregor13 on Mar 20, 2013 11:30 AM
  • 6. Re: I have confusion on form commit
    GregorM Explorer
    Currently Being Moderated
    Hi.

    Hamid I must disagree with you the solution you are proposing is 100% error prone. The chances of duplication are minimized but in some situations could still happen. For example :
    1. user one presses the button -- transaction for user 1 starts
    2. user two presses the button a few milliseconds after user 1 -- transaction for user 2 starts
    3. the transaction of user 1 is not yet commited as the transaction of user 2 is in the stage of checking of the record existance - result is "does not exists as the transaction from user 1 is not commited yet"
    4. now transaction of user 1 is commited
    5. transaction of user 2 also gets commited

    So to summarize the longer the transaction the more chances are the same thing will happen. Correct me if I am wrong.

    To extend this a little bit further I would suggest you reorganize authorization table(s) so there is a unique authorization identifier which is also set as unique key on database. This way the database it's self would guaranteed no two inserts with the same values of unique key could be inserted. That way there is no chance the second user would succeeded inserting values.

    Best regards.

    Edited by: gregor13 on Mar 20, 2013 11:52 AM

    Edited by: gregor13 on Mar 20, 2013 2:01 PM
  • 7. Re: I have confusion on form commit
    HamidHelal Guru
    Currently Being Moderated
    gregor13 wrote:
    Hi.

    Hamid I must disagree with you the solution you are proposing is 100% error prone. The chances of duplication are minimized but in some situations could still happen. For example :
    1. user one presses the button -- transaction for user 1 starts
    2. user two presses the button a few milliseconds after user 1 -- transaction for user 2 starts
    3. the transaction of user 1 is not yet commited as the transaction of user 2 is in the stage of checking of the record existance - result is "does not exists as the transaction from user 2 is not commited yet"
    4. now transaction of user 1 is commited
    5. transaction of user 2 also gets commited

    So to summarize the longer the transaction the more chances are the same thing will happen. Correct me if I am wrong.
    If there is something unique in then insertion, then this will not happen.
    To extend this a little bit further I would suggest you reorganize authorization table(s) so there is a unique authorization identifier which is also set as unique key on database. This way the database it's self would guaranteed no two inserts with the same values of unique key could be inserted. That way there is no chance the second user would succeeded inserting values.
    Right ..
  • 8. Re: I have confusion on form commit
    Sachinmrt Explorer
    Currently Being Moderated
    To extend this a little bit further I would suggest you reorganize authorization table(s) so there is a unique authorization identifier which is also set as unique key on database. This way the database it's self would guaranteed no two inserts with the same values of unique key could be inserted. That way there is no chance the second user would succeeded inserting values.
    Thanks gregor sir, I set a unique key on database. Now duplicate voucher not inserted two times.

    Once again thanks sir, and also thanks to HamidHelal for support.

Legend

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