Oracle: MERGE INTO with invisible columns

Question

I have a problem with ORA-00904: invalid identifier.

As an example: I have a table created as follows:

    CREATE TABLE TEST_TABLE
    (
      COL_1     VARCHAR2(5 CHAR) NOT NULL,
      COL_2     VARCHAR2(30 CHAR),
      COL_3     RAW(16) INVISIBLE DEFAULT SYS_GUID ()
    )
    CREATE UNIQUE INDEX TEST_TABLE_PK ON TEST_TABLE
    (COL_1);

The second table on the remote db (DBLINK: testdb) looks like this:

CREATE TABLE TEST_TABLE
(
  COL_1     VARCHAR2(5 CHAR) NOT NULL,
  COL_2     VARCHAR2(30 CHAR)
)
CREATE UNIQUE INDEX TEST_TABLE_PK ON TEST_TABLE
    (COL_1);

In the next step, I want to combine the data between the local and remote db with the union in the operator as follows:

MERGE INTO TEST_TABLE@testdb target
        USING (SELECT * FROM TEST_TABLE 
                WHERE COL_3 = '3F47613050860B4EE0539D0A10AC10B7') source
           ON (target.COL_1 = source.COL_1)
   WHEN MATCHED
   THEN
      UPDATE SET target.COL_2 = source.COL_2
   WHEN NOT MATCHED
   THEN
      INSERT     (COL_1, COL_2)
          VALUES (source.COL_1, source.COL_2);

Merging into a statement does not work due to an invalid identifier ORA-00904: "A5" .COL_3. But the same merge into an operator works fine if the column COL_3 is visible. Where does "A5" come from? What is the problem? Does anyone have the same problems?

Oracle versions: local db is 12cSE, and remote db is 11g.

Answer 1

COL_1 COL_2. SELECT * from

MERGE INTO TEST_TABLE@testdb target
        USING (SELECT COL_1,COL_2 FROM TEST_TABLE 
                WHERE COL_3 = '3F47613050860B4EE0539D0A10AC10B7') source
           ON (target.COL_1 = source.COL_1)
   WHEN MATCHED
   THEN
      UPDATE SET target.COL_2 = source.COL_2
   WHEN NOT MATCHED
   THEN
      INSERT     (COL_1, COL_2)
          VALUES (source.COL_1, source.COL_2);