Diagnoise Forms Using Trace

Some times we need to diagnose the issue or error coming in forms. For such situation we need to get more information about the issue we are facing in forms. One of the best way to get such information is using tracing.

Many types of tracing are available in forms. We will discuss couple of methods for enabling the tracing and see how that can help us in diagnosing the issue.

Enabling SQL Tracing
First we see how to enable a normal SQL tracing. In my case I have a forms where I am entering few values and then saving the form. This will have some insert and updates command in the backend which will get executed when we save the form. SQL tracing is used to track those DML queries to debug the issue. When we open the form we can see on the menu option

Help -> Diagnostic -> Trace ->

Here we have many options available (default is ‘No Trace’). We can enable tracing by selecing on of the options from here. ‘Regular Trace’ gives the least information and ‘Trace with Binds and Waits’ (level 12) gives maximum information. Be careful while enabling SQL trace with wait and binds as this will make the trace file huge.

Also we can start the tracing in the middle of forms sessions also. Example in my case I wanted to check the last insert/update DML statements that gets fired when we save the form. So after we fill all the values in the fields, we can start the tracing so that the initial select statement does not come in trace file.

When we enable the trace it will give the trace file location (This location will be the location of USER_DUMP_DESTINATION parameter of database).
After you save the form you can stop tracing by selecting ‘No Trace’ again from
Help -> Diagnostic -> Trace -> No Trace

Use tkprof to covert trace file in readable format.
-bash-2.05b$ tkprof md9ys210_ora_20412_MFG.trcoutput = trace_sql.txt
TKPROF: Release 10.2.0.4.0 - Production on Mon Jul 28 23:54:31 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
-bash-2.05b$

Enabling Forms Tracing
Another way for debugging is to enable tracing at forms level for a particular user. This method is also explained in metalink note ID 373548.1. By default, it will trace errors only. To utilize the full flexibility of Forms Trace, the record=forms parameter must be used in conjunction with the tracegroup parameter.
Applications Development has created several recommended levels of tracing, which are predefined in the file
$ORA_CONFIG_HOME/10.1.2/forms/server/ftrace.cfg

· light (0-71,192): Records user actions, error messages and form service events.
· medium (0-97,192,194,195): Includes events in the light tracegroup also captures built-in and user-exit events.
· full (0-98,100-199): Captures all information that is currently available through Forms Tracing.
· dbsql (32,64,98,100,101,136,137,192): All events related to the database and forms interaction with it
· network (64,32,128,129,131,130,132,133,134,192): All events related to communications between client tier and Forms server.

In my case I am enabling the tracing for SQLs.
Please follow below steps to enable form level tracing.
1. Sign on to Oracle Applications as a user with the System Administrator responsibility.
2. Select the System Administrator responsibility.
3. Select the Profile -> System function (this will launch Forms).
4. Search for the Applications user and the profile option Forms Runtime Parameters.
5. Add the required parameters, using the syntax: record=forms tracegroup=dbsqlYou can use any other value also for tracegroup depending on your requirement. Examples:
record=forms tracegroup=medium
Note the space between the parameter/value pairs.
6. Commit the changes and log out of Applications.
7. Restart the Forms OC4J group using either adformsctl.sh (servlet) or adformsrvctl.sh (socket).
8. Log in to Applications as the user whose Forms Runtime Parameters profile option was amended.
Before doing this, metalink note ID also mention to append “record=forms” at the end of ICX: Forms Launcher profile for that user. Example
http://rafi.oracle.com:8080/forms/frmservlet?record=forms

But when I tried that I got following error.
FRM-90926: Duplicate Parameter on command line.
So I removed record=forms and I was able to generate the trace files without any issue. This may be some issue with my instance.

Once you set the profile “Forms Runtime Parameters”, bounce the forms and try to accecss forms, it will generate a trace file at the location defined by FORMS_TRACE_DIR. After you finish the navigation in form and exit, your trace file will get created FORMS_TRACE_DIR location.
This file will be in binary format and cannot be read. You need to convert this into .txt or .html format using following command
java -cp $ORACLE_HOME/forms/java/frmxlate.jar oracle.forms.diagnostics.Xlate datafile=forms_8842.trc outputfile=forms_trace1.html outputclass=WriteOutHTML

Here,
datafile=forms_8842.trc is the name of trace file that got created due to activities on forms.
outputfile=forms_trace1.html is the name of output file that will get created. You can use html or .txt outputclass=WriteOutHTML parameter decides if the output file should be HTML or TXT.

If you use just WriteOut then output file will be TXT.
If you use WriteOutHTML then output file will be HTML.