As Published In May/June 2008

DEVELOPER: Browser-Based

The Power of Two

Use Oracle SQL Developer to aid Oracle Application Express development.

Oracle Application Express and Oracle SQL Developer are no-cost tools that both work with Oracle Database versions 9.2 and above. Not only can Oracle SQL Developer help you develop database applications but it also lets you browse, export, import, deploy, and remotely debug your Oracle Application Express applications.

This column introduces the ways Oracle SQL Developer and Oracle Application Express work together to aid your development efforts. We'll see how to browse Oracle Application Express information from within Oracle SQL Developer. Then we'll walk through an example of remotely debugging a browser-based application's PL/SQL code from Oracle SQL Developer.

Overview

Using Oracle SQL Developer in conjunction with Oracle Application Express, you can browse across multiple applications and pages and drill down to specifics quickly. For example, in Oracle Application Express, you need two browsers to view the code for two lists of values (LOVs) in two applications simultaneously. In Oracle SQL Developer, this task is as simple as expanding both applications in Connections Navigator. Oracle SQL Developer also provides several very useful and detailed reports—about applications, pages, schemas, and workspaces—specifically for Oracle Application Express (under Data Dictionary Reports).

Oracle SQL Developer lets you export, import, and deploy applications across multiple workspaces, instances, and databases. Right-clicking the application name invokes a context menu that lets you move the application and its supporting objects from one workspace to another. This capability is especially useful for deploying applications from your development environment to testing or production.

You can copy SQL code from your Oracle Application Express regions, LOVs, and validations; paste it into a SQL worksheet in Oracle SQL Developer; and execute the query. Oracle SQL Developer lets you enter values for the bind variables and run explain plan or autotrace against the query to identify performance issues and tune the SQL.

This column's examples require Oracle Application Express 3.0.1 or above and Oracle SQL Developer 1.2.1 or above. (Note that you cannot perform the examples' steps on the hosted version of Oracle Application Express at apex.oracle.com.) You also need an Oracle Application Express workspace associated with the sample HR schema available in the default database installation.

Browsing Oracle Application Express Information

In Oracle SQL Developer, create a connection to the HR schema. See the Oracle SQL Developer column " Making Database Connections" in this issue of Oracle Magazine for step-by-step instructions on making connections from Oracle SQL Developer to a database.

Once the connection is established, you can see an Application Express node under the HR connection in Connections Navigator, as shown (under the XE Dpeake connection) in Figure 1. 

You can now browse the HR user's Oracle Application Express applications, using the detail tabs in Oracle SQL Developer, as shown in Figure 2. 

Remote Debugging

You can use Oracle SQL Developer to debug PL/SQL remotely. This capability is especially useful when an application isn't failing but also isn't producing the results you expect. As an exercise, follow these steps to debug a procedure in the asset manager packaged application deployed in Oracle Application Express:

Step 1: Obtain and install the packaged application. Download, import, and deploy the asset manager application in Oracle Application Express.

1. Download asset_manager_1.0.zip from oracle.com/technetwork/products/database/application_express/packaged_apps/packaged_apps.html#ASSETS, and save the file locally.
2. Unzip the file.
3. In Oracle SQL Developer, connect to the HR schema.
4. Right-click the Application Express node under HR in Connections Navigator, and select Import Application .
5. Browse to and open the unzipped asset_manager_1.0 folder, and select the asset_manager_installer_1.0.sql file.
6. Click Next .
7. Click the Run Install Script check box (see Figure 3), and click Next . 
8. Click Finish .
9. Click OK in the success window.

Step 2: Configure Oracle SQL Developer for remote debugging. Set up Oracle SQL Developer for debugging the application's EBA_ASSET.update_status procedure remotely.

1. In Oracle SQL Developer, connect to the database as a user with DBA privileges.
2. Enter the following code in the SQL worksheet. If necessary, replace PUBLIC with the user defined to run Oracle Application Express in mod_plsql. (PUBLIC is correct if you're using Oracle Database 10g Express Edition.)

grant DEBUG CONNECT SESSION to HR; 
grant DEBUG on HR.EBA_ASSET to PUBLIC;

3. Click Run Script (or press F5).
4. Connect as HR.
5. In Connections Navigator, open the Packages node and click EBA_ASSET .
6. Right-click EBA_ASSET Body , and select Edit .
7. Right-click in the margin on the detail pane, and select Toggle Line Numbers .
8. Scroll down to line 113, right-click in the margin, and select Toggle Breakpoint .
9. Right-click in the main body, and select Compile for Debug . (Note that running a package in debug mode severely hampers performance. Once testing is complete, you should recompile the package.)
10. Right-click HR in Connections Navigator, and select Remote Debug (see Figure 4).
 11. Click OK .

Step 3: Configure Oracle Application Express for debugging. Set up Oracle Application Express to debug the EBA_ASSET .update_status procedure:

1. Log in to Oracle Application Express as HR.
2. Click Application Builder.
3. Click Asset Manager 1.0.
4. Click Page 11 - Inventory Details.
5. Under Processes , click Update Asset Status.
6. In the Source box, replace the existing code with the following:

IF :DEBUG = 'YES' THEN
     dbms_debug_jdwp.connect_tcp('localhost',4000);
END IF;
eba_asset.update_status(:P11_ASSET_ID);
IF :DEBUG = 'YES' THEN
     dbms_debug_jdwp.disconnect;
END IF;

This code is designed to fire only when the page is run in DEBUG mode. The second IF statement disconnects the debugging session.

Figure 1: Oracle Application Express node in Oracle SQL Developer

Figure 2: Detail tabs for an application

Figure 3: Importing and deploying an application

Figure 4: Oracle SQL Developer configured for remote debugging

Figure 5: Remote debugging in Oracle SQL Developer

7. Click Apply Changes.

Step 4: Run the application. Run the asset manager application in Oracle Application Express, first normally and then in DEBUG mode.

1. In Oracle Application Express, navigate back to page 1 and click Run.
2. Log in as HR.
3. Select Assets.
4. Click the edit icon for any asset record.
5. Click the edit icon for an inventory record in that asset record. Change the status value.
6. Click Apply Changes . The application performs normally, because it's not in DEBUG mode.
7. Click the edit icon for the same inventory record.
8. Click Debug in the developer toolbar.
9. Change the status value, and click Apply Changes.
10. Oracle SQL Developer should now have the desktop focus. (If not, the application will show a partially complete process and you should navigate to Oracle SQL Developer manually.)
11. Use the Oracle SQL Developer debugging controls to resume, step over, and step into the procedure (see Figure 5). As you step through the procedure, you can see the data values change. 

Once the procedure finishes executing, the remote debugger disconnects.

You can use remote debugging to step through complex PL/SQL processing that's not performing its intended function. Reviewing the data values as you step through the program helps you identify incorrect logic.

Conclusion

You can use Oracle SQL Developer to perform the following tasks as you develop your Oracle Application Express applications:

• Navigate rapidly through all your applications' components
• Export, import, and deploy applications
• Tune SQL
• Remotely debug PL/SQL

The combination of Oracle SQL Developer and Oracle Application Express can give your Web application development efforts a significant productivity boost.

Send us your comments