用 EDB 的 Oracle 相容 DBLink 呼叫 Oracle 上的 stored function

EnterpirseDB 的企業版資料庫是目前對 Oracle 相容性最好的 PostgreSQL 延伸發行版，裡面也提供了在 Oracle 資料庫中，常用的 DBLink 工具的支援。

在原生的 PostgreSQL 已經有對 PostgreSQL 之間的 DBLink；不過，在 EnterpriseDB 企業版中，更特別增加了「對 Oracle」的異質資料庫的 DBLink 內建功能。使的不只在同質資料庫（PostgreSQL）之間有 DBLink，還能跨 EnterpriseDB 與 Oracle ，幫助降低同時使用EnterpriseDB / Oracle 兩種產品時，系統間的整合難度。

不過，對於 EnterpriseDB 對於連到 Oracle 資料庫的 DBLink 支援，畢竟是異質資料庫之間的事情，也不可能是百分之百完全相容的：在基本資料交換上，還能滿足語法一致（用 @ 的語法）；不過在更為進階的使用上，就還沒有那麼充分支援。

在這篇練習中，會示範一個稍微進階的 EDB DBLink 用法：在 EDB 上用 DBLink 呼叫遠在 Oracle 上的 Stored Function。

首先，先在 Oracle 上面建立一個測試函數：

Oracle上：

conn hr/hr CREATE OR REPLACE FUNCTION TEST_ADD(ad1 NUMBER,ad2 NUMBER) RETURN NUMBER AS V_NUM NUMBER; BEGIN V_NUM:=ad1+ad2; RETURN V_NUM; END; /

SQL> select test_add(2,3) from dual; TEST_ADD() ----------- 5

接著，就可以從 EDB 上呼叫上面這個 Stored Function 了

EDB上：

edb=# CREATE DATABASE LINK test2 CONNECT TO edb_user IDENTIFIED BY 'passwd'  USING oci '//127.0.0.1/xe'; CREATE DATABASE LINK edb=# SELECT dblink_ora_connect('test2','127.0.0.1','xe','hr','hr','1521');  dblink_ora_connect -------------------- OK (1 row) edb=# SELECT * FROM dblink_ora_record('test2','select test_add(2,3) from dual') AS tm(tm NUMBER);  tm ----  5 (1 row)

上面這樣就呼叫成功了。

那麼遠在 Oracle 上的 Stored 「Prcedure」呢？

由於 EnterpriseDB 對於 DBLink 連到 Oracle 的支援，設計上是仿照原生 POstgreSQL 的 DBLink 函數，因此對於一般沒有回傳值的 Oracle  Stored Prcedure，就還沒辦法「直接」支援（截至 EDB 9.5 版）。

若是非用遠端 Oracle Procedure 不可呢？目前在 EDB 上可以「使用」遠端 Oracle Procedure 的方式，就是在 Oracle 上，多加一個 Wrapper Function，包住需要被 EDB DBLink 用到的 Stored Procedure，然後再於 EDB 上，透過上述的方式呼叫：

Oracle 上：

CREATE TABLE mytestday (mydate DATE); CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS BEGIN INSERT INTO mytestday VALUES(sysdate); commit; V_NUM := 2; END; / -- 下面是多出來的，讓 EDB 透過 DBLink 呼叫的 Wrapper Function CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER AS V_NUM NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN TEST_PROC(V_NUM); RETURN V_NUM; END; /

SQL> select TEST_FUNC() from dual; TEST_FUNC() -----------          2 SQL> select * from testday; MYDATE -------------- 11-7月 -16

然後，在 EDB 上，一樣去呼叫這個 Wrapper（Stored Function）：

EDB 上：

edb=# CREATE DATABASE LINK test2 CONNECT TO edb_user IDENTIFIED BY 'passwd'  USING oci '//127.0.0.1/xe'; CREATE DATABASE LINK edb=# SELECT dblink_ora_connect('test2','127.0.0.1','xe','hr','hr','1521'); dblink_ora_connect -------------------- OK (1 row) edb=# -- 注意這裡呼叫的是多建立的 Wrapper edb=# select * from dblink_ora_record('test2','select test_func() from dual') as tm(tm varchar2(10)); tm ----  2 (1 row) edb=# -- 檢查一下剛剛執行 Oracle Stored Procedure 所插入的資料 edb=# SELECT * FROM mytestday@test2;      some_day     -------------------- 11-JUL-16 20:48:09 11-JUL-16 20:50:33   <--- 剛剛從 EDB 新增的 (2 rows)

這樣繞路之後，總算是從 EDB 上呼叫 Oracle Stored Procedure 了（汗～）。

上述的方式，需要在來源 Oracle 上增加冗贅的函數，如果有考慮這樣使用 DBLink 的話，還是需要衡量一下比較好。