How to resolve error ORA-20987: APEX - ORA-02015?

Question

I have a page in apex with multiple interactive grid. In one of the interactive grid i am trying to update the data of a column of a table in the database. The column named defect kind is a dropdown in the grid and on click of save should be able to updated the column value in database with the selected value in the grid.

While clicking on save apex is showing an error as follows:

Ajax call returned server error ORA-20987: APEX - ORA-02015: cannot select FOR UPDATE from remote table for.

I have used the type interactive grid - automatic row processing(DML) for saving the grid. Only defect kind is selected by the user rest all column value are coming directly from the table.

Interactive grid image:

Interactive grid image

NOTE:For this workspace the tables are accessed through a database link. I am using APEX 5.1.

pmdba pmdba · Accepted Answer · 2020-06-13T12:00:07

This error is raised specifically because you are working over a database link.

Error code: ORA-02015

Description: cannot select FOR UPDATE from remote table

Cause: An attempt was made to select FOR UPDATE on a remote table (directly, or via a view) that had abstract data type columns, and the view or select list contained abstract data type columns or non-column items.

Action: To attempt a select FOR UPDATE, use a view or select list that consists only of base columns from the remote table, none of which may be abstract data type columns.

I can't tell from what you've posted if the issue is with the query you're using to populate the IG or with some underlying view or the table itself. Depending your ability to locate and remove the "abstract data type columns or non-column items" (if that's even possible with an IG) you may not be able to make this work with automatic row processing over a database link at all. It may require a custom PL/SQL API instead.

How to resolve error ORA-20987: APEX - ORA-02015?

2 Answers