C# 讀取 dbms_output 效能強化版

2018-01-12 10:32 PM

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

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

使用 ODP.NET OracleParameter 接收
OracleParameter 有個 CollectionType 屬性，將屬性型別設為 OracleDbType.Varchar2，Size 為陣列大小，再設定 CollectionType = OracleCollectionType.PLSQLAssociativeArray，ArrayBindSize 傳入 int[] 指定每筆訊息字串最大長度(12c 上限 32767，更早版本為 4000)，可從 lines 參數取回字串陣列。
這個做法的缺點是寫法複雜且高度依賴 ODP.NET(不易改寫成 Dapper 版)，然後每次遇到要預估接收空間都讓我焦慮，太大怕浪費、太小取不完要分多次，好阿雜。
跑 PL/SQL 組裝成單一字串傳回
寫一小段 PL/SQL 程式，呼叫 get_lines() 再跑迴圈將其串成單一字串傳回。優點是只需單一字串參數接收結果，缺點是可能卡到 4000 或 32767 的字串大小上限，感覺爆掉的機會不低，不甚實用。

自訂函式將結果轉成 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 全部內容，算是最漂亮簡潔的解法，小缺點是需要資料庫部署自訂函數。

使用 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();

C# 讀取 dbms_output 效能強化版

Comments

# 2018-01-16 05:02 AM by 阿翰

Post a comment