FRM-40510 : THE MYSTERIOUS ERROR MESSAGE

The error message FRM-40510: ORACLE error: unable to DELETE record

was very difficult to troubleshoot and fix. 

I remember I spent about 2 weeks trying to solve the annoying problem.

The error message FRM-40510 appeared in Oracle Forms 12c running on a Windows 2016 server.

The offending form consisted of a parent and a child database blocks.

As one added new rows in the parent block and new rows in the child block and then one pressed the button Save the new data was not saved onto the database.

The button Save seemed active as it was still waiting for data to be committed.

At that time I was working at a customer’s site upgrading an application from Oracle Forms 6i, Oracle database 8i, all running on Windows Server 2003 – 32 bits to Oracle Forms 12c, Oracle database 12c, all running on Windows Server 2016 – 64 bits.

The form that had the problem worked fine in the old version – Oracle Forms 6i.  Adding new rows to the parent block and new rows to the child block and then saved the data worked fine and the new data was indeed committed to the database.

Several debug techniques were used in order to pin point the problem.

Here I will describe one useful technique:

Logged on the database, that contains the application data, as the same database user that owns the data displayed on Oracle Forms.

The following code was run in order to create an auxiliary table, sequence and procedure:

CREATE TABLE log_forms_trigger (event_id     NUMBER
                               ,forms_name   VARCHAR2(100)
                               ,trigger_name VARCHAR2(100)
                               ,action_name  VARCHAR2(200)
                               ,fired_time   TIMESTAMP);

CREATE SEQUENCE log_forms_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE OR REPLACE PROCEDURE 
log_trigger_action (p_trigger_name   VARCHAR2
                   ,p_trigger_action VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_id_seq     NUMBER;
v_forms_name VARCHAR2(100) := 'OFFENDING_FORM';

BEGIN
  v_id_seq := log_forms_seq.NEXTVAL;
  INSERT INTO log_forms_trigger (event_id,forms_name,trigger_name, action_name,fired_time) 
  VALUES (v_id_seq, v_forms_name, p_trigger_name, p_trigger_action, systimestamp); 
  COMMIT;
END;
/

Then I added calls to the stored procedure created above log_trigger_action in several triggers in Oracle Forms 12c.

I will not describe any trigger.  Usually there are lots of triggers in a forms application and a typical call to the stored procedure looks like this:

Trigger: WHEN-NEW-BLOCK-INSTANCE

log_trigger_action (‘WHEN-NEW-BLOCK-INSTANCE’, ‘Start’);
log_trigger_action (‘WHEN-NEW-BLOCK-INSTANCE’, ‘Current Block :'||:system.current_block’);
log_trigger_action (‘WHEN-NEW-BLOCK-INSTANCE’, ‘Current Item :'||:system.current_item’);
log_trigger_action (‘WHEN-NEW-BLOCK-INSTANCE’, ‘End’);

The code above is just an example, but the point here is that as the form is run the table log_forms_trigger will be populated.  One row for each call to the stored procedure log_trigger_action.

Just logged on the database and run the query:

SELECT * FROM log_forms_trigger ORDER BY fired_time;

In this way one can check the order of firing of the triggers and maybe identify where the flow stops.

Of course it is a boring task to add calls to the stored procedure from any trigger, but one may limit the number of triggers to analyze by focusing on the form blocks involved.

The error message FRM-40510 appeared on the table log_forms_trigger right after a block level trigger ON-UPDATE was finished.

Apparently the following code caused the error message FRM-40510:

DELETE FROM parent_table WHERE rowid=:1;

The code line above was not explicitly generated from the Forms code.  It seems that it was generated dynamically.

After a lot of troubleshooting I realized that setting the property Deleted allowed to No on the parent data block was a promising solution.

But other issues appeared and then I considered creating a new trigger on Forms level, ON-DELETE.  Then I tested the following code on the trigger ON-DELETE:

log_trigger_action ('ON-DELETE', 'Start');
log_trigger_action ('ON-DELETE','Current Block :'||:system.current_block);
log_trigger_action ('ON-DELETE','Current Item  :'||:system.current_item);
log_trigger_action ('ON-DELETE', 'End');

The code above does nothing else than inserting 4 rows on the table log_forms_trigger.

In other words the trigger ON-DELETE will contain just the line code NULL; after the calls to log_trigger_action are commented out or removed from the trigger.

Then the new data finally got saved to the database.

The trigger ON-DELETE was not present in the old Oracle Forms 6i application and had to be created in Oracle Forms 12c in order to work properly.

Leave a Reply

Your email address will not be published. Required fields are marked *