由 Microsoft SQL Server 呼叫 Oracle Server

最近公司引進Oracle資料庫，因此有需求要從 Microsoft SQL Server 呼叫 Oracle Server。經過一番 Google 後，並且實際驗證可行。茲將步驟歸納如下：

1. 於 Oracle Server 上建立測試資料，包括

Table : EMP
Package : WRAPPACK (請參考這裡)

CREATE PACKAGE WrapPack
AS
TYPE EmpID IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE EmpName IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
PROCEDURE WrapPackSP
(
NameLike IN VARCHAR2,
EID OUT EmpID,
EName OUT EmpName
);
END WrapPack;
/
CREATE PACKAGE BODY WrapPack
AS
PROCEDURE WrapPackSP
(
NameLike IN VARCHAR2,
EID OUT EmpID,
EName OUT EmpName
)
IS
EmpCount NUMBER DEFAULT 1;
BEGIN
EID(EmpCount) := 1;
EName(EmpCount) := 'test';
END WrapPackSP;
END WrapPack;
/

2. 並於 MS SQL Server 的機器中安裝 Oracle Client 並設定 tnsnames.ora 的內容。

ORASVR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = xxxx))
)
(CONNECT_DATA =
(SID = ORASVR)
(SERVER = DEDICATED)
)
)

3, 並於 MS SQL Server 建立連至 Oracle Server 的 Link Server(請參考這裡)。

EXEC sp_addlinkedserver 'OraLink', 'Oracle', 'MSDAORA', 'ORASVR'
EXEC sp_addlinkedsrvlogin 'OraLink', 'FALSE',NULL, 'userid', 'password'

4. 可於 MS SQL Server 上使用下列呼叫方式呼叫 Oracle 上的 Package。

declare @eid int
declare @ename varchar(30)
SELECT @eid=EID, @ename=ENAME FROM OPENQUERY( OraDBOSA , '{Call DOBOS.WrapPack.WrapPackSP( ''J%'', {resultset 20, eid, ename})}' )
select @eid, @ename