Solved

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

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

758 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

21 Experts available now in Live!

Get 1:1 Help Now