How To Debug Application Errors In Oracle APEX

At some point during the process of developing or maintaining an APEX application, you will need to step into the world of debugging. Debugging and troubleshooting in Application Express is challenging because of the different technologies used in APEX. You need to put on many hats (for example, SQL, PL/SQL, CSS, and JavaScript) to develop professional apps in Oracle APEX. And this multi-layer development environment demands you to employ a blend of tactics to track and resolve issues.

This tutorial will demonstrate how to use APEX's Debug mode, which is a built-in mechanism used to track down unexpected application behavior.

This tutorial assumes that you have gone through Tree with Checkboxes tutorial and have executed the instructions provided there because here you will use the components of that module to learn debugging in Google Chrome.

Enabling Debug Mode for an Application

The first step is to turn on the debug mode which you can do by using the Debugging attribute on the Edit Application Definition page. A developer who is logged into the workspace where the application resides can always run the application in debug mode.

To enable or disable debug mode:

1.     Open the application, and click the Edit Application Properties button to the right of the application name. The Edit Application Definition page appears.

2.     Click the Properties tab, and select Yes for Debugging.

3.     Click the Availability tab, and make sure that the Status is set to Available with Developer Toolbar. By setting this value, the Developer Toolbar (which contains debugging options) is shown on each page when the developer is logged in to the App builder in the same browser session.

And here are the steps that will walk you through the debugging process:

1.   Run the page you created for the Tree with Checkboxes module. Select Managers from the Select a Group list, and turn off the last two options - Display and Print under Ledgers. After de-selecting the two options, click Save.

2.     In Page Designer, click the Processing tab to modify the Save Selected Segments process. On line 53, in the pattern argument, replace the colon with a comma, as shown in the following illustration.

3.     Save and run the page.

4.     Select Managers from the select list, turn on the two options you turned off in step 1, and click Save.

5.     You will encounter the process error message saying, Something went wrong while saving the selections. This is a generic message, which doesn't provide any reason why the process failed while saving the changes. In addition to this message, a red JavaScript error icon also appears on the Developers Toolbar, as shown in the following figure. Right-click the JavaScript error icon, select Inspect from the context menu, or press F12 on your keyboard. 

6.     Another window will be opened beside the application page, containing the source of that page. In the code window, click the Console tab, and then click the error link.

7.     This time a new browser tab will be opened showing the Debug page, as shown in Figure-5. Scroll toward the middle of this window, where you will find the actual reason for the process failure. It's a numeric or value error, which is fired when you try to:

-  Assign a value to a numeric variable, but the value is larger than the variable can handle.

-  Assign a non-numeric value to a numeric variable, which causes a conversion error. 

The error message also reveals the line number (57), which caused the error. Open the PL/SQL code in Page Designer and examine line 57, which says – if i.Vlevel4 >= 0. Considering the error message reasons provided above, we can say that the value held in Vlevel4 which is being compared with zero, is not a number. If it is not a numeric value, then what is it? What value is contained in Vlevel4 and how to locate this value? Proceed with the remaining steps of this section to get the answer.

An alternate method to call the debug screen is to click the View Debug option in the Developer Toolbar. This option will call a Debug report in a modal window that lists several events in the form of an interactive report. The View Identifier column in this report is a link, which is used to get details of the selected event. Usually, the report displays the most recent event at the top. So, the error you just encountered will be listed on top of all other events. In both cases, you will see the same Debug page as illustrated in Figure-5.

8.     Execute the following statement in SQL Commands to create a temporary table. You will use this table to capture values from the Vlevel4 variable.

CREATE TABLE TEST2 (SEGMENTID_N NUMBER, SEGMENTID_V VARCHAR2(50));

9.    Add a few lines to the Save Selected Segments process, as highlighted in the following figure, to capture the value. The code is available in Debugging folder.

10.   Save and run the page. Select the Managers group, check the Display and Print options, and click Save. The same error message will be displayed again. Now, open Object Browser in a new tab, and examine the TEST2 table, which contains a row displaying Alpha Value: 22:23 in the SEGMENTID_V column, as shown in Figure-7. The code added in the previous step captured this colon-delimited string value. The value indicates that something is wrong in the main FOR loop, which is supposed to get only numbers and not the colon-delimited values. Revert the comma on line 53 with a colon, save and run the page and grant the two privileges to the Managers group. Examine the TEST2 table, which should now have two separate numeric values, captured through lines 57 and 58.

REGEXP_SUBSTR Function

REGEXP_SUBSTR function extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string. In the current scenario, the REGEXP_SUBSTR() function examines the string stored in the P23_LEVEL4 page item, looking for the first substring bounded by colons. It searches for a colon followed by one or more occurrences of non-colon characters followed by a colon. It looks for one or more characters that are not ":", which is done using [^:]+ pattern. The "^" in the brackets represents NOT and "+" means 1 or more.

As mentioned earlier, sometimes you go beyond Oracle APEX's built-in features and use some other technologies to build professional web applications, and these technologies have their own set of debugging tools. This situation poses a big challenge to developers to debug and troubleshoot such apps. In addition to the debug tool, Oracle APEX provides some utility reports that can assist you in the troubleshooting process. For example, Oracle Application Express Advisor can proactively provide advice before the trouble begins.