[oracle]通过DBLINK远程查询带DBLINK的视图

最近做了一个非常奇葩的事情“通过DBLINK远程查询带DBLINK的视图”:

有A、B两个数据库实例，A下面有user1和user2两个用户，B实例下有用户user3。

现在user1 用户下建连接user3名为link3的DBLINK，并写视图user1.v_test查询 user3 用户下的tableA。

CREATE OR REPLACE VIEW user1.v_test AS
select field1,field2 
from tableA@link3
where 1=1;

在user2用户下调用user1.v_test即报“ORA-04054:数据库连接 LINK3 不存在”。意思就是user2不能用user1的私有dblink，因此我们要想办法使user2查询的时候在user1本地执行dblink查询。

解决办法如下：

在user1建函数调用user1.v_test 返回表类型

create or REPLACE type type_tableA is OBJECT (
  field1 VARCHAR2(6),
  field2 VARCHAR2(200)
  );

create or REPLACE type tb_tableA is table of type_tableA;


function f_tb_tableA()
return tb_tableA 
is
o_tb tb_tableA := tb_tableA();
i number := 0;
begin
  for v_rec in (select t.field1,t.field2
  from user1.v_test t
  WHERE 1=1) loop
    o_tb.extend;
    i := i + 1;
    o_tb(i) := type_tableA (
     v_rec.field1,
     v_rec.field2);
  end loop;
  return o_tb;
end;

还可参考:在Oracle的函数中,返回表类型

调用方式如下:

select * from table(user1.f_tb_tableA());

但是，当有另外一个用户user4 通过dblink(名为link2) 访问user2调用f_tb_tableA()，即在user4下执行:select * from table(user1.f_tb_tableA@link2());

报错“ORA-30626:不支持远程对象类型的功能/过程参数”。意思就是通过DBLINK不能调用返回值为类型的功能或过程。

因此我们继续想办法，既然返回值为“类型”的无法通过dblink, 那就想办法用可以通过dblink 的方法。我想到写个视图封装以上的查询语句，将视图给user4查询。

CREATE OR REPLACE VIEW user1.v_test_trans AS
select field1,field2 
from table(user1.f_tb_tableA())
where 1=1;

然后在user4用户下查询user1.v_test的方法如下:

select field1,field2 
from user1.v_test_trans@link2
where 1=1;

至此问题解决。

当然实际工程中我还使视图带参数，通过包体实现带参查询。