在遠端和本地儲存過程間傳遞陣列[zt]

Ref: http://yangtingkun.itpub.net/post/468/405146

簡單描述一下在遠端儲存過程和本地儲存過程之間傳遞陣列的方法。

對於本地的儲存過程直接，直接傳遞陣列是很容易的：

SQL> CREATE OR REPLACE PACKAGE PA_TEST AS
2 TYPE T_NAME IS TABLE OF VARCHAR2(30);
3 END;
4 /

程式包已建立。

SQL> CREATE OR REPLACE PROCEDURE P_TEST (P_NAME OUT PA_TEST.T_NAME) AS
2 BEGIN
3 SELECT TABLE_NAME BULK COLLECT INTO P_NAME FROM USER_TABLES;
4 END;
5 /

過程已建立。

SQL> SET SERVEROUT ON SIZE 1000000
SQL> DECLARE
2 V_NAME PA_TEST.T_NAME;
3 BEGIN
4 P_TEST(V_NAME);
5 FOR I IN 1..V_NAME.COUNT LOOP
6 DBMS_OUTPUT.PUT_LINE(V_NAME(I));
7 END LOOP;
8 END;
9 /
T_BLOB
T
T_PK
MLOG$_T
MLOG$_T_BLOB
MLOG$_T_PK
T2
RUPD$_T_PK

PL/SQL 過程已成功完成。

不過同樣的方法並不適應於遠端儲存過程呼叫：

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
------------------------------------------------
YTK102.YTK_THINKPAD

SQL> CONN YANGTK/YANGTK@TEST1已連線。
SQL> CREATE DATABASE LINK YTK102.YTK_THINKPAD CONNECT TO YANGTK IDENTIFIED BY YANGTK USING 'YTK102';

資料庫連結已建立。

SQL> CREATE OR REPLACE PACKAGE PA_TEST AS
2 TYPE T_NAME IS TABLE OF VARCHAR2(30);
3 END;
4 /

程式包已建立。

SQL> SET SERVEROUT ON SIZE 1000000
SQL> DECLARE
2 V_NAME PA_TEST.T_NAME;
3 BEGIN
4 P_TEST@YTK102.YTK_THINKPAD(V_NAME);
5 FOR I IN 1..V_NAME.COUNT LOOP
6 DBMS_OUTPUT.PUT_LINE(V_NAME(I));
7 END LOOP;
8 END;
9 /
P_TEST@YTK102.YTK_THINKPAD(V_NAME);
*第 4 行出現錯誤:
ORA-06550: 第 4 行, 第 2 列:
PLS-00306: 呼叫 'P_TEST' 時引數個數或型別錯誤
ORA-06550: 第 4 行, 第 2 列:
PL/SQL: Statement ignored

對於遠端儲存過程，即使傳遞完全相同的集合資料型別，也是會報錯的，這是由於變數的定義在本地，Oracle無法確定本地變數和遠端變數是相同的。

對於這種情況，可以利用遠端型別來進行變數的宣告：

SQL> DECLARE
2 V_NAME PA_TEST.T_NAME@YTK102.YTK_THINKPAD;
3 BEGIN
4 P_TEST@YTK102.YTK_THINKPAD(V_NAME);
5 FOR I IN 1..V_NAME.COUNT LOOP
6 DBMS_OUTPUT.PUT_LINE(V_NAME(I));
7 END LOOP;
8 END;
9 /
T_BLOB
T
T_PK
MLOG$_T
MLOG$_T_BLOB
MLOG$_T_PK
T2
RUPD$_T_PK

PL/SQL 過程已成功完成。