前文介紹過使用 C# 讀取 dbms_output 寫入內容,範例留了一個小尾巴,跑迴圈連資料庫犯了效能大忌,應改成一次執行或查詢取回才上道。

dbms_output.get_lines() 允許一次取得多筆訊息,但傳回型別為 TYPE DBMSOUTPUT_LINESARRAY IS VARRAY(2147483647) OF VARCHAR2(32767); 讀取要費點手腳,Oracle 生手經過一番研究,試出四種不同做法,就當練功吧。

  1. 使用 ODP.NET OracleParameter 接收
    OracleParameter 有個 CollectionType 屬性,將屬性型別設為 OracleDbType.Varchar2,Size 為陣列大小,再設定 CollectionType  = OracleCollectionType.PLSQLAssociativeArray,ArrayBindSize 傳入 int[] 指定每筆訊息字串最大長度(12c 上限 32767,更早版本為 4000),可從 lines 參數取回字串陣列。
    這個做法的缺點是寫法複雜且高度依賴 ODP.NET(不易改寫成 Dapper 版),然後每次遇到要預估接收空間都讓我焦慮,太大怕浪費、太小取不完要分多次,好阿雜。
  2. 跑 PL/SQL 組裝成單一字串傳回
    寫一小段 PL/SQL 程式,呼叫 get_lines() 再跑迴圈將其串成單一字串傳回。優點是只需單一字串參數接收結果,缺點是可能卡到 4000 或 32767 的字串大小上限,感覺爆掉的機會不低,不甚實用。
  3. 自訂函式將結果轉成 Table 後以 SELECT 查詢讀取
    在 Stackoverflow 看到這招妙計
    create or replace function get_dbms_output
        return dbmsoutput_linesarray
    as
        l_output dbmsoutput_linesarray;
        l_linecount number;
    begin
        dbms_output.get_lines(l_output, l_linecount);
     
        if l_output.count > l_linecount then
            -- Remove the final empty line above l_linecount
            l_output.trim;
        end if;

    透過神奇的 get_dbms_output 自訂函數,跑 SELECT column_value FROM TABLE(get_dbms_output) 純查詢就能撈回 dbms_output 全部內容,算是最漂亮簡潔的解法,小缺點是需要資料庫部署自訂函數。
  4. 使用 Ref Cursor 讀取
    受 get_dbms_output 函數的啟發,我想到不用部署 Procedure 或自訂函數也能用 Ref Cursor 一次取回所有訊息的做法(Dapper 可支援 Ref Cursor),比 SELECT 法複雜,但不必動到資料庫就能用,算是次佳解。

附上四種做法的範例。

        static void Main(string[] args)
        {
            using (var cn = new OracleConnection(cs))
            {
                //**重要** 先開啟連線,確保後續執行在同一個Session
                cn.Open();
 
                //**重要** 記得要啟用dbms_output
                cn.Execute("dbms_output.enable", 
                    commandType: CommandType.StoredProcedure);
 
                //方法1,使用PLSQLAssociativeArray接回陣列
                cn.Execute("JeffDbJobTest1", 
                    commandType: CommandType.StoredProcedure);
 
                var cmd = cn.CreateCommand();
                cmd.CommandText = "dbms_output.get_lines";
                cmd.CommandType = CommandType.StoredProcedure;
                var pLines = cmd.Parameters.Add("lines", OracleDbType.Varchar2, 
                    ParameterDirection.Output);
                pLines.Size = 2000; //可容納的訊息字串筆數
                pLines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                //指定每筆字串最大長度(Oracle 12可到32767)
                pLines.ArrayBindSize = Enumerable.Repeat(4000, pLines.Size).ToArray();
                //numlines為雙向參數,執行前傳入lines可容納筆數,執行後傳回實際讀得筆數
                var pNumLines = cmd.Parameters.Add("numlines", OracleDbType.Int32, 
                    ParameterDirection.InputOutput);
                pNumLines.Value = pLines.Size; 
                cmd.ExecuteNonQuery();
 
                var rawLines = (OracleString[]) pLines.Value;
                string[] lines =
                    //依numlines判斷資料筆數
                    rawLines.Take(((OracleDecimal) pNumLines.Value).ToInt32())
                        .Select(o => o.ToString()).ToArray();
                Console.WriteLine(string.Join("\n", lines));
                //缺點: 程序較複雜,得先預估空間,若一次取不完還是得跑迴圈(但應很罕見)
 
                //方法二,用PL/SQL指令組成字串一次傳回
                //呼叫Stored Procedure
                cn.Execute("JeffDbJobTest1",
                    commandType: CommandType.StoredProcedure);
 
                var p = new DynamicParameters();
                p.Add("result", dbType: DbType.AnsiString, size: 32767, 
                    direction: ParameterDirection.Output);
                
                cn.Execute(@"
DECLARE 
    lines dbmsoutput_linesarray;
    numlines INTEGER;
    i INTEGER;
    msg VARCHAR2(32767);
BEGIN
    numlines := 32767;
    dbms_output.get_lines(lines, numlines);
    i := 1;
    WHILE i <= numlines 
      LOOP
        IF i = 1 THEN
            msg := lines(i);
        ELSE
            msg := msg || CHR(10) || lines(i);
        END IF;
        i := i + 1;
      END LOOP;
    :result := msg;
END;
", p);
                Console.WriteLine(p.Get<string>("result"));
                //缺點: 取回字串有長度限制(12c 32767,更早版本只有4000)
 
                //方法3,使用自訂函數轉成Table
                cn.Execute("JeffDbJobTest1",
                    commandType: CommandType.StoredProcedure);
                lines = cn.Query<string>(
                    "SELECT column_value from table(get_dbms_output)").ToArray();
                foreach (var line in lines)
                    Console.WriteLine(line);
                //缺點: 需在資料庫部署自訂函數
 
                //方法4,用RefCursor
                cn.Execute("JeffDbJobTest1",
                    commandType: CommandType.StoredProcedure);
 
                //使用 Dapper 接收 Oracle Ref Cursor 
                //http://blog.darkthread.net/post-2017-04-17-dapper-ref-cursor.aspx
                var op = new OracleDynamicParameters();
                op.Add("res", dbType: OracleDbType.RefCursor, 
                    direction: ParameterDirection.Output);
                var m = cn.QueryMultiple(@"
DECLARE
    lines dbmsoutput_linesarray;
    numlines INTEGER;
BEGIN
    dbms_output.get_lines(lines, numlines);
    IF lines.COUNT > numlines THEN
        lines.TRIM;
    END IF;
    OPEN :res FOR SELECT column_value FROM TABLE(lines);
END;
", op);
                var data = m.Read();
                lines = data.Select(o => (string) o.COLUMN_VALUE).ToArray();
                foreach (var line in lines)
                    Console.WriteLine(line);
 
                Console.Read();
            }
        }

Comments

# by 阿翰

真的很不喜歡 Oracle... 要這樣才能從sp中select T-SQL真的人性化多

Post a comment