Dynamic SQL Tracing in Application

While debugging we might want to know the SQL generated for the particular event. So we increase the log level to 5 OR we enable sql spooling.
In both cases, it is difficult to find the sql's generated only for the particular event. The following configuration will help you out to turn on/off sql spooling and there by analysing the sql generated only for the events.

The Idea here is to create a command button on application toolbar and then upon pressing button invoke a business service which will trun on/off sql trace.Follow the below steps to configure dynamic sql trace.

Step 1: Create a Business Service with the following code.

function LogTrace(Inputs, Outputs)
{
var isTraceOn = TheApplication().GetProfileAttr("IsTraceOn");
var sFile = "c:\\temp\\Siebel_Trace.Log";
if( isTraceOn == "TRUE" ){
TheApplication().TraceOff();
TheApplication().SetProfileAttr("IsTraceOn","FALSE");
var oShell = COMCreateObject("Shell.Application");
oShell.ShellExecute(sFile,"","","open","1");
oShell = null;
}
else{
TheApplication().TraceOn(sFile,"SQL","");
TheApplication().SetProfileAttr("IsTraceOn","TRUE");
}
}
Step 2: Create a Command Object to call this business service method.
Name: Log Trace
Business Service: Business Service Name created in step1.
Display Name: Log Trace
HTML Bitmap: Log Trace Icon (you can create a bitmap image and associate it to this command)
Method: LogTrace
Target: Server

Step3: Add this command to a Toolbar Item. I used Query Tool bar which is displayed right top corner of the application.
Query for the "HIQuery" Toolbar from Toolbars Object Type.In Toolbar Item Child Object Type add following.
Name: Log Trace
Command: Command Created in step2
Display Name: Log Trace
HTML Type: Link
Position: 30

Compile the change and open application, you will see a button just after the query toolbar.
Press Log Trace Command button once. It will enable the sql trace, then perform the event i.e, click on button or navigate to a new view.
Then press the Log Trace command button. It will disable the sql trace and opens a text file with sql statements executed for that event.