[zt] 利用 SQL Server 中的 Linked Server 執行 Oracle 中之預儲函式、程式

說明

MSSQL 提供的 linked server 功能，讓我們可以連線到其所支援的異質資料庫引擎，執行各種 DML 相關的 SQL 指令。

我們可以利用這個功能，讓原先使用 MSSQL 滿足應用面需求的程式，在面對必須同時使用來自 Oracle 資料庫之資料，才能滿足的需求時，無需程式設計師自行連結到 Oracle 資料庫，即可讓設計師完成該需求之實作。

[FIXME：這樣做明顯的好處在哪裡？]

建立 Oracle linked server

關於如何在 MSSQL 中，建立連線到 Oracle 資料庫的 linked server ，其相關之步驟，我們可在 MSSQL 的線上檔案或是在網路檔案中，找到巨細靡遺的說明，在此 shortie 就不再浪費篇幅介紹，基本上包含以下兩個主要的步驟：

1. 在 MSSQL 所在的機器上，安裝合適的 Oracle 使用者端程式，並設定好可以連線至目標 Oracle 資料庫的連線資訊。
2. 利用 MSSQL 提供的 Enterprise Manager 管理介面或是 sp_addlinkedserver 預儲函式，建立 linked server 。

使用 Oracle linked server

建立好連線至 Oracle 的 linked server 之後，只要再注意以下幾個容易犯錯的地方，我們便可在 DML 中，輕鬆使用 Oracle 中的資料庫物件。

1. Oracle 資料庫物件必須使用完整的四部式命名，即 OracleLinkedServerName..OwnerUserName.ObjectName 。
   1. 如果 Oracle 資料庫物件建立時，未使用 " 字元包住，使用全部大寫的 ObjectName 。
   2. 反之，則使用與建立時相同大小寫之 ObjectName 。

範例：

假設我們的 Oracle linked server 名稱為 OraTest ，使用者為 ERD ，物件名稱為 CODEREF 資料表，則以下的 DML SQL 指令，都可以正確的在 Query Analyzer 中執行：

SELECT *
  FROM OraTest..ERD.CODEREF
 WHERE REFID = '168'

UPDATE OraTest..ERD.CODEREF
   SET DESC = DESC + '168'
 WHERE REFID = '168'

INSERT INTO OraTest..ERD.CODEREF (
                REFID, DESC, ....
            )
     VALUES (
                '538', '940', ...
            )

DELETE
  FROM OraTest..ERD.CODEREF
 WHERE REFID = '538'

大部分的需求都可以由這些 DML 來完成。

執行 Oracle linked server 中之預儲函式與程式

本文之標題重點就在這裡，而這也是屬於比較不容易完成的工作。

1. 首先，將 Oracle 中要開放給 MSSQL 呼叫的預儲函式，先用 Package 包裝起來：

Spec:

CREATE OR REPLACE PACKAGE pkg_MSSQL
AS

  TYPE ReturnSerialTbl IS TABLE OF VARCHAR2 (20)
  /* The index is important, otherwise {resultset} doesn't work. */
  INDEX BY BINARY_INTEGER;

  PROCEDURE pr_GetSerialNo (
    ASerialType    IN       VARCHAR2,
    ASerialOwner   IN       VARCHAR2,
    ASerialNo      OUT      ReturnSerialTbl
  );
  
END pkg_MSSQL;
/

Body:

CREATE OR REPLACE PACKAGE BODY pkg_MSSQL
AS

  PROCEDURE pr_GetSerialNo (
      ASerialType    IN       VARCHAR2,
      ASerialOwner   IN       VARCHAR2,
      ASerialNo      OUT      ReturnSerialTbl
  )
  IS
    VSerialNo   VARCHAR2 (20);
  BEGIN
    ERD.pr_GetSerialNo (ASerialType, ASerialOwner, VSerialNo);
    ASerialNo (1) := VSerialNo;
  END pr_GetSerialNo;
  
END pkg_MSSQL;
/

以上的例子，乃是將 Oracle 中的 pr_GetSerialNo 開放給 MSSQL 使用。

註：pr_GetSerialNo 是 shortie 常用的系統取號控制預儲程式。

2. 接著，從 MSSQL 利用 Dynamic SQL 呼叫 Oracle 中的預儲函式：

create table #serialtbl (
  SerialNo VARCHAR(20)
)

Declare
  @ASerialType  VARCHAR(200),
  @ASerialOwner VARCHAR(200),
  @OracleCall   VARCHAR(8000),
  @RetVal       VARCHAR(20)

SET @ASerialType = 'ID'
SET @ASerialOwner = 'BB_BloodOrderId'

SET @OracleCall = 'INSERT INTO #serialtbl '
SET @OracleCall = ' SELECT * '
SET @OracleCall = ' FROM OPENQUERY(MYORACLE , ''{CALL ERD.pkg_mssql.pr_getserialno( '
SET @OracleCall = @OracleCall + ''''''+ @ASerialType + ''''''
SET @OracleCall = @OracleCall + ',' + '''''' +  @ASerialOwner + ''''''
SET @OracleCall = @OracleCall + ',{RESULTSET 25, ASerialNo})}'')'

PRINT @ORACLECALL

EXEC (@OracleCall)

SELECT @RetVal = SerialNo FROM #SerialTbl

DROP TABLE #SerialTbl

PRINT @RetVal

執行結果

INSERT INTO #serialtbl
    SELECT *
    FROM OPENQUERY(oratest_erd ,
         '{CALL ERD.pkg_mssql.pr_getserialno(''ID'',''BB_BloodOrderId'',{RESULTSET 25, ASerialNo})}'
)

(1 row(s) affected)
O000000016

結果顯示執行成功，傳回的序號值為 '0000000016' 。

結論

本文說明在 Mircrosoft SQL Server 中，透過 Linked Server 連線至 Oracle ，然後呼叫 Oracle 中的 Stored Procedure/Function 。基本上，對 shortie 個人而言，知道這個做法純粹是滿足個人「知性」的需求，真正面臨到這個問題時， shortie 應該會使用其他的解決方案，因為大部分 shortie 碰到的 DBA 都會要求自己管理的 DBMS 要「守身如玉」，與其跟這些不入流的 DBA 去 Fight ，還不如繞開他們，山不轉路轉，路不轉人轉！