This discussion is archived
7 Replies Latest reply: Nov 20, 2012 1:16 AM by hokoba RSS

FRM-40501

hokoba Newbie
Currently Being Moderated
Dear Techies,

I have a datablock D1 on a content canvas C1.When the form opens the execute_query is triggered. it fetches records from table T1. I have a button B1 in C1(content canvas) clicking on which I open a stacked canvas C2 in which I have my other Datablock D2.The execute query is triggered when it opens and it fetches records from Table T2.

When the content canvas opens I get Forms message asking "Could not reserve records(2 tries). Keep trying?" and I am forced to choose 'No' and 'Yes' option doesnt help anything. After that "Frm-40501:Oracle Error:Unable reserve record for Update or Delete" error is thrown.

I have the below entries in respective Datablock property palatte:
D1:
DML Data Target Type: Table
DML Data Target Name: T1
D2:
DML Data Target Type: Table
DML Data Target Name: T2

I see the oracle error when I press Shift+F1 "ora-00054 resource busy and acquire with nowait specified"

Please assist to overcome this error.

Thanks and Regards
Sri

Edited by: hokoba on Nov 18, 2012 3:01 AM

Edited by: hokoba on Nov 18, 2012 3:04 AM
  • 1. Re: FRM-40501
    yoonas Expert
    Currently Being Moderated
    Hi,

    See if some other session is trying to update the table for the block D1. Execute query will not normally give you this error. Are you doing any modification to the fields of the block in post query? If you do this way and you have two separate sessions of the same form you will have this error.

    Basically form is telling you that the record you are trying to update is already locked by someone else.


    Regards
    Yoonas

    Edited by: yoonus on Nov 18, 2012 11:06 AM
  • 2. Re: FRM-40501
    hokoba Newbie
    Currently Being Moderated
    Hi Yoonas,
    Thx for the reply. In post query I populate values in non-DB items and setting 'ENABLED' property to a few items.
    The purpose is to update a field but this error takes place even before I try to update.

    Regards
    Sri
  • 3. Re: FRM-40501
    yoonas Expert
    Currently Being Moderated
    Hi,

    How many sessions are open for the same form? open up a sqlplus session and try updating manually to find if its already locked.

    Even if you are setting a value of a text_item like D1.Sample_text = 'testin' this will happen.

    just for the purpose of testing can you just comment your post_query codes and run the form.


    Regards
    Yoonas
  • 4. Re: FRM-40501
    hokoba Newbie
    Currently Being Moderated
    Hi Yoonas,
    If I have more than one session, this issue appears and I tried in sqlplus I get "ORA-00054: resource busy and acquire with NOWAIT specified"

    Yes, in one place I assign a value to the block item and I commented that line and it works fine..But It is again a non-DB display item..In Property palatte the 'Database Item' is 'No' for this Datablock item. Even if I give commit_form, it is not fruitful . Is there any other way out?

    Regards
    Sri

    Edited by: hokoba on Nov 18, 2012 10:15 PM
  • 5. Re: FRM-40501
    yoonas Expert
    Currently Being Moderated
    Hi,

    Make sure you are setting the property of the item in question becuase the issue should not happen to a nondatabase item.


    Regards
    Yoonas
  • 6. Re: FRM-40501
    user346369 Expert
    Currently Being Moderated
    hokoba wrote:
    Yes, in one place I assign a value to the block item and I commented that line and it works fine..But It is again a non-DB display item..In Property palatte the 'Database Item' is 'No' for this Datablock item. Even if I give commit_form, it is not fruitful . Is there any other way out?
    If you change ANY item in your post-query trigger, even non-base-table items, it causes other triggers to run, specifically the When-Validate-Item on that particular item, and the When-Validate-Record on the row. If, in either one of those triggers, you set or change a value in a base-table column in the record, even setting a column value to null when it is already null, Forms automatic processing will mark the record as 'CHANGED', and lock the record.

    If you run a second instance of your form and query the same record, the locking process will encounter the locked record and you will encounter your problem situation.

    Go through all your trigger processing, and remove the code that sets base-table column values, ONLY setting them if the record status is already = 'CHANGED' or 'INSERT'.

    I have many forms with this code at the top of the When-Validate-Item and When-Validate-Record triggers:
    Begin
      If :System.Mode = 'QUERY'
      Or :System.Record_status = 'NEW' then
        Return; --exits the validation process and marks all affected fields as valid.
      End if;
      -- validation code below this
  • 7. Re: FRM-40501
    hokoba Newbie
    Currently Being Moderated
    Hi Steve & Yoonas,

    Thx for the reply. There is a DB column item in post-query which is set to null. I commented it and it works fine now. I applied steve's code in other WVI triggers.

    Cheers and Regards
    Sri

Legend

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