With Oracle Application Express, developers can quickly build database-driven applications ranging from small departmental applications to multithousand-page commercial applications supporting tens of thousands of users. The recently released Oracle Application Express 5 greatly improves developer productivity while making it easier to create attractive UIs in modern, responsive, and accessible web applications.
To introduce you to some of the new features in Oracle Application Express 5, this article walks through the solution to a simple problem scenario that many enterprises face: the need to move data from a spreadsheet to an Oracle database and manage the data through a multiuser, scalable, and modern web application. You’ll create a new Oracle Application Express application that uses data from a comma-separated file (saved from a spreadsheet) to generate versatile, customizable, and highly usable interactive reports.
To follow the steps in this column’s exercise, you can use the hosted instance of Oracle Application Express 5.0 at apex.oracle.com. (You must request a free workspace to use this hosted instance.) You can also use a local instance of Oracle Application Express 5.0. Alternatively, from Oracle Technology Network, you can download the Database Application Development virtual machine, which includes Oracle Application Express 5 along with a preconfigured Oracle Database 12c, Enterprise Edition Release 1; Oracle REST Data Services; Oracle SQL Developer; and Oracle SQL Developer Data Modeler.
Download the o55apex-2602732.zip file, and unzip it to a convenient location on your computer. Then log in to Oracle Application Express.
With the preceding steps, you created a table in Oracle Database and inserted spreadsheet data from the tasks.csv file into the table as rows. And you created a fully functional, multiuser report from this table, along with input and update forms—all accessible from a web browser.
Click the Run Application icon, and log in with your workspace credentials. The application runs in either a new window or a new tab, depending on your web browser. Your application home page should look similar to Figure 1.
Figure 1: Web application created from a spreadsheet
Experiment with your newly created application’s UI. Click the pencil icon to edit any of the table’s rows via the update form, modify the data, and then apply the changes. Page through the results of your report. Enter environment in the search box, and click the Go button. Select and deselect the check box next to your report filter for environment. Reduce your browser window. Note that both the home page and the form pages automatically fit into the visible space, showing that the application layout works equally well on small-screen devices as well as desktop computers.
Using an interactive report like the one that starts on page 1 of your application, end users can determine which data they want to see and how they want it presented. They can create distinct “views” of that data and can retain these custom views for their own use, as saved reports.
As the application’s developer, you can create custom views that all users can choose from. As an example, now you’ll create a second way to report on the data in the TASKS table. This new report will be sorted by Start Date and grouped by Project, and it won’t display the End Date column.
From now on, any end user of the application can choose this second report from the report selection list to the right of the Go button.
A new feature in Oracle Application Express 5 is the ability to pivot the results of an interactive report. This feature makes use of the PIVOT operator in SQL, but you don’t need to know anything about the PIVOT syntax. The coding is all done for you, behind the scenes, in the formatting of interactive reports.
You just created a report on the TASKS data, this time showing the total budget amount for each of the projects, organized by project status. Under the covers, Oracle Application Express is adding the PIVOT clause on the SUM of BUDGET and for the distinct values of STATUS. As before, you can save these settings as an alternative default report, displayed as an option to the application’s end users.
As you explore the running application, note that the insert form (which opens when you click the Create button) and the update form are each displayed on a separate full page. In modern web applications, though, it’s common to present such forms in a modal dialog window. With modal dialog windows, the application’s main page remains visible but is effectively disabled—a child window is available for user input. This arrangement makes the expected action more obvious to the user.
Oracle Application Express 5 supports modal dialog pages, and it’s easy to convert an existing form page to a modal dialog page:
Now edit a row in the running application and note that the update form is rendered as a modal dialog. Edit a value, and click Apply Changes. Note that the dialog window closes and that the report on the home page is automatically refreshed.
Next, you’ll further refine the update page and make it a bit more usable as a modal dialog.
Template options are another significant feature in Oracle Application Express 5. With them, you can easily control the UI of various elements in your application without having to modify the underlying CSS or HTML templates. Although you can define template options in any Oracle Application Express theme, only the Universal Theme (Theme ID 42) has a wide array of predefined template options. The Universal Theme is the default for new applications in Oracle Application Express 5, and it’s the theme you selected when you created your application.
Now you’ll use the Universal Theme’s template options to improve the usability of the modal dialog for the update form:
Now edit a row from your report. Your dialog window should look like Figure 3. It’s less cluttered, and it’s obvious that the Apply Changes button provides the primary action. The item labels and values are much easier to read and edit.
Figure 3. Refining the modal dialog page with template options
Most likely the date format used to render the date columns in your application isn’t a format to which you are accustomed. You could edit the attributes of each of these report columns and items and set them to a more commonly used date format. But there’s an even easier way to do this, globally, throughout the entire application:
As you’ve seen in this article, Oracle Application Express lends itself well to an iterative style of development, because you can easily switch between the Oracle Application Express development environment and your running application. You can quickly create an application’s building blocks by using the Oracle Application Express wizards and then iterate toward your end result.
DOWNLOAD sample data for this article.
READ more about Oracle Application Express.
Photography by Ricardo Gomez, Unsplash