0

使用 dbms_output.put_line() 列印執行資訊是常用的 Oracle Stored Procedure 偵錯技巧，以下 Procedure 範例在DELETE 及 INSERT 後透過 dbms_output.put_line() 印出影響資料筆數，概念跟在程式碼裡塞入一堆 Debug.Print、MsgBox、alert() 差不多，是執行期間追查問題的重要線索：

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;

使用 PL/SQL Developer 或 Toad 等 Oracle 資料庫工具執行 Procedure，軟體介面有地方可以檢視 dbms_output 的輸出訊息，除錯抓蟲時很有用。

這個技巧開發測試階段大家用得很順手，如果程式已經上線在正式環境，是否也有機會蒐集到這些珍貴偵錯情資呢？跟同事討論到這個問題，起初大家都覺得無解，認真爬文找到線索，經過一番摸索及踩坑，還真的可行。

整理重點如下：

  1. dbms_output.put_line() 所寫入的內容會被放在緩衝區( Buffer )中( 緩衝區容量預設 20,000 Bytes )，可透過 dbms_output.get_line() 或 .get_lines() 讀取，若光寫不讀會把緩衝區塞爆出錯。
  2. 緩衝區以 Session 為單位，依實務的角度，就是你必須在執行 Procedure 的 OracleConnection 執行 dbms_output.get_line() 才讀得到東西。像 Dapper 允許不必開啟連線就執行 .Execute()/.Query() (背後自動開啟、關閉)，就可能因 Procedure 執行與 dbms_output 讀取使用不同連線( Session )而讀不到資料。
  3. dbms_output 預設為停用，記得要先呼叫 dbms_output.enable() ( 就是上圖有個 Enable Chceckbox 開關的意義 )，不然會做白工。
  4. dbms_output.get_line(line, status) 有兩個輸出參數，每次讀取一列字串，line 為字串內容，status 傳回 0 表示還有下一筆，傳回 1 代表緩衝區已空；dbms_output.get_lines(lines, numlines) 則一次取回字串陣列( CHARARR 型別 )及資料筆數。

講完原理來實際演練，我用 Dapper + ODP.NET 示範，用 get_line() 加 while 迴圈讀取，get_lines() 得取回字串陣列型別比較囉嗦，以後再試：

        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);
 
            }
        }

測試成功!


Comments

Be the first to post a comment

Post a comment