Solved

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

Posted on 2006-10-25
4
5,159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

752 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