企業庫調用 Oracle 預存程序返回單一/多重結果集

前言

Microsoft Enterprise Library Data Access Application Block (EntLib) 簡化 ADO.NET 資料存取。呼叫 Oracle 預存程序回傳結果集時，EntLib 有特定約定：單一結果集自動識別 cur_out 參數；多重結果集需手動註冊 OracleParameter。本文記錄 EntLib 5.0 + Oracle 10g/11g 實測可行的完整模式。

場景一：單一結果集 (自動映射 cur_out)

Oracle 預存程序

CREATE OR REPLACE PROCEDURE p_sel_top_count (
    in_top     IN  NUMBER,
    out_count  OUT NUMBER,
    cur_out    OUT SYS_REFCURSOR
) AS
BEGIN
    SELECT COUNT(*) INTO out_count FROM userinfo;
    OPEN cur_out FOR SELECT * FROM userinfo WHERE id < in_top;
END;
/

關鍵約定： OUT 游標參數必須命名為 cur_out，EntLib 內部硬編碼識別此名稱，改其他名稱將無法自動回傳結果集。

C# 呼叫程式碼

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using System.Data;
using System.Data.Common;

Database db = DatabaseFactory.CreateDatabase("OracleConnectionString");
DbCommand cmd = db.GetStoredProcCommand("p_sel_top_count");

// 輸入參數
db.AddInParameter(cmd, "in_top", DbType.Int32, 10);
// 輸出參數 (標量)
db.AddOutParameter(cmd, "out_count", DbType.Int32, 4);
// 注意：cur_out 不需手動 AddOutParameter，EntLib 會自動處理

DataSet ds = db.ExecuteDataSet(cmd);
// ds.Tables[0] 即為 cur_out 對應的結果集
int totalCount = Convert.ToInt32(db.GetParameterValue(cmd, "out_count"));

dataGridView1.DataSource = ds.Tables[0];

場景二：多重結果集 (手動註冊 OracleParameter)

Oracle 預存程序

CREATE OR REPLACE PROCEDURE p_sel_multi_result (
    in_top       IN  NUMBER,
    out_count    OUT NUMBER,
    cur_out_1    OUT SYS_REFCURSOR,
    cur_out_2    OUT SYS_REFCURSOR
) AS
BEGIN
    SELECT COUNT(*) INTO out_count FROM userinfo;
    OPEN cur_out_1 FOR SELECT * FROM userinfo WHERE id < in_top;
    OPEN cur_out_2 FOR SELECT * FROM userinfo WHERE id < 5;
END;
/

C# 呼叫程式碼 (關鍵差異)

Database db = DatabaseFactory.CreateDatabase("OracleConnectionString");
DbCommand cmd = db.GetStoredProcCommand("p_sel_multi_result");

db.AddInParameter(cmd, "in_top", DbType.Int32, 10);
db.AddOutParameter(cmd, "out_count", DbType.Int32, 4);

// 【關鍵】必須顯式建立 OracleParameter 並加入命令
// 需引用 Oracle.DataAccess.Client (ODP.NET)
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

OracleParameter pCursor1 = new OracleParameter("cur_out_1", OracleDbType.RefCursor);
pCursor1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pCursor1);

OracleParameter pCursor2 = new OracleParameter("cur_out_2", OracleDbType.RefCursor);
pCursor2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pCursor2);

// 執行
DataSet ds = db.ExecuteDataSet(cmd);
// ds.Tables[0] -> cur_out_1, ds.Tables[1] -> cur_out_2
// 順序與預存程序 OPEN 游標順序一致

int total = Convert.ToInt32(db.GetParameterValue(cmd, "out_count"));

EntLib 配置檔 關鍵設定

<connectionStrings>
  <add name="OracleConnectionString" 
       connectionString="Data Source=ORCL;User Id=scott;Password=tiger;" 
       providerName="Oracle.DataAccess.Client" />
</connectionStrings>

<dataConfiguration defaultDatabase="OracleConnectionString" />

providerName 必須為 Oracle.DataAccess.Client (ODP.NET)，而非 System.Data.OracleClient (已淘汰)。

常見問題與解決

最佳實務建議

1. 統一命名慣例：單一結果集一律使用 cur_out，多重結果集命名 cur_out_1, cur_out_2... 並對應手動註冊。
2. 封裝 Helper 方法：將 EntLib 呼叫邏輯封裝為 ExecuteProcSingleResult、ExecuteProcMultiResult，減少重複代碼。
3. 參數方向明確：所有 OUT 參數 (含標量與游標) 皆在 C# 端顯式宣告方向，避免隱含行為差異。
4. 使用 Managed ODP.NET：Oracle.ManagedDataAccess 無需 Client 安裝，部署簡單，支援 .NET Core / .NET 5+。

測試環境：EntLib 5.0.505 + Oracle 11g R2 + ODP.NET 4.121.2 (Managed)。EntLib 6 版本行為一致。若遷移至 .NET Core，建議改用 Dapper + Oracle.ManagedDataAccess 直接呼叫，更輕量。