Way Too Invisible Columns

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 SELECT and MERGE statements.

Test data

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;

Testing SELECT

> 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 SELECT *.
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 12.1.0.2.

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 INTO clause.

> 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.

Conclusion

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?

Advertisements

2 thoughts on “Way Too Invisible Columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s