如何通過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

From:http://tw.wingwit.com/Article/program/Oracle/201311/17874.html