The Power of TwoBy David Peake
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.
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 reportsabout applications, pages, schemas, and workspacesspecifically 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.
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.
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.
grant DEBUG CONNECT SESSION to HR; grant DEBUG on HR.EBA_ASSET to PUBLIC;
3. Click Run Script (or press F5).
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.
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.
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.
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.
You can use Oracle SQL Developer to perform the following tasks as you develop your Oracle Application Express applications:
The combination of Oracle SQL Developer and Oracle Application Express can give your Web application development efforts a significant productivity boost.
David Peake (firstname.lastname@example.org) is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.