Solved

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

Posted on 2006-10-25
4
4,978 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now