10 Debugging an Application

This section describes approaches to debugging an application including viewing Debug Mode, enabling SQL tracing, viewing page reports, and how to manually remove a control or a component to isolate a problem.

This section contains the following topics:

• About Tuning Performance

• Reviewing Session State

• Accessing Debug Mode

• Enabling SQL Tracing and Using TKPROF

• Monitoring Application and Page Resource Use

• Viewing Reports

• Debugging Problematic SQL Queries

• Removing Controls and Components to Isolate a Problem

See Also:

"Application Builder Concepts" and "Using Application Builder"

About Tuning Performance

For applications having a large number of concurrent users, maintaining optimal performance is critical. To optimize your application's performance, remember to utilize the following features:

• Use bind variables within your application whenever possible. You can reference session state values using bind variable syntax in SQL queries and application logic such as PL/SQL executed from processes and validations. Accessing session state using bind variables is the most efficient way to reference session state.

• Include a #TIMING# substitution string in the region footer so that you can view the timing of each region.

See Also:

• "About Bind Variables"

• "Using Substitution Strings"

Reviewing Session State

Many applications are based on data contained within application controls. For example, buttons can display conditionally based on a value stored in session state. You can view current session state for your application by clicking the Session link on the Developer Toolbar.

See Also:

"Using the Developer Toolbar" , "Viewing Session State", "Managing Session State Values", and "Managing Session State and User Preferences"

Accessing Debug Mode

Viewing a page in Debug Mode enables you to track what the Application Express engine is doing as it renders a page. You access Debug mode by clicking the Debug link in the Developer Toolbar.

See Also:

"Using the Developer Toolbar"

Debug Mode displays time codes that correspond to specific Application Express engine actions. This can be useful if you want to determine when the engine is setting session state. The Debug view also shows additional details about item names and computation and processing points. To exit Debug mode, click No Debug in the Developer Toolbar.

You can also use f?p syntax to run an application in Debug mode. Simply call the page and set the Debug argument to YES. For example:

f?p=100:1:&SESSION::YES

See Also:

"Using f?p Syntax to Link Pages"

Enabling SQL Tracing and Using TKPROF

Tracing your session can be a very effective way to debug an application. From a database perspective, each page request is a single database session. If you enable SQL tracing, then Oracle Application Express creates a temporary file you can then analyze using the TKPROF utility.

You enable SQL tracing in Oracle Application Express by using f?p syntax to set the argument p_trace=YES. For example, to trace the display of page 1 in application 100, you would use the syntax:

http:/.../f?p=100:1&p_trace=YES

To use the TKPROF utility:

• Navigate to the directory in which the trace file is created.

• Enter the following to view instructions about using TKPROF utility:

  tkprof help=yes
  
  

See Also:

"Using Application Tracing Tools" in Oracle Database Performance Tuning Guide for information about using the TKPROF program

Monitoring Application and Page Resource Use

Oracle Application Express facilitates the monitoring of resources used by applications and pages by calling the package DBMS_APPLICATION_INFO. Whenever the Application Express engine renders or processes a page, the module is set to APEX and includes the application ID and page ID. Once set, you can query the V$SESSION and V$SQLAREA views to monitor transactions.

Viewing Reports

When isolating an issue within a page, it is important to clearly understand the functions it is performing. To accomplish this goal, Application Builder includes a number of page and application reports.

To view page reports:

1. Navigate to the appropriate Page Definition:

   1. Navigate to the Database Home Page.

   2. Click the Application Builder icon.

   3. Select an application.

   4. Select a page.

      The Page Definition appears.

2. Click one of the following buttons at the top of the Page Definition:

   • Event View links to a report that details currently defined page controls and processes.

   • Object References displays a list of database objects referenced by the current page.

   • History displays a history of recently changed pages.

See Also:

"About Groups", "About Objects", and "About History"

To view application reports:

1. On the Database Home Page, click the Application Builder icon.

2. Select an application.

3. On the Tasks list, click View Application Reports.

4. Select the type of reports to view:

   • Shared Components reports offer information on common elements that can display or be applied on every page within an application. Report examples include Application Comments, Breadcrumb Entries, Database Object Dependencies, Lists of Values, Static Entries, and Messages.

   • Page Components reports offer detailed information on controls and logic that execute when the page is rendered (for example, branches, buttons, computations, items, and regions).

   • Activity reports offer details about developer activity within the current application. Available reports include Changes by Developer, Changes by Developer by Day, Chart of Changes by Developer, and Recent Changes.

See Also:

"About the Database Object Dependencies Report" and "About the Region Search Source Report"

Debugging Problematic SQL Queries

If your query does not seem to be running correctly, try running it in SQL Command Line (SQL*Plus) or in SQL Commands. Either approach will test your query outside the context of your application, making it easier to determine what the problem is.

See Also:

"Using SQL Commands" and "Using SQL Command Line" in Oracle Database Express Edition 2 Day Developer Guide

Removing Controls and Components to Isolate a Problem

If you have problems running a page, try removing controls and components one at a time. Using this approach, you can quickly determine which control or component may be the source of your problem. You can disable a control or component by selecting the Condition attribute Never.

To remove a control or component using conditional attributes:

1. Navigate to the appropriate Page Definition:

   1. Navigate to the Database Home Page.

   2. Click the Application Builder icon.

   3. Select an application.

   4. Select a page.

      The Page Definition appears.

2. Select the name of the control or component you want to disable.

   The appropriate attributes page appears.

3. Scroll down to Condition Type and select Never from the Condition Type list.

4. Click Apply Changes and return to the Page Definition.

5. Try running the page again.

6. Continue to remove controls or components until the page runs correctly.

See Also:

"About the Page Definition", "Editing Page Attributes", "Understanding Conditional Rendering and Processing", and "Running a Page or Application"