用C#实现对Oracle 存储过程/函数/包的调试(附源代码)

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;

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

启动调试的代码：

 1 private   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;
        }

单步调试的代码：

 1 private  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 = breakFlag;
28
29            OracleParameter pOut = this.debugCommand.Parameters.Add("Terminated",OracleType.Int32);
30            pOut.Direction = ParameterDirection.Output;
31            
32            pOut = this.debugCommand.Parameters.Add("Line",OracleType.Int32);
33            pOut.Direction = ParameterDirection.Output;
34
35            pOut = this.debugCommand.Parameters.Add("Reason",OracleType.Int32);
36            pOut.Direction = ParameterDirection.Output;
37
38            pOut = this.debugCommand.Parameters.Add("BreakPoint",OracleType.Int32);
39            pOut.Direction = ParameterDirection.Output;
40            
41            pOut = this.debugCommand.Parameters.Add("Oer",OracleType.Int32);
42            pOut.Direction = ParameterDirection.Output;
43
44            pOut = this.debugCommand.Parameters.Add("StackDepth",OracleType.Int32);
45            pOut.Direction = ParameterDirection.Output;
46            
47            pOut = this.debugCommand.Parameters.Add("DBLink",OracleType.VarChar,20);
48            pOut.Direction = ParameterDirection.Output;
49            
50            pOut = this.debugCommand.Parameters.Add("EntrypointName",OracleType.VarChar,20);
51            pOut.Direction = ParameterDirection.Output;
52            
53            pOut = this.debugCommand.Parameters.Add("Name",OracleType.VarChar,20);
54            pOut.Direction = ParameterDirection.Output;
55            
56            pOut = this.debugCommand.Parameters.Add("NameSpace",OracleType.Int32);
57            pOut.Direction = ParameterDirection.Output;
58
59            pOut = this.debugCommand.Parameters.Add("Owner",OracleType.VarChar,20);
60            pOut.Direction = ParameterDirection.Output;
61            
62
63            pOut = this.debugCommand.Parameters.Add("UnitType",OracleType.Int32);
64            pOut.Direction = ParameterDirection.Output;
65            
66            pOut = this.debugCommand.Parameters.Add("cont_result",OracleType.Int32);
67            pOut.Direction = ParameterDirection.Output;
68
69            this.debugCommand.CommandText = strSQL;
70            this.debugCommand.ExecuteNonQuery();
71            
72            pOut = this.debugCommand.Parameters["cont_result"];
73            int p = int.Parse(pOut.Value.ToString());
74            ErrorCode errorCode = (ErrorCode)p;
75            
76            runtimeInfo.Terminated = 0;
77            if(errorCode == ErrorCode.success)
78            {
79                runtimeInfo.Terminated = GetValueFromParameters(this.debugCommand.Parameters,"Terminated",-1);
80                runtimeInfo.Line = GetValueFromParameters(this.debugCommand.Parameters,"Line",-1);
81                runtimeInfo.Reason = (ReasonFlag)GetValueFromParameters(this.debugCommand.Parameters,"Reason",0);                
82                runtimeInfo.Breakpoint = GetValueFromParameters(this.debugCommand.Parameters,"Breakpoint",-1);
83                runtimeInfo.Oer = GetValueFromParameters(this.debugCommand.Parameters,"Oer",-1);
84                runtimeInfo.StackDepth = GetValueFromParameters(this.debugCommand.Parameters,"StackDepth",-1);                
85                runtimeInfo.Program.Dblink = GetValueFromParameters(this.debugCommand.Parameters,"DBLink","");
86                runtimeInfo.Program.EntrypointName = GetValueFromParameters(this.debugCommand.Parameters,"EntrypointName","");
87                runtimeInfo.Program.Line = runtimeInfo.Line;
88                runtimeInfo.Program.Name = GetValueFromParameters(this.debugCommand.Parameters,"Name","");
89                runtimeInfo.Program.Namespace = (NameSpace)GetValueFromParameters(this.debugCommand.Parameters,"NameSpace",127);
90                runtimeInfo.Program.Owner = GetValueFromParameters(this.debugCommand.Parameters,"Owner","");
91                runtimeInfo.Program.UnitType =(LibunitType)GetValueFromParameters(this.debugCommand.Parameters,"UnitType",-1);
92            }
93            return errorCode;
94        }

处理单步调试结果的代码：

 1 private   void  Debug(BreakFlag breakFlag)
 2          {
 3            RuntimeInfo runtimeInfo;             
 4            this.Continue(out runtimeInfo,breakFlag);
 5            //this.MString.ActiveTextAreaControl.TextArea.            
 6            if(runtimeInfo.Reason == ReasonFlag.KernlExit || this.targetThread.ThreadState == System.Threading.ThreadState.Stopped)
 7            {
 8                this.DetachSession();    
 9                this.MSQL.ActiveTextAreaControl.SelectionManager.ClearSelection();
10                System.Threading.Thread.Sleep(500);
11                if(this.executeException != null)
12                {
13                    ExceptionDialog.Show(this.executeException.Message,MessageboxTitle.Title.Error,this.executeException);
14                }                
15                else
16                {
17                    DisplayOutParameter();
18                }
19//                if(this.targetThread.ThreadState != System.Threading.ThreadState.Stopped)
20//                    this.AutoDebug();
21                this.IsDebuging = false;
22                if(this.ObjectName != null && this.ObjectName != "")
23                {                
24                    this.btnConfigure.Enabled = true;
25                }
26                this.richTextBox1.Text = "";
27                this.MSQL.Document.ReadOnly = false;
28                this.mRun.Enabled = false;
29                this.mStepInto.Enabled = false;
30                this.mStepOver.Enabled = false;
31                this.btnStartDebug.Enabled = true;
32                if(currentDebugSQLEditor != null && currentDebugMarker != null)
33                {
34                    currentDebugSQLEditor.Document.MarkerStrategy.TextMarker.Remove(currentDebugMarker);
35                    currentDebugSQLEditor.Refresh();
36                }
37            }
38            else if(runtimeInfo.Program.Name == "" && runtimeInfo.Line > 0)
39            {
40                this.tabSource.SelectedTab = this.tabSource.TabPages[0];
41                Highlight(this.tabSource.TabPages[0],runtimeInfo.Line);
42                DisplayAllVarValue();
43                GetBackTrace();
44            }
45            else if(runtimeInfo.Program.Name != "" && runtimeInfo.Line > 0)
46            {
47                int x =0;
48                x ++;
49                string objectName  = runtimeInfo.Program.Owner + "." + runtimeInfo.Program.Name;
50                Crownwood.Magic.Controls.TabPage sourcePage = this.GetTabPage(runtimeInfo);
51                //if(runtimeInfo.Program.Namespace == NameSpace.Pkg_Body 
52                if(sourcePage == null)
53                {
54                    sourcePage = this.GenerateTabPage(runtimeInfo);
55                    
56                }
57                if(sourcePage != null)
58                {
59                    this.tabSource.SelectedTab = sourcePage;
60                    Highlight(sourcePage,runtimeInfo.Line);
61                }
62                DisplayAllVarValue();
63                GetBackTrace();
64            }
65        }

所有的源代码：
/Files/SharkXu/OracleDebug.zip

Oracle 调试功能我已经融合进了我的 Shark DB Expert