ORA-20010 Error When Updating Records (Doc ID 1584573.1)

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.

Symptoms

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.

Error
-------
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
BEGIN
RAISE_APPLICATION_ERROR(-20010,'CANNOT UPDATE COLUMN ID IN TABLE ORGANIZATION AS IT USES SEQUENCE.');
END;
/

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.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms