Editing a remote db table from Interactive Grid

Generally, in Interactive Grids we use local database table for any DML operation. Recently I came across a scenario where the underlying table used in the interactive grid was a remote database table. When I tried to use it through same process I got an error while saving a record after edit. Today in this post I will share my experience regarding the same and how I fixed the issue.

Lets first understand the use case.

I have a remote Snowflake database where I created a table and entered few rows in that. And I have created a DB link to access tables from Oracle Database.

Remote Table Name : PSTAGE_TAXONOMY_SPECIALTY_MAP_TEST

DB Link Name : snow

In my Oracle APEX application I created an Interactive Grid because I want to edit one of the columns of the remote db table

Now whenever I access the remote table I use @snow for db link.

The query that I used for Interactive Grid:

SELECT b.TAXONOMY_CODE,
b.DERIVED_HCP_TYPE,
b.SPECIALTY_CODE,
b.STATUS,
b.last_update_date,
b.updated_by
FROM BPH.PSTAGE_TAXONOMY_SPECIALTY_MAP_TEST@snow b

Now if I try to update any record from this remote table, it will throw error

Here SNOW is the name of my DB link.

Which means remote db tables can't be used exactly same way as our local tables.

After that I did some changes in the Interactive Grid property and it allowed me to edit and save records in remote table.

In the Interactive Grid Automatic DML process, there is property "Prevent Lost Updates"

By default, this property will be switched on, but we have to keep it switched off for remote database.

After doing this change, I repeated the same process

Now record is properly saving in the remote db table.

There might be other ways for solving this issue.

Please share your thoughts or your experiences about utilising a remote database table in interactive grid.

Thanks for reading my blog !!

Have a Great Day !!