ORA-20010 Error When Updating Records
(Doc ID 1584573.1)
Last updated on AUGUST 27, 2019
Applies to:Oracle Application Express (APEX) - Version 4.2.2 and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Created a simple test report and form database application. When attempting to edit records, it tries to update the primary key ( for example: ID) column. The ID column is generated using a sequence and trigger. The records can be updated fine using another tool such as TOAD or SQL Plus.
The following error occurs when trying to edit and save a record:
ORA-20010: CANNOT UPDATE COLUMN ID IN TABLE ORGANIZATION AS IT USES SEQUENCE. ORA-06512: at "<SCHEMA>.<TRIGGER_NAME>", line 2 ORA-04088: error during execution of trigger 'J<SCHEMA>.<TRIGGER_NAME>'
This error is coming from an AFTER UPDATE trigger on the <COLUMN_ID> field:
CREATE OR REPLACE TRIGGER <SCHEMA>.<TRIGGER_NAME> AFTER UPDATE OF ID
ON <SCHEMA>.<TABLE> FOR EACH ROW
RAISE_APPLICATION_ERROR(-20010,'CANNOT UPDATE COLUMN ID IN TABLE <TABLE> AS IT USES SEQUENCE.');
This indicates that the ID field is being passed in the update statement. The ID column should not be included in the update statement since the ID field has been flagged as hidden. The user can't even see, much less edit, it in the form. All the form allows them to do is edit the organization name. So would expect the SQL to be something like update organization set name ='user supplied value' where id = 7;
Can add, read and delete records in APEX. Can update records in TOAD, SQL Plus, etc. with the trigger enabled. Can also update records in APEX if the update trigger is disabled. But should not have to disable the trigger as APEX applications may not be the only applications that are intended to be run against this database, and do not want users updating the primary keys. For example, users may be allowed access to the database with TOAD.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document