ODP.NET 練習 - 執行 PL/SQL 將結果寫入暫存資料表傳回

有讀者問到 ODP.NET CommandText 跑多行指令的花式應用，包含在 Script Block DECLARE 宣告變數以及傳回暫存資料表結果。 透過多組 PL/SQL 邏輯組裝資料存進暫存資料表再一次傳回結果的做法，實務上還蠻常見的，但大多情況會寫成 Procedure。如果能讓 .NET 端決定 Script，應用彈性可更上層樓，感覺是很實用的技巧，決定來練習一下。

資安宣導：任何動態決定 SQL 指令的場合(包含在 Procedure 裡呼叫 EXECUTE IMMEDIATE、sp_executesql 也是)，都請務必排除 SQL Injection 風險，此事非同小可，情節嚴重甚至可致身敗命裂家破人亡。

整理這項練習涉及的議題：

1. 使用 CommandText 執行多行指令，關鍵要用 begin ... end; 把指令包起來，並且要留意不能更動 Schema (DDL)，若一定要需改用 EXECUTE IMMEDIATE '...'。
2. Script Block 要回傳 SELECT 結果，需使用 OracleDbType.RefCursor 型別參數，常見範例多應用在 Stored Procedure，用在 Script Block 概念差不多，宣告 OracleDbType.RefCursor 參數並設成 ParameterDirection.Output 即可。
3. Oracle 的暫存資料表概念跟 MSSQL 很不一樣，18c 以前的暫存資料表必須事先宣告且為全域性，差在每個 Session 只看得到自己的資料，互不干擾。全域暂存表分為 ON COMMIT DELETE ROWS (Commit 時就清掉)、 ON COMMIT PRESERVE ROWS (同一 Session 可繼續存取資料直到 Session 結束)。18c+ 起支援私有暫存資料表，資料只保存在記憶體裡，Session 或 Transaction 結束時連同資料表定義一起丟棄 (ON COMMIT DROP DEFINITION)。不過，Oracle 的暫存資料表雖然可以用 EXECUTE IMMEDIATE '...' 動態建立，後續若想直接寫 INSERT INTO ora$ptt_XXX 卻會因不認得 Schema 無法編譯，通通得改寫成 EXECUTE IMMEDIATE，便利性輸 MSSQL 一大截，加上 Oracle 18+ 限定，我還是選擇用全域暫存資料表實作。
4. 經實測 OracleCommand.ExecuteNonQuery() 預設會觸發 Commit，若暫存資料表設成 ON COMMIT DELETE ROWS，RefCursor 讀不到資料，會出現 ORA-08103: object no longer exists 錯誤；若設成 ON COMMIT PRESERVE ROWS 雖可讀到資料， 但由於 Connection Pool 共用連線物件會沿用同一 Session，導致第二次查詢看到前一次殘留資料。針對這個問題，我的解法是宣告 ON COMMIT DELETE ROWS，執行時啟用 Transaction，讀完 RefCursor 資料再 Commit。(如果大家若有其他更好做法，也請不吝分享。)

講完原理，來看程式碼。

測試前要預先建好 GLOBAL TEMPORARY TABLE TheSalmons，我在 .NET 裡寫了一段 Script，用 DECLARE 宣告 v_idx 練習在 Script Block 中使用變數，寫入兩筆固定資料到暫存資料表，第三筆姓名則由外部傳入 :my_salmon_name 參數指定，最後開啟名為 :salmons 的 RefCursor 供呼叫端讀取。

<%@Page Language="C#"%>
<%@ Import Namespace="Oracle.ManagedDataAccess.Client" %>
<script runat="server">
    string cs = CnnStringManager.GetConnString("XE");
    /*
-- 預先在 Oracle 建好 Global Temporary Table
CREATE GLOBAL TEMPORARY TABLE TheSalmons (
    FishId NUMBER(4,0), FishName NVARCHAR2(64)
) ON COMMIT DELETE ROWS
    */
    const string sql = @"
DECLARE
    v_idx NUMBER(1);
BEGIN
    v_idx := 1;
    INSERT INTO TheSalmons VALUES (v_idx, N'張鮭魚之夢');
    v_idx := v_idx + 1;
    INSERT INTO TheSalmons VALUES (v_idx, N'許星光流連擊鮭魚');
    v_idx := v_idx + 1;
    INSERT INTO TheSalmons VALUES (v_idx, :my_salmon_name);
    OPEN :salmons FOR SELECT * FROM TheSalmons;
END;
";

    void Page_Load(object sender, EventArgs e)
    {
        using (var cn = new OracleConnection(cs))
        {
            cn.Open();
            var trn = cn.BeginTransaction();
            var cmd = cn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Transaction = trn;
            cmd.BindByName = true;
            cmd.Parameters.Add("my_salmon_name", OracleDbType.NVarchar2).Value = 
                "童鮭魚靜";
            var refCur = cmd.Parameters.Add("salmons", OracleDbType.RefCursor);
            refCur.Direction = System.Data.ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            var dr = ((Oracle.ManagedDataAccess.Types.OracleRefCursor)refCur.Value).GetDataReader();
            Response.ContentType = "text/plain";
            while (dr.Read())
            {
                Response.Write(dr["FishId"].ToString() + "." + dr["FishName"].ToString() + "\n");
            }
            trn.Commit();
        }
    }

</script>

成功!

Example of using PL/SQL script block to insert data into temporary table and read the results by reference cursor with ODP.NET.