T-SQL 與 SSMS：透過 Linked Server 從 SQL Server 呼叫 Oracle Procedure

T-SQL 與 SSMS：透過 Linked Server 從 SQL Server 呼叫 Oracle 預存程序

大家好，我是 Jayson Chan。在跨資料庫平台的環境中，我們時常需要從 SQL Server 去執行 Oracle 資料庫上的預存程序。SQL Server 的「連結伺服器」（Linked Server）功能讓我們可以無縫地存取 Oracle 的資料表、檢視，甚至直接呼叫 Oracle 的預存程序。今天我們就來探討如何使用 EXECUTE ... AT 語法，在 T‑SQL 中呼叫遠端 Oracle 的預存程序，並提供完整的範例與注意事項。

一、為什麼需要從 SQL Server 呼叫 Oracle 預存程序？

在企業環境中，資料常分散在不同的資料庫平台。例如前端應用程式使用 SQL Server，但某些核心商業邏輯寫在 Oracle 的預存程序中。若要在 SQL Server 中觸發這些邏輯，傳統做法是透過連結伺服器直接操作 Oracle 資料表，但若需要執行已封裝好的預存程序，就必須使用分散式查詢或 EXECUTE AT 指令。

二、什麼是 Linked Server？

Linked Server 是 SQL Server 的一種設定，讓 SQL Server 能夠對其他 OLE DB 資料來源執行命令。設定完成後，我們可以在 T‑SQL 中使用四部分名稱（[伺服器].[資料庫].[結構描述].[物件]）存取遠端物件，或是使用 OPENQUERY、EXECUTE AT 執行傳遞查詢或命令。

如何查看現有的 Linked Server？

在 SQL Server Management Studio (SSMS) 中，展開「物件總管」→ 你的 SQL Server 執行個體 → 「伺服器物件」→ 「連結伺服器」資料夾，即可看到所有已設定的連結伺服器。如下圖概念：

物件總管
└─ 你的伺服器名稱
   ├─ 資料庫
   ├─ 安全性
   ├─ 伺服器物件
   │  ├─ 備份裝置
   │  ├─ 端點
   │  └─ 連結伺服器      <-- 在這裡
   │      ├─ Oracle_IMS
   │      └─ ...

如果你需要新增一個連結伺服器，可以在「連結伺服器」上按右鍵 → 「新增連結伺服器」，然後提供 Oracle 的連線資訊（Provider 選擇 OraOLEDB.Oracle 或 Microsoft OLE DB Provider for Oracle），並設定安全性。

三、使用 EXECUTE ... AT 呼叫 Oracle 預存程序

SQL Server 提供了 EXECUTE 語法搭配 AT 關鍵字，可以在指定的連結伺服器上執行命令。基本語法如下：

EXECUTE ( '命令字串' ) AT [連結伺服器名稱];

其中「命令字串」必須是目標資料庫（此處為 Oracle）所能理解的 SQL 或 PL/SQL 語句。若要呼叫 Oracle 的預存程序，語法通常是：

EXECUTE ( 'CALL schema_name.procedure_name(參數...)' ) AT [Linked_Server_Name];

根據使用者的範例：

EXECUTE ( 'CALL ims.SP_004( ''ABC'' )' ) AT [Oracle_IMS];

這裡的 ims 是 Oracle 的 schema（使用者），SP_004 是預存程序名稱，''ABC'' 是傳入的字串參數（注意在 SQL Server 的字串中，單引號必須重複兩個來表示一個單引號）。

參數傳遞的注意事項

字串參數：必須使用兩個單引號包住，例如 ''ABC''。
數值參數：直接寫數字，例如 123。
日期參數：最好使用 Oracle 可接受的日期字面值，例如 ''2024-01-01''，並確保格式符合 Oracle 的 NLS 設定。
布林參數：Oracle 沒有布林型別，通常用字串或數字代替。
輸出參數：單純的 CALL 無法直接接收輸出參數。若要取得輸出參數，需要在 Oracle 端將結果放入暫存表，或在 SQL Server 端使用 BEGIN ... END; 區塊搭配繫結變數，但實務上較複雜。另一種方式是讓預存程序將結果寫入某個表格，再由 SQL Server 查詢該表格。

四、完整範例：呼叫 Oracle 預存程序並處理結果

假設 Oracle 中有一個預存程序 ims.get_employee_name，接受員工編號（數字），回傳員工姓名（字串）。但由於 CALL 不能直接回傳結果，我們可以將姓名存入一個全域暫存表，或讓預存程序有一個 OUT 參數，並在 SQL Server 端使用 PL/SQL 區塊來接收。以下示範如何使用 OUT 參數：

步驟 1：在 Oracle 建立測試用預存程序（含 OUT 參數）

CREATE OR REPLACE PROCEDURE ims.get_employee_name (
    p_empno  IN  NUMBER,
    p_ename  OUT VARCHAR2
) IS
BEGIN
    SELECT ename INTO p_ename FROM emp WHERE empno = p_empno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_ename := NULL;
END;

步驟 2：在 SQL Server 中呼叫此程序並取得 OUT 參數

由於 EXECUTE AT 只能執行單一命令，我們需要撰寫一個匿名 PL/SQL 區塊來宣告變數、呼叫程序，並將結果轉換成查詢結果（透過 SELECT 或 DBMS_OUTPUT）。但 EXECUTE AT 會將命令傳到 Oracle 執行，若我們希望 Oracle 回傳結果集給 SQL Server，可以在區塊中最後用 SELECT 將變數值回傳。例如：

DECLARE @result VARCHAR(100);

EXECUTE ( '
DECLARE
    v_name VARCHAR2(100);
BEGIN
    ims.get_employee_name( p_empno => 7369, p_ename => v_name );
    ? := v_name;   -- 這是錯誤寫法，Oracle 無法這樣用
END;
' ) AT [Oracle_IMS];

上述方式不可行，因為 EXECUTE AT 不支援輸出參數的繫結。正確的作法是讓 Oracle 端將結果以結果集的形式回傳，例如在 PL/SQL 區塊中使用 DBMS_SQL.RETURN_RESULT（Oracle 12c 以上）或使用 OPEN ? FOR 繫結 REF CURSOR，但這些在 EXECUTE AT 中也無法直接處理。因此最簡單的替代方案是：讓 Oracle 程序將結果寫入一個表格，然後 SQL Server 再透過 SELECT * FROM OPENQUERY(...) 去查詢。

替代方案：將結果寫入暫存表再查詢

我們可以設計 Oracle 程序將結果插入一個全域暫存表（例如 GLOBAL TEMPORARY TABLE），然後在 SQL Server 用 OPENQUERY 或 SELECT ... FROM [Oracle_IMS]..[schema].[table] 讀取資料。但這樣會有兩個步驟，且需考慮交易隔離與清理。

不過，如果我們只是想執行不帶回傳值的程序（例如 INSERT/UPDATE 或純商業邏輯），直接用 EXECUTE ... AT 就非常簡單。

五、錯誤處理與交易注意事項

1. 錯誤處理

當 EXECUTE AT 執行失敗時，SQL Server 會拋出錯誤，並停止後續語句。我們可以在 T‑SQL 中使用 TRY...CATCH 捕捉：

BEGIN TRY
    EXECUTE ( 'CALL ims.SP_004( ''ABC'' )' ) AT [Oracle_IMS];
    PRINT '執行成功';
END TRY
BEGIN CATCH
    PRINT '錯誤：' + ERROR_MESSAGE();
END CATCH

2. 交易（Transaction）

如果 EXECUTE AT 在 SQL Server 的明確交易內執行，它會遵循分散式交易（MS DTC）協調。Oracle 必須支援 XA 交易，且 MS DTC 服務必須設定正確。如果沒有設定分散式交易，則無法保證跨資料庫的原子性。若要避免使用分散式交易，可以將 SET REMOTE_PROC_TRANSACTIONS OFF，但如此便無法將遠端操作與本機交易合併。

六、實用範例：批次呼叫 Oracle 程序

假設我們有一個 SQL Server 的暫存表，內含多筆參數，需要逐筆呼叫 Oracle 程序。我們可以使用游標逐筆組合 EXECUTE AT：

DECLARE @empno INT, @ename VARCHAR(100);
DECLARE emp_cursor CURSOR FOR
    SELECT empno FROM temp_emp_list;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @empno;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 動態組合 CALL 字串，注意單引號處理
    DECLARE @sql NVARCHAR(1000) = 'CALL ims.process_employee(' + CAST(@empno AS NVARCHAR) + ')';
    EXECUTE ( @sql ) AT [Oracle_IMS];
    
    FETCH NEXT FROM emp_cursor INTO @empno;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

此範例假設 Oracle 的 process_employee 不需要回傳值。若有參數是字串，要記得加上引號。

七、常見問題與排除

錯誤：無法起始連結伺服器的 OLE DB 提供者 "OraOLEDB.Oracle" 的分散式交易 → 需要設定 MS DTC 或關閉分散式交易。
錯誤：登入失敗 → 檢查連結伺服器的安全性設定，是否正確對應 SQL Server 登入到 Oracle 帳號。
Oracle 預存程序中的參數型別不符 → 確認傳入的資料型別與 Oracle 程序定義一致，必要時在 T‑SQL 中先轉換。
無法從 EXECUTE AT 接收輸出參數或結果集 → 如前所述，EXECUTE AT 僅適用於不返回結果的命令；若需要回傳資料，考慮改用 OPENQUERY 或四部分名稱查詢。

八、結論

透過 EXECUTE ... AT 語法，SQL Server 可以輕鬆呼叫 Oracle 的預存程序，達成跨平台的商業邏輯整合。雖然在接收輸出參數上有些限制，但對於純執行動作的程序來說，已經非常方便。設定好連結伺服器後，只要留意參數格式與交易問題，就能像呼叫本機程序一樣簡單。

希望這篇文章能幫助你在實際工作中順利整合 SQL Server 與 Oracle！如果你有任何問題或經驗分享，歡迎在下方留言討論。

參考資料：Microsoft Docs – EXECUTE (Transact-SQL)、＜使用連結伺服器存取外部資料＞

下篇文章預告：探討 SQL Server 中的 SQL Agent 作業與排程呼叫 Oracle 程序，敬請期待！

本文由 Jayson Chan 原創，轉載請註明出處：https://freecodeinfosite.blogspot.com/

搜尋此網誌

freeCodeInfo