C# 讀取 dbms_output 效能強化版

        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://blog2.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();

            }

        }