熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何通過DBlink進行遠程過程或函數調用

2013-11-13 16:08:09  來源: Oracle 

  有朋友在留言板問:如何通過動態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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.