Q

Error trying to delete a particular record

I am trying to delete a particular record selected using a LOV.

This is the code that I am trying to use. It does not work 100%. The first record is deleted instead and the one I am trying to delete becomes the first record.

If I try to delete it again then I get an error message: FRM-40657 Record deleted by another user. Otherwise I get the error message FRM-40509 Unable to update record.

The records are being deleted from the V_BASE table even though I am getting these errors.


Code for Delete Button:

DECLARE
	
	ALERT_BUTTON NUMBER;
	
BEGIN

	ALERT_BUTTON:= SHOW_ALERT('DELETE_CAUTION_ALERT');
	
	IF ALERT_BUTTON=ALERT_BUTTON1 THEN
		DELETE FROM CUSTOMER
		WHERE :CUSTOMER.CUSTOMER_NO = CUSTOMER.CUSTOMER_NO;
		COMMIT;
		ALERT_BUTTON:= SHOW_ALERT('DELETE_SUCCESSFUL_ALERT');
		MESSAGE('RECORD HAS BEEN DELETED SUCCESSFULLY');
	ELSIF ALERT_BUTTON=ALERT_BUTTON2 THEN
		ROLLBACK;
		ALERT_BUTTON:= SHOW_ALERT('DELETE_CANCELLED_ALERT');
		MESSAGE('NO RECORDS HAVE BEEN DELETED');
	
	END IF;
END; 

Is the block you are deleting the record from a BASE table block or a non-V_BASE table block?

I get the feeling you are locking the record yourself based on the information you provided me.

Maybe you want to just say:

 IF ALERT_BUTTON=ALERT_BUTTON1 THEN
  delete_record;  -- A FORM'S API;
...

This should suffice.

Please note there is no NEED to do anything if the user hits cancel (i.e. no need to right code for ALERT_BUTTON2).

This was first published in June 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close