"The trouble with doing things right the first time is nobody appreciates how difficult the task was" Programming is an imperfect activity. It is nearly impossible to write perfect code every time. Therefore, this paper will discuss the activities associated with debugging forms and reports.

Debugging is given little coverage in the literature. Hopefully, this paper will help fill in this gap by doing the following:

• Providing information to assist the Oracle developer in building a debugging toolkit;
• Reviewing the built-in capabilities of Oracle Devloper/2000; and
• Presenting a number of custom techniques and concepts.

The execution of the code in a form does not follow a fully linear path. Furthermore, code may be found in a variety of locations in the form, menu, library, object library, sub-classed form, or template. Finding the code that executes when triggered by a Forms event may be difficult. Code may be placed in any one of the following locations:

• Program unit functions, procedures, or packages
• Triggers on the form, block, or field
• Object or database tables
• Referenced objects and property class objects

Triggers are the primary cause of confusion for Forms developers. Triggers are fired based upon events. These events are 'triggered' by actions, state changes, validations, programmed calls, change in focus, navigation, or inputs from the keyboard, mouse, serial port, database, operating system, or other source. An event will generally cause more than one trigger to fire. Additionally, triggers may be placed on one or more forms or database objects. Therefore triggers may be fired at the field, block, form, or database levels either before, after or instead of a trigger at another level.

The Oracle Developer/2000 FormsBuilder provides a number of built-in features to assist the developer in debugging an application. A number of traditional built-in techniques are available to developers to debug code. Some of these include the Debugger, the On-Error Trigger, Menu - Help|Error and LAST_ERROR.

The most under-used component of the Developer/2000 tool set has to be the built-in Debugger. This probably is due to the limited documentation available and its limitations compared to similar products in other toolsets. The goal of this section is to explain the usefulness of the Debugger, so that it will become a regular component of the Forms Developer’s debugging tool kit.

To be able to take advantage of the built-in Debugger you must first get into Debug Mode in a running application. The Debugger is initiated by running a form in debug mode. Debug mode is enabled by either clicking on the debug icon shown in Figure 1 or selecting "Debug Mode" from the Program menu.

Figure 1: Debug Icon

You may run the form using the command line option of "debug=yes" if the form has been compiled in Debug mode using f50gen32 as follows:

%oracle_home%/f50gen32.exe module=module.fmb userid=scott/tiger debug=yes compile_all=yes

%oracle_home%/f50run32.exe module

For those who are familiar with the Forms Debugger from Oracle Forms version 4.5, you will notice a few new features added to the product. The additional features the author has identified include:

• Display of Parameter Lists and Values
• Display of inherited source code from sub-classing
• Server side debugging on supported Oracle platforms

Once you have entered Debug Mode, each time you arrive at a break point, execution is interrupted and the Forms Debugger Window becomes active. This window is composed of the following elements:

• Three panes: Navigator, Source and Interpreter
• Window toolbar addition
• Two sub-menus added to the MDI Window

1. Navigator Pane

The Navigator Pane, displayed in the middle of the Debugger Window, shows an object navigator that allows you to select objects to be investigated, or source code to be inspected as shown in Figure 2.

The navigator pane operates exactly like the Object Navigator in the FormsBuilder. Most of the headings are self-explanatory. Module, Global & System Variables, and Command Line Parameters allow you to drill down to see the current value of any variable or block item in the currently executing form.

Under the Stack heading you can find all actively executing program units at the point of interruption. Code can be selected for inspection, and variable values reviewed. Additionally temporary code changes can be made to program units by double clicking on the name and editing it in the pop-up editor.

Hint: When stepping through a form, often the current program unit line can scroll off the page. Clicking on the prior program unit in the stack and back again moves the cursor to the currently executed line in the Source Pane.

The Debug Actions heading displays a list of all breaks created during the session, for review or disabling.

2. Source Pane

The Source Pane displays source code for the currently highlighted program unit or trigger code in the Navigator Pane. This pane is displayed at the top of the Debugger Window.

3. Interpreter Pane

The Interpreter Pane, displayed at the bottom of the Debugger Window, shows commands sent to the debugger. This is the least necessary of the panes for use during simple debugging and cab is turned off, from the View Menu, to provide additional real estate during debugging.

Although, all of the actions that can be executed in this Pane are easily accomplished using Icons, Menus, and mouse clicks in the other Panes. Through the use of cut and paste commands created and executed through other means are easy to re-execute.

4. Toolbar

The Window toolbar includes the following:

• Step - Interprets the next line of code. The Step action also provides control over the number of times to repeat the step or other action. (e.g.: step 5 times)

• Step Over - Skips the next line of code.

• Revert - Performs the equivalent of a raise Form_Trigger_Failure and closes the entire stack, returning you to the current cursor field.

• Collapse and Expand Icons work like the other navigators in Oracle products by opening, closing, fully opening, and fully closing the selected heading.

• Find Filter field and Icons - provide a quick and easy way to locate a program unit, block, field, variable, or object anywhere inside the Navigator Pane, with the option of searching forward or backward.

5. View Menu

The View Menu is added to the MDI Window menu when the Debugger Window is active. The three debugger panes are available to be displayed or hidden as discussed above.

5. Debug Menu

The Debug Menu is added to the MDI Window menu when the Debugger Window is active. The actions available during debugging can all be executed through this menu. Most are also available on the toolbar.

The debugger is easy to use once you've tried it a number of times. The biggest concern is how to get started and how to set breaks so that program execution is interrupted appropriately depending upon the issue you are trying to investigate.

When a form is started in debug mode, you are presented with the Debugger Window prior to execution of any code. This gives you an opportunity to set one or more breaks in the form prior to execution. You also have the opportunity to review source code and check out database objects.

Breaks are markers placed in the source code where the programmer desires to interrupt execution of the application and perform another action. Examples of these possible actions include:

1. Checking the value of variables.
2. Finding where the program is in the Program Unit/Trigger Stack
3. Reviewing source code and objects including Module Objects and Database Objects

Breaks can be set in one of two ways, either programmatically, or during debugging.

Programmatic breaks are set by placing the built-in procedure break directly into the form, menu, or library code.

Oracle's built-in debug_messages can be used to display ongoing messages on the message line about trigger execution while the form runs.

%oracle_home%f50run32.exe module=module.fmx debug_messages=yes

Forms5.0 now supports the use of this feature from within Form Builder as a check box on runtime options.

Unfortunately, these messages cannot be captured through On-Message or On-Error for programmatic use, manipulation or filtering.

Caution should be used when using debug_messages as a message and alert are displayed for each and every trigger fired. When-Timer-Expired should be disabled when using this option, otherwise you'll be overwhelmed by messages and may have difficulty getting to the triggers you wish to inspect.

The Display_Error built-in (usually provided as an option on the Help Menu) displays the last database error encountered. Forms 5.0 provides additional functionality over earlier versions by showing the exact location of the problem in the SQL statement.

Note: The last error in display_error can get lost when trapping the error messages in an ON-ERROR trigger if processing is allowed to continue.

Oracle Forms provides a built-in function Last_Error that displays the last error encountered while performing a block-based SQL statement against the database. This function is available from the Menu under Help. The SQL statement can then be examined for possible errors.

Unfortunately, the problem with the SQL statement is not identified. However, the statement can be cut and pasted into another tool such as SQL*Plus for syntax checking. You may have to strip out or replace some of the lexicals to get a more satisfactory result.

Common errors include:

• Setting a non-base table field as base-table
• Spelling a field name wrong
• Marking a field as Query Only

Although :System.Last_Query is available for display in debug statements and using other techniques, the Last_Error function can serve as a quick method of getting the last query and reviewing it for anomalies. The approach involves using Oracle’s pop-up query window with the Enter_Query trigger as shown in the steps below:

1. Perform an Enter_Query through the menu, fkey, or icon.
2. Type a :X or another lexical in a query field
3. Type a X or other syntactically incorrect statement in the pop-up window
4. Execute Query
5. Call up Help.Error and review the Select statement ignoring and (X).
6. Cut, paste and test, or examine the SQL as described above.

This technique is useful for identifying fields that were not intended to be queried.

Two custom debugging techniques will be presented in this section. These approaches, combined with On-Error and On-Message triggers provide insight into the most common programming techniques required for an advanced Forms developer to possibly build his/her own tool kit.

A common technique for debugging code is to insert messages into the code that display relevant information in the area of the code that may be causing problems.

Our recommendation is to use an alert to display information. The template we use in our company includes the fp_debug procedure that deploys the alert that can display source information from :system variables and other relevant runtime information. The calling sequence is as follows:

fp_debug(i_flag in varchar2, i_msg in varchar2);

eg:

fp_debug('pi',v_value||' '||to_char(:dept.deptno);

Within the procedure are IF statements to test for Debug Mode and a valid debug flag.

if :parameter.in_debug = 'Y' then

if i_flag in ('xx'-- default place holder

,'pi' -- pre-insert DEPT

-- ,'rg' -- when-new-tab TAB1

record-group pop

) then lk_alert.ok(i_flag||': '||i_value||v_std_disp):

end if:

end if:

Note: The rg flag has been disabled by the programmer, but left in place to be used at a later time and as documentation to be stripped from the code or commented out at production time.

The advantage to this approach is that all messages are controlled in one location, thus allowing the programmer or tester to control the debugging environment through the easy ability to disable and re-enable the messages selectively at runtime.

Sometimes it is not all that easy to just display some messages and hope that the programmer can find the source of the problem. It may be useful to see what code is fired and in what sequence along with a log for review and documentation.

A relatively easy method for tracing code that has proven highly effective requires limited coding but careful planning and rigorous adherence to coding standards.

Our template library includes a trace utility that creates a text file log as a trace of the application logic. At the beginning of each trigger and optionally at the end, a call to a simple package procedure is performed. Additionally, calls to the package procedure may be made throughout the code, and included in program units, libraries, and other code locations. A sample calling sequence would be as follows:

lk_debug.trace('when-new-form-instance');

To support optional calls within and at the end of code, the default argument can overridden as follows:

lk_debug.trace('when-new-form-instance','end');

lk_debug.trace('function f_funtion','before loop'||v_value,10);

The first argument is the name of the Trigger or Program Unit. The second argument is a text string for noting where in the code the debug statement is called. This second argument may include relevant data values. The default value is 'Begin'. The third argument provides a debug level for controlling the amount of output, defaulted to 0.

The procedure is overloaded to support debug levels without a text message:

lk_debug.trace('key-nextitem',5):

To maximize the information provided the package actually prints output from a number of relevant system variables and optional fields that the developer may deem important.

The lk_debug.trace package creates and updates the text file log

Tracing code in Reports is less of an issue in Reports 3.0 given the Live Previewer. Now, it is possible to make any change to the Layout Editor and immediately see its impact in the Live Previewer without re-executing the query. Some changes to the layout can even be made right in the Live Previewer.

Occasionally, you'll need to display or identify information in reports without upsetting the format and display of information in the Live Previewer. Oracle provides a set of functions and procedures called "srw routines" for looking at internals and other relevant information for debugging reports.

Even the use of built-in debugging from Oracle requires some advanced planning on the part of the developers. Key to simplifying the debug processes is preparation for the inevitable. Including tracing and messaging procedures in your code will provide pay back many times over in the pre-production and maintenance of application code. In this paper, we have demonstrated were a few simple techniques that are easily implemented yet can prove to be very powerful.

I. Michael Snyder is a Principal at Dulcian, Inc. His 17 years of systems development and consulting experience includes Oracle Forms, Reports, Oraterm, Web Server, PL/SQL and Database Administration.

Michael Snyder is a Scout Master for Boy Scout Troop 772, has taught Database Concepts at the George Washington

University, and has a Masters Degree in Information Technology. Michael is the co-author of an "Advanced Developer 2000 v2.0 Developers Guide" due out in October 1998, published by Que.