包的调试(附源代码)

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;

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

启动调试的代码：

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

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

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

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

//targetCommand.CommandText = "alter procedure TEST compile debug";
50

//targetCommand.ExecuteNonQuery();
51

#endregion
52

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

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;

}

单步调试的代码：

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

OracleParameter pIn = this.debugCommand.Parameters.Add("breakflag",OracleType.Int32);
27

pIn.Value = breakFlag;
28

OracleParameter pOut = this.debugCommand.Parameters.Add("Terminated",OracleType.Int32);
30

pOut.Direction = ParameterDirection.Output;
31

pOut = this.debugCommand.Parameters.Add("Line",OracleType.Int32);
33

pOut.Direction = ParameterDirection.Output;
34

pOut = this.debugCommand.Parameters.Add("Reason",OracleType.Int32);
36

pOut.Direction = ParameterDirection.Output;
37

pOut = this.debugCommand.Parameters.Add("BreakPoint",OracleType.Int32);
39

pOut.Direction = ParameterDirection.Output;
40

pOut = this.debugCommand.Parameters.Add("Oer",OracleType.Int32);
42

pOut.Direction = ParameterDirection.Output;
43

pOut = this.debugCommand.Parameters.Add("StackDepth",OracleType.Int32);
45

pOut.Direction = ParameterDirection.Output;
46

pOut = this.debugCommand.Parameters.Add("DBLink",OracleType.VarChar,20);
48

pOut.Direction = ParameterDirection.Output;
49

pOut = this.debugCommand.Parameters.Add("EntrypointName",OracleType.VarChar,20);
51

pOut.Direction = ParameterDirection.Output;
52

pOut = this.debugCommand.Parameters.Add("Name",OracleType.VarChar,20);
54

pOut.Direction = ParameterDirection.Output;
55

pOut = this.debugCommand.Parameters.Add("NameSpace",OracleType.Int32);
57

pOut.Direction = ParameterDirection.Output;
58

pOut = this.debugCommand.Parameters.Add("Owner",OracleType.VarChar,20);
60

pOut.Direction = ParameterDirection.Output;
61

pOut = this.debugCommand.Parameters.Add("UnitType",OracleType.Int32);
64

pOut.Direction = ParameterDirection.Output;
65

pOut = this.debugCommand.Parameters.Add("cont_result",OracleType.Int32);
67

pOut.Direction = ParameterDirection.Output;
68

this.debugCommand.CommandText = strSQL;
70

this.debugCommand.ExecuteNonQuery();
71

pOut = this.debugCommand.Parameters["cont_result"];
73

int p = int.Parse(pOut.Value.ToString());
74

ErrorCode errorCode = (ErrorCode)p;
75

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

}

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

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

}
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

posted on 2007-05-10 22:08 大李阅读(1226) 评论(0) 收藏举报

刷新页面返回顶部

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

导航

公告