如何通过DBlink进行远程过程或函数调用

有朋友在留言板问:如何通过动态sql远程调用包里面的函数并返回值

我简单做了一个例子实现以上要求

首先进行适当授权

[oracle@jumper oracle]$ sqlplus / as sysdba

SQL*Plus: Release Production on Tue Nov ::

Copyright (c) Oracle Corporation All rights reserved

Connected to:

Oraclei Enterprise Edition Release Production

With the Partitioning option

JServer Release Production

SQL> grant create public database link to eygle;

Grant succeeded

SQL> grant all on dbms_flashback to eygle;

Grant succeeded

建立DB Link:

SQL> connect eygle/eygle

Connected

SQL> create public database link hsbill using hsbill;

Database link created

SQL> select db_link from dba_db_links;

DB_LINK

HSBILL

SQL> select * from dual@hsbill;

D

X

此后可以尝试使用DB Link进行远程和本地执行:

SQL> set serveroutput on

SQL> set feedback off

SQL> declare

r_gname varchar();

l_gname varchar();

begin

execute immediate

select GLOBAL_NAME from global_name@hsbill into r_gname;

dbms_outputput_line(gname of remote:||r_gname);

select GLOBAL_NAME into l_gname from global_name;

dbms_outputput_line(gname of locald:||l_gname);

end;

/

gname of remote:HSBILLHURRAYCOMCN

gname of locald:EYGLE

远程Package或Function调用也可以随之实现:

SQL> declare

r_scn number;

l_scn number;

begin

execute immediate

select dbms_flashbackGET_SYSTEM_CHANGE_NUMBER@hsbill from dual into r_scn;

dbms_outputput_line(scn of remote:||r_scn);

end;

/

scn of remote:

SQL>

The End