半岛权威十大直营(官方)网站

    在dotConnect for Oracle上如何使用OracleTrace组件呢？稍后将会用实例来展示。先来看一下OracleTrace组件，这个组件主要是用于跟踪在服务器端执行的SQL和PL / SQL命令，SQL跟踪位于服务器上的日志文件中，而L/SQL跟踪位于数据库表中。

    调用SqlTraceStart()方法或是SQL跟踪开始时设置可用属性为true，将会开启SQL 和PL/SQL跟踪，SQL跟踪自动选择跟踪日志文件写入跟踪，通过的GetTraceFileName （）方法可以获取该文件的完整路径，使用MaxTraceFileSize属性来限制日志文件的大小，SqlTraceMode属性将会指定哪种类型的数据应在跟踪的时候搜集。

    PL / SQL跟踪需要在服务器上安装的dbms_trace包，PLSQL_TRACE_RUNS 和PLSQL_TRACE_EVENTS表创建来存储日志，这个可以通过在Oracle数据库/客户端安装包中执行tracetab.sql实现。第一个表中主要用于跟踪运行时的信息，第二个表则要包括执行跟踪后的事件描述。

    调用SqlTraceStart()方法或是SQL跟踪开始时设置可用属性为true，开启SQL 和PL/SQL跟踪，也将会开启SQL跟踪。你需要权限来执行一个dbms_trace包用于开始PL / SQL跟踪。通过PlSqlTracePause 和PlSqlTraceResume方法 ，PL/SQL跟踪将会被暂停和恢复，PlSqlTraceLimit方法允许限制在数据库中用于数据跟踪的存储容量，PlSqlTraceMode属性将会指定跟踪类型。

    OracleTrace组件使用示例：

[C#]

// Initialize and open the connection to your Oracle server
// Connect as "system" to have the privileges needed to operate with the DBMS_TRACE package
OracleConnection connection = new OracleConnection();
connection.Server = "ORA";
connection.UserId = "system";
connection.Password = "password";
connection.ConnectMode = OracleConnectMode.SysDba;
connection.Open();

// Create the OracleTrace object representing the DBMS_TRACE package functionality.
OracleTrace trace = new OracleTrace(connection);

// Check the full path of the file where the trace log is stored
string path = trace.GetTraceFileName();
Console.WriteLine("The trace file: " + path);

// Create and execute a simple select query
// Observe that nothing was written to the trace file            
OracleCommand command = new OracleCommand("select * from scott.dept", connection);
command.ExecuteReader();            

// Start tracing the SQL commands execution.
trace.SqlTraceStart();

// Again execute the same command. 
// The trace log contains now detailed information on the query performed.            
command.ExecuteReader();

// Set the trace to observe the PL/SQL commands execution.
// The PL/SQL trace log is stored in the special database tables.
// By default, the PL/SQL trace mode is not set, 
// thus we need to change it, e.g., to tracing all PL/SQL calls.
trace.PlSqlTraceMode = PlSqlTraceMode.AllCalls;
trace.PlSqlTraceStart();

// Now tracing for both SQL and PL/SQL execution is enabled. 
// Note that this may be done by setting the Enabled property to true as well.

// Create an PL/SQL command.            
string plsqlCode = @"
DECLARE
i INTEGER;
BEGIN
i:= 1;
FOR rec IN (SELECT DeptNo FROM Scott.Dept
   WHERE RowNum <= 4 ORDER BY DeptNo)
LOOP
   UPDATE Scott.Dept
   SET DName = :NameArr(i)
   WHERE DeptNo = Rec.DeptNo;
   i:= i + 1;
END LOOP;
END;
";
string[] nameArray = { "Accounting", "Research", "Sales", "Operations" };
command = new OracleCommand(plsqlCode,connection);
command.Parameters.Add("NameArr", OracleDbType.VarChar);
command.Parameters["NameArr"].ArrayLength = 4;

[Visual Basic]

Dim connection As New OracleConnection
connection.Server = "ORA"
connection.UserId = "system"
connection.Password = "password"
connection.ConnectMode = 2
connection.Open()
Dim trace As New OracleTrace(connection)
Console.WriteLine(("The trace file: " & trace.GetTraceFileName))
Dim command As New OracleCommand("select * from scott.dept", connection)
command.ExecuteReader()
trace.SqlTraceStart()
command.ExecuteReader()
trace.PlSqlTraceMode = 1
trace.PlSqlTraceStart()
Dim plsqlCode As String = VbCrlf & 
"DECLARE" & VbCrlf & 
"i INTEGER;" & VbCrlf & 
"BEGIN" & VbCrlf & 
"i:= 1;" & VbCrlf &
"FOR rec IN (SELECT DeptNo FROM Scott.Dept" & VbCrlf & 
"   WHERE RowNum <= 4 ORDER BY DeptNo)" & VbCrlf & 
"LOOP" & VbCrlf & "   UPDATE Scott.Dept" & VbCrlf &
"   SET DName = :NameArr(i)" & VbCrlf & 
"   WHERE DeptNo = Rec.DeptNo;" & VbCrlf & 
"   i:= i + 1;" & VbCrlf & 
"END LOOP;" & VbCrlf & 
"END;" & VbCrlf

Dim nameArray As String() = New String() {"Accounting", "Research", "Sales", "Operations"}
command = New OracleCommand(plsqlCode, connection)
command.Parameters.Add("NameArr", &H1C)
command.Parameters("NameArr").ArrayLength = 4