Oracle: MERGE INTO with invisible columns

Question

I have got an problem with ORA-00904: invalid identifier.

As example: I have a table created like this:

    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);

A second table on a 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 merge the data between the local and remote db with an merge into statement like this:

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);

The merge into statement does not work, because of an ORA-00904: "A5".COL_3 invalid identifier. But the same merge into statement works fine if the COL_3 column is visible. Where does the "A5" come from? Whats the problem here? Does anyone have the same issues?

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

Answer 1

Specify your source table select, by name COL_1 and COL_2. The key is to get rid of 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);