学步园

Oracle用SYS.DBMS_DEBUG实现对存储过程/函数/包的调试。如果你用DESC SYS.DBMS_DEBUG命令查看它的发现其
成员函数和方法并不是特别多，但是为了实现这个功能却花了我近10天的功夫，因为某些方法或函数的不恰当的调用，
会导致意想不到的问题，如程序挂起。主要参考了Oracle的官方文档：
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_debug.htm
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_debug2.htm

为了实现调试功能，你要打开两个Oracle连接，一个是执行你要调试语句的targetSession,
另外一个是对其进行调试的debugSession.在调试之前，你需要对你要调试的存储过程/函数/包要重新编译以产生编译信息
ALTER [PROCEDURE | FUNCTION | PACKAGE | TRIGGER | TYPE] <name> COMPILE DEBUG;
ALTER [PACKAGE | TYPE] <name> COMPILE DEBUG BODY;

请看下面的流程图关于目标会话/调试会话需要执行的动作及其相互关系：

启动调试的代码：

 1private void StartDebug()
 2        {
 3            #region            
 4            this.mStepInto.Enabled = false;            
 5            this.mStepOver.Enabled = false;
 6            this.mRun.Enabled = false;            
 7            this.IsDebuging = true;
 8            this.MSQL.Document.ReadOnly = true;
 9            this.btnConfigure.Enabled = false;
10            this.OutParameterGrid.RowCount = 0;
11            this.OutDataGrid.DataSource = null;
12            #endregion
13
14            initialize target session and command#region initialize target session and command    
15            if(this.targetSession == null)
16            {
17                string csn = this._connection.GetOleCSNInfo().CSNNoProvider;
18                targetSession = new OracleConnection(csn +  ";Pooling=false;");                 
19                targetSession.Open();
20                //targetSession.
21                targetCommand = targetSession.CreateCommand();                
22                targetCommand.CommandText = "begin SYS.DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:id); end;";                
23                OracleParameter idp  = targetCommand.Parameters.Add("id",OracleType.VarChar,64);
24                targetClientID = System.Guid.NewGuid().ToString();
25                idp.Value = targetClientID;
26                targetCommand.ExecuteNonQuery();
27                targetCommand.Parameters.Clear();
28            }
29            #endregion 
30
31            initialize debug session and command#region initialize debug session and command
32            if(this.debugSession == null)
33            {
34                string csn = this._connection.GetOleCSNInfo().CSNNoProvider;
35                debugSession = new OracleConnection(csn);
36                debugCommand = debugSession.CreateCommand();   
37                debugSession.Open ();                
38            }
39            #endregion
40   
41            prepare#region prepare
42            targetCommand.Parameters.Clear();
43            if(this.debugID == null)
44            {
45                targetCommand.CommandText = "alter session set plsql_debug=true";
46                targetCommand.Parameters.Clear();
47                targetCommand.ExecuteNonQuery();
48
49                //targetCommand.CommandText = "alter procedure TEST compile debug";
50                //targetCommand.ExecuteNonQuery();
51                #endregion 
52   
53                Get DebugID;#region Get DebugID;
54                targetCommand.CommandText = "select SYS.DBMS_DEBUG.Initialize(null,0) from dual";
55                debugID = targetCommand.ExecuteScalar().ToString();
56                #endregion  
57                targetCommand.CommandText = "begin SYS.DBMS_DEBUG.Debug_on(true,false); end;";                            
58                targetCommand.ExecuteNonQuery ();
59            }
60            this.executeException = null;
61            //if(this.targetThread == null)
62            //{
63                targetThread = new System.Threading.Thread(new System.Threading.ThreadStart(this.Execute));
64            //}
65            
66            targetThread.Start();            
67            System.Threading.Thread.Sleep(500);    
68            if(this.Synchronize())
69            {
70                this.Debug(BreakFlag.AnyCall);    
71                this.btnStartDebug.Enabled = false;
72                this.mStepInto.Enabled = true;
73                this.mStepOver.Enabled = true;
74                this.mRun.Enabled = true;                
75            }
76            else
77            {
78                this.executeException = null;
79                this.IsDebuging = false;
80                this.MSQL.Document.ReadOnly = false;
81                this.btnStartDebug.Enabled = true;
82                this.mStepInto.Enabled = false;
83                this.mStepOver.Enabled = false;
84                this.mRun.Enabled = false;
85                if(this.ObjectName != null && this.ObjectName != "")
86                {                
87                    this.btnConfigure.Enabled = true;
88                }                
89            }            
90        }
91

与目标会话同步的代码：

private bool Synchronize()
        {
            if(this.targetThread.ThreadState == System.Threading.ThreadState.Stopped && this.executeException != null)
            {
                ExceptionDialog.Show(this.executeException.Message,MessageboxTitle.Title.Error,this.executeException);
                return false;
            }
            string strSQL = "declare \n" +
                "    running_info sys.dbms_debug.runtime_info; \n" +
                "    seconds BINARY_INTEGER;" +
                "begin \n" +
                "     seconds := SYS.DBMS_DEBUG.SET_TIMEOUT(5*60); \n" +
                "     SYS.DBMS_DEBUG.ATTACH_SESSION(:debugid,0); \n" + 
                "    :sync_result := SYS.DBMS_DEBUG.SYNCHRONIZE(run_info => running_info, \n" +
                "                    info_requested => SYS.DBMS_DEBUG.info_getStackDepth + \n" +
                "                                     SYS.DBMS_DEBUG.info_getLineInfo + \n" +
                "                                        SYS.DBMS_DEBUG.info_getBreakpoint); \n" +
                "    seconds := SYS.DBMS_DEBUG.SET_TIMEOUT(3600); \n" +
                "end;";
            this.debugCommand.Parameters.Clear();
            this.debugCommand.CommandText = strSQL;
            OracleParameter pdebugid = this.debugCommand.Parameters.Add("debugid",OracleType.VarChar,20);
            pdebugid.Value = this.debugID;
            OracleParameter psync_result = this.debugCommand.Parameters.Add("sync_result",OracleType.Int32);
            psync_result.Direction = ParameterDirection.Output;
            this.debugCommand.ExecuteNonQuery();
            int p = int.Parse(psync_result.Value.ToString());
            ErrorCode errorCode = (ErrorCode)p;
            return errorCode == ErrorCode.success;
        }

单步调试的代码：

 1private ErrorCode Continue(out RuntimeInfo runtimeInfo,BreakFlag breakFlag)
 2        {    
 3            runtimeInfo = new RuntimeInfo();
 4            string strSQL=    "declare \n " +
 5                "    running_info sys.dbms_debug.runtime_info; \n" +                            
 6                "begin \n" + 
 7                "    :cont_result := sys.dbms_debug.continue(run_info =>running_info,\n" +
 8                "                                            breakflags =>:breakflag,\n" +
 9                "                                            info_requested =>sys.dbms_debug.info_getStackDepth + \n" +
10                "                                            sys.dbms_debug.info_getLineInfo + sys.dbms_debug.info_getBreakpoint);" +
11                "    :Terminated := running_info.Terminated;\n" +
12                "    :Line := running_info.Line#; \n" +
13                "    :Reason := running_info.Reason;\n" +
14                "    :BreakPoint := running_info.BreakPoint;\n" +
15                "    :Oer := running_info.Oer;\n" +
16                "    :StackDepth := running_info.StackDepth;\n" +
17                "    :DBLink := running_info.Program.DBLink;\n" +
18                "    :EntrypointName := running_info.Program.EntrypointName;\n" + 
19                "    :Name := running_info.Program.Name;\n" +
20                "    :NameSpace := running_info.Program.NameSpace;\n" +
21                "    :Owner := running_info.Program.Owner;\n" +
22                "    :UnitType := running_info.Program.LibunitType;\n" +
23                "end;";
24            this.debugCommand.Parameters.Clear();
25            
26            OracleParameter pIn = this.debugCommand.Parameters.Add("breakflag",OracleType.Int32);
27            pIn.Value =