ORA-20010 Error When Updating Records
Last updated on MARCH 08, 2017
Applies to:Oracle Application Express (formerly HTML DB) - Version 4.2.2 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 "JSMITH.TSU_ORGANIZATION_ORGANIZATIO_0", line 2 ORA-04088: error during execution of trigger 'JSMITH.TSU_ORGANIZATION_ORGANIZATIO_0'
This error is coming from an AFTER UPDATE trigger on the ORGANIZATION.ID field:
CREATE OR REPLACE TRIGGER JSMITH.TSU_ORGANIZATION_ORGANIZATIO_0 AFTER UPDATE OF ID
ON JSMITH.ORGANIZATION FOR EACH ROW
RAISE_APPLICATION_ERROR(-20010,'CANNOT UPDATE COLUMN ID IN TABLE ORGANIZATION 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms