Invoke Application Express using the administrator login and select Manage Workspaces. You can either invoke the Manage Workspaces page and select Create Workspace or access the Create Workspace pages from the front panel, as shown in the following screenshot:

The wizard guides you through the steps of creating the workspace and schema. Creating a new workspace also creates the default sample application for the schema associated with the workspace. It is this schema and application that we will use in the examples that follow.

In the example shown in the previous screenshot, we created a workspace called SampleWorkspace and a new database schema called SAMPLE.

You can, at this point, use SQL Developer to connect to the database schema and view the details of the sample application created. However, later in the chapter, we'll return to Application Express to edit an application, and for that you'll also need an Application Express user who can access the workspace, modify, and run the application.

To create an Application Express user and associate it with one or more workspaces, use the Manage Developers and Users page, accessed from the main Manage Workspaces page, as shown in the following screenshot:

Click on the Create button to invoke the Create/Edit User screen and add your new user details to this. In the next screenshot, we show the part of the page with the initial details populated. Use this user to connect to Application Express and edit the applications in the workspace.

To view both the schema objects and the applications owned by the schema, create a new database connection in SQL Developer for the Application Express schema, as shown in the following screenshot:

In addition to viewing the applications, you can use SQL Developer's Connections navigator to review schema objects such as tables, views, and indexes, in the same way as you have browsed, created, and updated any other database schema objects. Application Express provides an SQL Workshop (similar to SQL Developer's SQL Worksheet) to create, edit, and update database objects. You may prefer to use SQL Developer for these activities when working with Application Express.

The next screenshot shows the tables used in the sample application. SQL Developer provides easy access to browse and query these tables and the data they hold.

Expand the Application Express node in the Connections navigator and select the Sample Application. As you select the application, a selection of display editor tabs reveals the application details, including pages, LOVs, and items. This top-level set of display editors includes the scripts to install, reinstall, and upgrade your applications. The following screenshot shows an example of the SQL script for an application called Sample Application (101):

There is also an SQL display editor, just as there is for other objects in the Connections navigator. In this case, it contains the full SQL script of the application.

Using SQL Developer, you can not only review the details of your application quickly and easily by clicking through the display editor tabs, but also compare these details across applications, as shown in the next screenshot. When comparing, you can do the following:

Drilling down into lists of values

Some of the display editor tabs have a set of detail records for each of the master records returned. The list of values (LOV) display editor is one of these. You'll notice that each LOV_TYPE is either Static or Dynamic. The detail records for these are displayed in either a Query or Static Values report. In the following example, the CATEGORIES list of values in the sample application is selected, and the list of static values is displayed in the Static Values report:

You can't use SQL Developer to update these values, but you can use it to quickly review and decide if there are incorrect or missing values, and then use Application Express to update the values.

Focusing on pages

Each of the pages displayed in the Pages display editor is also listed in the Connections navigator under the application node. Each page has its own set of display editors describing the page and providing detail on aspects of the page, including items, regions, and buttons.

If you select each page in the Connections navigator in turn and review the resulting page detail, you'll see that there are high-level page summary changes, detailing the number of buttons, items, and regions defined for each page. To get more details about each of these objects, select the appropriate display editor. In the following screenshot, the Buttons display editor shows the details for the buttons on the Customer Details page:

While reviewing your application in SQL Developer, you may want to make modifications to some of the objects in Application Express. Consider again the example shown in the screenshot above, where a list of buttons is displayed. You can also look back at the previous example, where a set of LOV static values was shown. Each of these is an object defined in Application Express, and you need to know where to find each object in order to update it. Invoke your Application Express environment and edit the Customer Details page.

Notice that each grouping on the Application Express page has a matching area in SQL Developer:

Use Application Express to update any objects in the application and then refresh the view in SQL Developer.

You can select and copy the SQL code into the SQL Worksheet and then tune it as you would any other piece of SQL code. Using the same Page 1, 1. Oracle Sample Application in the example in the previous screenshot, select the Region Source for the region, My Top Orders. This is a SQL query. To select the code, click to highlight the value. Ctrl+C copies the code, and you can then paste it using Ctrl+V into the SQL Worksheet. Alternatively, double-click on the field to expose the edit button, which when clicked invokes a new window displaying the full text. Now, you can use Ctrl+A to select the text and Ctrl+C to copy it. Cancel the window and then use Ctrl+V to paste the code into the SQL Worksheet. You need to remove the word, CLOB, which precedes the text.

Once you have completed the copy and paste, you'll have the SQL in the SQL Worksheet, and you can run it just as you would any other SQL. Often, the Application Express SQL includes bind variables, as this example does. In this case, use the Run Statement (F9) to be prompted for the bind values.

Once you have the SQL code in the SQL Worksheet, you can execute the Explain Plan (F6) to review the code. The following screenshot displays the code and plan details:

It is beyond the scope of this book to delve into tuning code using the results. However, SQL Developer does provide an extensive list of Optimizer Hints in the Snippets dialog, which you can drag into the query. Once you have added the Optimizer Hints, you can re-run the plan and compare the differences. Adding in Optimizer Hints is not a solution for tuning SQL, and it is recommended that you read some of the documentation on tuning and writing queries that is available online. If you have updated the SQL query, you need to return to Application Express and edit and replace the query.

While the SQL queries in Application Express applications may be straightforward, developers sometimes create anonymous blocks when adding PL/SQL code. Certainly, tuning SQL is important to an application, but reducing the calls to anonymous blocks in any application is also very important. Anonymous blocks are compiled at runtime, thus placing unnecessary overhead on the application. If you can identify and replace anonymous blocks with compiled procedures, you can significantly improve the performance of an application since the application calls compiled code.

Consider Page 12, 12. Enter New Order. To work with the PL/SQL code, first edit, select, and then copy the PL/SQL block to the SQL Worksheet, as previously described for the SQL query. The PL/SQL code that is now pasted on the SQL Worksheet is shown in the following screenshot:

This is an anonymous block. SQL Developer provides the facility to refactor to PL/SQL code and extract a procedure. To create a PL/SQL procedure from the anonymous block, select the full PL/SQL text, and use the context menu to invoke the Refactoring menu. Click on Extract Procedure… to invoke the New Procedure dialog. You need to provide a name and ensure that the procedure is stored as shown in the following example:

You can edit the code in the Confirm Running SQL dialog. Some anonymous blocks expect a bind variable, and so when you refactor the code and extract a procedure, you'll need to take this into account. We prefer to review the code in the PL/SQL Code Editor. To do this, accept the default code. This is now available for later review.

Once you have refactored the code, you can browse this code in the Connections navigator. To see the new procedures, expand the Procedures node, and using the previous example, reveal the new stored procedure, named PlaceOrder. Notice that the Application Express bind variable has been replaced by a declared variable and that the refactored code includes the required input parameter, as shown in the following screenshot:

Once you have refactored the code in SQL Developer, you can replace the anonymous block in the Application Express environment. Return to Application Express using the Application Express user created earlier and edit Page 11. The following example shows the Regions area in Application Express, with the Order Header PL/SQL region selected:

Replace the region source with placeorder (:P14_ORDER_ID) and select Apply Changes. To verify that the code has been correctly handled, run the application and place a new order for an existing customer. If you have replaced the code correctly, there will be no errors when you add the order.

In SQL Developer, if you review the updates, you'll see that the anonymous PL/SQL block is now replaced with a call to a compiled procedure.

To learn more about compiling and debugging PL/SQL, refer to Chapter 2, Working with PL/SQL, the chapter focusing on those topics.

The Application Express home page, http://apex.oracle.com, has a link to a long list of packaged applications. These are ready-built and functional applications that you can download, import, run, review, and also edit. They are useful as starter applications, and they also provide examples of code and techniques.

To learn more about importing applications, download and unzip one of the packaged applications, or you can follow the steps by importing one of your own applications. To import a new application, select the Application Express node in the Connections navigator and click on Import Application using the context menu. You need to select an SQL install script to import. Once you have the file, you can set the import options as shown in the following screenshot:

You can select the workspace that this application is imported into. You can also change the following:

Be sure to select the Run Install Script checkbox to install the application in your chosen workspace.

You can change the name and alias of an application using the Modify Application context menu.

In addition, this menu offers additional options, such as being able to send out a global notification. The Modify Application menu is displayed in the previous screenshot with the Global Notification… option selected. Once selected, you should supply the message that you want to send to the users.

When created, a global notification appears on all the pages of an application and is visible to all of the users who start up or access the application, as shown next for the sample application:

You can also set the status of the application, which is useful when you need to carry out maintenance on the application and have a few users accessing the application simultaneously. The choice of status options are as follows:

This facility allows you to deploy a completed application to another schema from outside of Application Express. This is useful when you are working on an application in a "sandbox" environment and you want to move it, say, to test or production schemas. When deploying an application to another schema, this new schema must have access to a workspace in Application Express.

SQL Developer offers users the ability to start and stop the embedded PL/SQL gateway for Application Express (which is shown in the following screenshot). You may not have access to do this in a large shared environment, but it can be useful in a test setup.