Oracle Database 12c introduced “invisible columns”: they are only visible when you name them explicitly in the
SELECT list. Unfortunately, they seem to be even more invisible when you access them through a database link! Here are some surprising results from
I made this setup as concise as possible, so it is not realistic.
- I will select from table T, and I will merge into T using a source table S.
- @ORLC@LOOPBACK is a database link to the same database.
- V_LOCAL is a view on T. It explicitly names the invisible column I1, so I1 should be “visible” when accessing the view.
- V_REMOTE is a view on T, but through the database link.
create table T( K1 number primary key, I1 number INVISIBLE not null ); insert into T (K1,I1) select 1, 1 from DUAL; create table S( K1 number primary key, I1 number INVISIBLE not null ); insert into S (K1,I1) select 1, 1 from DUAL; create database link ORCL@LOOPBACK connect to STEW identified by STEW using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= 127.0.0.1)(PORT=1521)))(CONNECT_DATA=(service_name=orcl.localdomain)(SERVER=DEDICATED)))'; create or replace view V_LOCAL as select K1, I1 from T; create or replace view V_REMOTE as select K1, I1 from T@ORCL@LOOPBACK;
> select * from T / K1 ---------- 1 > select K1, I1 from T / K1 I1 ---------- ---------- 1 1
This just shows how the invisible column works: you don’t see it when you say
Now let’s try the views. Since I named the column in each view, I assume it will be visible at all times.
> select * from V_LOCAL / K1 I1 ---------- ---------- 1 1 > select * from V_REMOTE / K1 I1 ---------- ---------- 1 1
OK, that worked as expected. Now let’s try to access the “local” view through the database link.
> select * from V_LOCAL@ORCL@LOOPBACK / K1 ---------- 1
Oops! The very same SELECT on the very same view gives different results when accessed remotely. The invisible column has gone into hiding again.
Note that this is on version 18.104.22.168.
[Update 2018-02-11: I get the same result in version 22.214.171.124]
Testing MERGE with a WHERE clause
To be concise, I am using MERGE to update the I1 column only. Since I don’t want to do any unnecessary work, I make sure the I1 column is different before doing the update. That is why I add the clause
where O.I1 != N.I1.
That WHERE clause is the problem. Oracle refuses to “see” the
O.I1 column: it keeps saying it is an “invalid identifier”.
Local table: exception
> merge into T O using (select K1, I1 from S) N on (O.K1 = N.K1) when matched then update set I1=N.I1 where O.I1 != N.I1 / SQL Error: ORA-00904: "O"."I1": invalid identifier
Local table with inline view: OK
I already figured out how to work around this problem: use an “inline view” in the
> merge into ( select K1, I1 from T ) O using (select K1, I1 from S) N on (O.K1 = N.K1) when matched then update set I1=N.I1 where O.I1 != N.I1 / 0 rows merged.
Remote table with inline view: exception
I’m going to use the same “inline view” technique here, but with a remote table.
> merge into ( select K1, I1 from T@ORCL@LOOPBACK ) O using (select K1, I1 from S) N on (O.K1 = N.K1) when matched then update set I1=N.I1 where O.I1 != N.I1 / SQL Error: ORA-00904: "A1"."I1": invalid identifier ORA-02063: preceding line from ORCL@LOOPBACK
Look closely at the error message: the “O” alias has disappeared, to be replaced by “A1”. Apparently, the remote database is using an “A1” alias internally, but it does not realize that the column should be visible.
I tried several different ways to work around this problem. To be brief, I’ll just show the combination that finally worked.
Inline view of remote access to local view: OK
Believe it or not, the only combinition that worked was to create a view on the table, then access that view while also explicitly naming the columns.
> merge into ( select K1, I1 from V_LOCAL@ORCL@LOOPBACK ) O using (select K1, I1 from S) N on (O.K1 = N.K1) when matched then update set I1=N.I1 where O.I1 != N.I1 / 0 rows merged.
Watch out for invisible columns on remote tables! They are even more invisible remotely than they are locally. Views that are accessed remotely don’t act the same as when they are accessed locally.
Using MERGE on remote invisible columns is especially challenging.
All these tests use the same Oracle version on the local and remote databases. Who knows what will happen when a pre-12c database tries to access an invisible column in a remote 12c database?