2.1 Remote Insert Rowsource

A remote insert rowsource feature allows remote insert requiring local Oracle data to work through the Oracle database and Oracle Database Gateway. This functionality requires that the Oracle database and the Oracle Database Gateway to be version 12.2 or later.

By Oracle Database design, some distributed statement must be executed at the database link site. But in certain circumstances, there is data needed to execute these queries that must be fetched from the originating Oracle Database. Under homogeneous connections, the remote Oracle database would call back the source Oracle database for such data. But in heterogeneous connections, this is not viable, because this means that the Foreign Data Store would have to query call back functions, or data, that can only be provided by the Oracle instance that issued the query. In general, these kinds of statements are not something that can be supported through the Oracle Database Gateway.

The following categories of SQL statements results in a callback:

• Any DML with a sub-select, which refers to a table in Oracle database.

• Any DELETE, INSERT, UPDATE or "SELECT... FOR UPDATE..." SQL statement containing SQL functions or statements that needs to be executed at the originating Oracle database.

  These SQL functions include USER, USERENV, and SYSDATE; and involve the selection of data from the originating Oracle database.

• Any SQL statement that involves a table in Oracle database, and a LONG or LOB column in a remote table.

An example of a remote INSERT statement that can work through the remote insert rowsource feature is as follows:

INSERT INTO gateway_table@gateway_link select * from local_table;