As Published In
Oracle Magazine
March/April 2009

TECHNOLOGY: SQL Developer


Improving Application Performance

By Sue Harper

Manage and tune Oracle Application Express applications with Oracle SQL Developer.

Oracle SQL Developer provides capabilities for browsing and working with Oracle Application Express applications. This column shows how developers and DBAs can use Oracle SQL Developer to examine and tune Oracle Application Express code. You’ll learn how to

 

  • Use the Oracle SQL Developer interface to browse and review code in Oracle Application Express quickly. This interface lets you see regions in Oracle Application Express applications and view the SQL and PL/SQL code within those regions.

     

  • Use Oracle SQL Developer’s code refactoring capabilities to turn anonymous PL/SQL blocks created in Oracle Application Express into compiled stored procedures. Anonymous blocks are compiled at runtime, so you can improve application performance by replacing them with compiled PL/SQL.


This column’s examples require Oracle SQL Developer 1.5.0 or above and Oracle Application Express 3.0.1 or above. (Note that you cannot step through the examples on the hosted version of Oracle Application Express at apex.oracle.com.) You also need access to an unmodified version of the sample application in an Oracle Application Express workspace associated with the sample HR schema that’s available with the default database installation. The sample application installs automatically when you create an Oracle Application Express workspace.

Getting Started

Start Oracle SQL Developer, create a database connection for the HR schema, and connect. (For detailed information on creating a connection, follow the link to “Creating a Database Connection” in Next Steps or see “Making Database Connections” in the May/June 2008 issue of Oracle Magazine.) In the Connections navigator, find and expand the Application Express node for the HR schema. You should see Sample Application in the expanded node.

Managing and Browsing Oracle Application Express Applications

Right-click Sample Application in the Connections navigator to display the context menu. As you can see in Figure 1, you can perform management tasks such as renaming, importing, or deploying the application.

 

figure 1
Figure 1: Managing Oracle Application Express applications in Oracle SQL Developer


When you selected Sample Application , a series of tabs or display editors associated with the application opened in the main window. (If this did not happen, double-click to open the display. Single-click and double-click behavior is governed by a preference. To change this preference, expand the database tree and select Advanced Parameters .) The display editors describe high-level objects defined within the application, such as the number of pages or lists of values (LOVs). The SQL tab enables you to review the complete code for the entire application.

Select the LOVs tab to see a list of all of the LOVs within the application. Select any LOV in the list to view its details. If the LOV is static, the detail will be displayed in the Static Values tab below. If the LOV is dynamic, the detail will be displayed in the Query tab.

Although you can’t update LOV values in Oracle SQL Developer, you can use the information displayed in the LOVs tab to determine whether to update them in Oracle Application Express.

Expand the Sample Application node in the Connections navigator, and select 1. Oracle Sample Application , which is page 1 of the application. You can review all the features of the page, including items and buttons, by tabbing through the displays. You can see that page 1 has two hidden items and no buttons. Select the Regions tab; there are four regions available, each with a region source. This source is what you are most interested in exploring, particularly when it contains SQL or PL/SQL statements.

Under the Sample Application node in the Connections navigator, select 7. Add/Modify Customers and the Orders for this Customer report.

This somewhat involved piece of code selects columns for several tables. Double-click the code to invoke the edit button that appears to the right of the selection, and then click the button. Copy the SQL code in the edit window, click Cancel , and paste the code into a blank SQL worksheet for the HR user. Click anywhere in the SQL code in the SQL worksheet, click the Execute Explain Plan button (or press F6), and review the output.

After you tune or amend code in Oracle SQL Developer, you can replace the original code in the Oracle Application Express application. In the next exercise, you’ll drill into a piece of PL/SQL, modify it, and replace the code in the application.

Refactoring Anonymous Blocks

Whenever possible, it is best for applications to call compiled PL/SQL code. If your application is full of uncompiled code, the little hits on performance that result from runtime compilation can add up to cause a significant impact.

Select the 14. Place Order node in the Connections navigator. Click the Regions display editor. Now double-click the source for the Order Header region, which is a PL/SQL source type. Click the edit button, and then select and copy the entire anonymous block. Click Cancel , and paste the code into a blank SQL worksheet.

Press F5 to run the script. The Script Output window displays the following:

 

Bind Variable “P14_ORDER_ID” is 
NOT DECLARED
anonymous block completed


Indeed, the code does contain P14_ORDER_ID. The PL/SQL code expects a parameter, so you need to refactor it to fix the error, using the following steps:

1. Select the PL/SQL anonymous block in the SQL worksheet.

2. Right-click to invoke the context menu, and select Refactoring -> Extract Procedure .

3. In the dialog box, select the Stored option and give the new procedure the name PLACE_ORDER. Click OK.

A new window displaying the syntax for creating the PL/SQL procedure is now displayed. Remember that you still need to take the parameter into account. You could make the necessary updates here, but you’ll use the PL/SQL editor instead.

4. Click Yes to close the window.

5. In the Connections navigator, select the Procedures node for the HR user. Right-click, select Refresh , and then expand the node. You’ll now see the PLACE_ORDER procedure there.

6. Double-click the PLACE_ORDER procedure to open the PL/SQL editor. (The first tab is read-only, and the second is the code editor.) Select the editor, and click the Compile button. The code highlights errors, and as you’d expect, :P14_ORDER_ID is highlighted.

7. Modify the procedure to accept a parameter for P14_ORDER_ID, as shown here in the italic portions of the first several lines:

 

create or replace
procedure place_order (
                               
p14_order_id number
) as begin for x in (select c.cust_first_name, c.cust_last_name, cust_street_address1, cust_street_address2, cust_city, cust_state, cust_postal_code from demo_customers c, demo_orders o where c.customer_id = o.customer_id and o.order_id = P14_ORDER_ID) loop


Note also that the colon preceding P14_ORDER_ID has been removed.

8. Click the Compile button. The error highlighting should disappear.

Updating Your Application

Next Steps



 READ more SQL Developer

LEARN more about
Oracle SQL Developer
creating a database connection

Now you’re ready to update the page 14 code in Oracle Application Express to use the stored procedure you just compiled. Invoke Oracle Application Express, and log in as HR. Click Application Builder , and then double-click Sample Application to reveal the application pages. Double-click Page 14 - Place Order to reveal the details for the page. You are working with regions—specifically, the Order Header region.

Click the Order Header region, and scroll down to the source region. Select all the code in the source region, and replace it with

 

place_order(:P14_ORDER_ID);


Click the Apply Changes button at the top of the screen. To test the modified application and this specific piece of code, run the sample application and place a new order.

Return to Oracle SQL Developer and to the Application Express node in the Connections navigator. Expand Sample Application , and select 14. Place Order . Select the Regions tab, and click Refresh . The Order Header source region source should now correctly display

 

(CLOB) place_order(:P14_ORDER_ID);


as shown in Figure 2.

 

figure 2
Figure 2: Refactored anonymous block showing procedure call


Conclusion

In this column, you have seen that you can easily use Oracle SQL Developer to browse applications built in Oracle Application Express. Better yet, you can review and tune any SQL and PL/SQL code in the application. Use Oracle SQL Developer and Oracle Application Express together to improve your productivity and your applications.

 



Sue Harper
is an Oracle senior principal product manager based in London. Her technical blog, sueharper.blogspot.com, addresses Oracle SQL Developer features.

 

Send us your comments