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

最新推荐文章于 2024-06-27 16:28:47 发布

fushidi

最新推荐文章于 2024-06-27 16:28:47 发布

阅读量2.4k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle相关文章标签： oracle 数据库

本文链接：https://blog.csdn.net/fushidi/article/details/120161756

Oracle相关专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了如何通过DBLINK在Oracle中实现跨数据库查询，并解决不同用户权限问题。当用户尝试通过DBLINK访问远程对象时遇到权限限制，作者通过创建视图、函数和类型来封装查询，确保用户可以间接访问所需数据。然而，当进一步通过DBLINK调用返回类型的功能时，由于不支持远程对象类型，作者创新地使用视图`v_test_trans`作为中介，成功解决了这一难题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近做了一个非常奇葩的事情“通过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;

至此问题解决。

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