Oracle APEX 24.1.6 | Oracle DB 19c
Hi, I have an APEX application with an interactive grid that is tied to a remote table. The table creation statement I used is similar to this (this is a test table, not my actual table):
create table my_table (
id integer generated by default on null as identity not null
, my_column_a number(8, 0) not null
, my_column_b integer not null
, my_column_c number(4, 0) not null
, my_column_d number(15, 2) not null
);
Then I add permissions (note that I played around with granting select to the sequence that is made automatically for an IDENTITY column, but that doesn't seem to have any bearing on the outcome shown below):
grant select, insert, update, delete on my_table to my_user;
Then I create the synonym (on the remote server):
create or replace editionable synonym my_table for my_schema.my_table@database_link;
At this point, all is well. I create an interactive grid for the table (this is an APEX workspace tied to the remote schema, so it is referencing the table as a synonym). I can add, update, delete just fine. However, if I add a condition in the Where Clause property of the Interactive Grid, I get an issue. Here is the condition I added (note that it specifically matters that I'm filtering on a Page Item, and yes, I have the “Page Items to Submit” filled out with P7_SOME_ID):
MY_COLUMN_A = :P7_SOME_ID
If I do this, then I try to perform an UPDATE or DELETE operation (INSERT works fine), I get the error:
Process 'MY_TABLE - Save Interactive Grid Data' raised 'ORA-0000: normal, successful completion' while performing row locking. This error can occur when the process issues a SELECT FOR UPDATE on a complex view with an instead-of trigger, to lock the row. Set the process attribute 'Lock Row' to No or PL/SQL Code.
The error ORA-0000 is strange. The documentation says “normal, successful completion”. In any case, it seems that APEX is interpreting the table as a “complex view” now that I have a condition specified in the WHERE clause. To my recollection, this isn't a problem when the table referenced is a local table (in APEX's parsing schema) as opposed to a synonym.
If I turn off “lock row” it's fine, but, I'd rather keep lock row on. Not that I'll likely have many users, or even concurrent users, but its possible and I'd rather prevent any issues to begin with.
I also tried it as a SQL Query, but that doesn't seem to make a difference.
Any idea why this is an issue and a good workaround?