Solved

"FRM:40501: ORACLE ERROR : Unable to reserve record for update or delete"

Posted on 2006-10-25
4
5,113 Views
Last Modified: 2012-08-13
I have written in the following code in custom.pll ---

 BEGIN    
          If (form_name = 'OKEKAUWB') Then
           If (event_name = 'WHEN-NEW-BLOCK-INSTANCE') Then
                If (block_name = 'K_HEADER') Then
                    -- fnd_message.set_string('Change Request Status: '||l_chg_req_num||' '||g_chg_status);
                 --  fnd_message.show;
                BEGIN
                      
                      l_contract_id := To_Number(Name_In('K_HEADER.K_HEADER_ID'));
                      l_contract_vers := To_Number(Name_In('K_HEADER.MAJOR_VERSION'));
                      l_last_form     := To_Number(name_in('system.last_form'));
                 
                  SELECT ocr.chg_request_num, ocr.chg_status_code
                  INTO   l_chg_req_num, l_chg_status
                  FROM   OKE.oke_chg_requests ocr
                  WHERE  ocr.k_header_id = l_contract_id
                  AND    ocr.chg_status_code = 'IN PROGRESS'
                  FOR UPDATE of ocr.chg_status_code NOWAIT;
                 
                     fnd_message.set_string('chg_request_num ->'||l_chg_req_num);
                     fnd_message.show;                   
                     
                      If ( l_chg_req_num IS NOT NULL AND l_chg_status = 'IN PROGRESS') Then
                             set_item_property('K_HEADER.K_VALUE',ENABLED,PROPERTY_FALSE);
                             fnd_message.set_string('Contract Value has been Disabled');
                                              fnd_message.show;
                      Else
                             set_item_property('K_HEADER.K_VALUE',ENABLED,PROPERTY_TRUE);
                              fnd_message.set_string('Contract Value has been Enabled');
                                              fnd_message.show;  
                      End If;
                      
          EXCEPTION
                 WHEN OTHERS THEN
                  FND_MESSAGE.SET_STRING('SQLMSG: '||SQLERRM );
                  FND_MESSAGE.SHOW;
                  RAISE FORM_TRIGGER_FAILURE;    
          END;      
            End If;
        End If;
    End If;      
    EXCEPTION
      WHEN OTHERS THEN
               FND_MESSAGE.SET_STRING('SQLMSG: '||SQLERRM );
               FND_MESSAGE.SHOW;
               RAISE FORM_TRIGGER_FAILURE;  
    END; --- WHEN-NEW-BLOCK-INSTANCE  

I am trying to update the same record on a standard Oracle APPS form called (OKECHGRQ) which is based on the table ( OKE.oke_chg_requests) which changes CHG_STATUS_CODE to "AMENDED", When I click button labelled "CHG STATUS", I am getting the Error "FRM:40501: Unable to reserve record for update or delete" .

I would greatly appreciate your quick response asap.

Thanks!!
Kalyan
0
Comment
Question by:Kalyan_Thalakoti
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17805004
FOR UPDATE of ocr.chg_status_code NOWAIT; -- why did you put this ?

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17805005
where is the update command in your code.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 17805503
Do you need this line in your query:
"FOR UPDATE of ocr.chg_status_code NOWAIT"?

I agree with nav_kum_v, there is no update in this procedure, so your question is somewhat confusing.

I am not familiar with Oracle Apps, but I am very familiar with Oracle Forms.  In Oracle Forms, this message usually means that another user or process has locked or changed the record, so it must be re-queried before this form can lock it.
0
 
LVL 6

Expert Comment

by:Ritesh_Garg
ID: 17807099
This trigger is getting fired at WHEN-NEW-BLOCK-INSTANCE.  It looks like you are just want to reserve/lock this record and display the message.

In Oracle Apps, there are program units to handle DML Operations (Lock / Insert / update / delete) and the program unit is a package with same name as data block name.
In order to reserve record, just copy value of any field into it.  E.g.
  copy('KHEADER.chg_status_code', 'KHEADER.chg_status_code');


Thanks,
Ritesh
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question