Using OracleTrace Component

In This Topic

The OracleTrace component provides functionality for tracing the SQL and PL/SQL commands execution on the server side. The SQL trace is stored in log files located on the server. The PL/SQL trace is stored in the database tables.

Call the SqlTraceStart() method or set the Enabled property to true for the SQL trace starting. It starts both SQL and PL/SQL traces. The SQL trace automatically chooses a log file where the trace will be written. The full path of this file can be retrieved by the GetTraceFileName() method. Use MaxTraceFileSize property to limit the size of this log file. The SqlTraceMode property specifies which type of statistics should be collected while tracing.

The PL/SQL trace needs the DBMS_TRACE package installed on the server. The PLSQL_TRACE_RUNS and PLSQL_TRACE_EVENTS tables are created to store log. This may be done by executing the tracetab.sql script available in the Oracle database/client installation package. The first table stores information about the trace runs, such as the time when trace started and finished, the trace owner, etc. The second table contains description of events happened while trace was active.

Call the PlSqlTraceStart() method or set the Enabled property to true for the PL/SQL trace starting. It will start the SQL trace as well. You need permissions to execute a DBMS_TRACE package to start PL/SQL tracing. The PL/SQL trace may be paused and resumed by the PlSqlTracePause and PlSqlTraceResume methods. The PlSqlTraceLimit method allows to limit the storage size used for the trace data in the database. The PlSqlTraceMode property specifies the tracing mode, i.e. events of which types should be traced.

// 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

See Also