TIPS - C# 讀取 Oracle dbms_output.put_line 輸出資訊

create or replace procedure JeffDBJobTest1 is

begin

  delete from JEFFTEST where idx = 32;

  dbms_output.put_line(sql%rowcount || ' rows deleted');

  insert into JEFFTEST values (32, sysdate);

  dbms_output.put_line(sql%rowcount || ' rows inserted');

end;

        static void Main(string[] args)

        {

            using (var cn = new OracleConnection(cs))

            {

                //**重要** 先開啟連線，確保後續執行在同一個Session

                cn.Open();

                //**重要** 記得要啟用dbms_output

                cn.Execute("dbms_output.enable", 

                    commandType: CommandType.StoredProcedure);

                //呼叫Stored Procedure

                cn.Execute("JeffDbJobTest1", 

                    commandType: CommandType.StoredProcedure);

                //準備參數接收

                DynamicParameters p = new DynamicParameters();

                p.Add("line", dbType: DbType.String, 

                    direction: ParameterDirection.Output, size: 4000);

                p.Add("status", dbType: DbType.Int32, 

                    direction: ParameterDirection.Output);

                int status;

                do

                {

                    cn.Execute("dbms_output.get_line", p, 

                        commandType: CommandType.StoredProcedure);

                    Console.WriteLine(p.Get<string>("line"));

                    status = p.Get<int>("status");

                } while (status == 0);

            }

        }