Create modern web applications in Oracle Application Express without writing a single line of code.

September/October 2015

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.

1. On the Oracle Application Express home page, select Application Builder.
2. Click Create, and then click the From a spreadsheet link.
3. Click the Upload file, comma separated (*.csv) or tab delimited radio button, and click Next.
4. Click Browse. Browse to and choose the tasks.csv file, enter " for Optionally Enclosed By, and click Next.
5. Enter TASKS for Table Name, and click Next.
6. Change Singular Name to Task, and click Next. Click Next again. Accept the defaults, and then click Next one more time.
7. Click Create Application.

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.

1. Ensure that you’re on the home page of your running application (the report on the TASKS table).
2. Click the End Date column heading and the Hide Column icon.
3. Click the Start Date column heading and the Sort Ascending icon.
4. Click the Project column heading and the Control Break icon.
5. Click the Actions button at the top of the report and Save Report.
6. For Save, choose As Default Report Settings.
7. For Default Report Type, select Alternative.
8. For Name, enter Group by Project, and click the Apply button.

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.

1. Navigate to the home page of your running application (the report on the TASKS table).
2. Choose Primary Report from the report selection list.
3. Click Actions, and choose Reset. When prompted to restore the report to the default settings, click Apply.
4. Click Actions, and choose Format -> Pivot.
5. For Pivot Columns, select Status.
6. For Row Columns, select Project.
7. For the functions, select Sum for Function, Budget for Column, and enter Total Budget for Label.
8. Click the Sum check box.
9. Click Apply, and view the results, which should look similar to Figure 2.


10. Figure 2. Pivoted report from the TASKS table

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:

1. Navigate to the running application’s home page.
2. Choose Primary Report from the report selection list.
3. Select Actions -> Reset. When prompted to restore the report to the default settings, click Apply.
4. Click the pencil icon of any row in the report to display the update page (page 3).
5. In the developer toolbar at the bottom of the page, click Edit Page 3. In a separate tab or window, you should now be in the Oracle Application Express development environment, editing page 3 in the Page Designer.
6. In the tree on the left side of the page, ensure that Page 3: Update Form is selected.
7. In the property editor on the right side of the page, change Page Mode from Normal to Modal Dialog.
8. In the left-side tree, select BreadCrumbs 3, right-click, and choose Delete. (In a modal dialog, it’s not necessary to display a breadcrumb trail on the page.)
9. In the left-side tree, under Region Buttons, select GET_PREVIOUS_ID, hold down the Shift key, and also select GET_NEXT_ID. Right-click, and choose Delete. (You’re deleting the pagination buttons to remove clutter from the modal dialog.)
10. In the left-side tree, click the Processing icon at the top.
11. In the left-side tree, in the Branches section, select both Go To Page 3 branches, right-click, and choose Delete.
12. At the upper right, click the Save button.
13. In the page finder at the top of the page, change 3 to 1 and click Go. (Modal dialogs can’t be run directly, so you’re navigating to page 1 to run the application from there.)
14. Click the run button at the upper right of the page (to the right of the Save button).

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:

1. With the update form in the modal dialog displayed in the running application, click Edit Page 3 in the developer toolbar at the bottom.
2. In the tree on the left, immediately under Content Body, select the Task region.
3. In the attributes of the Task Region in the Property Editor on the far right, scroll down to the Appearance section. In the Template Options section, click None detected.
4. In the Template Options dialog box, select Remove Borders for Style, Large for Item Size, and Stretch Form Fields for Item Width.
5. Click OK.
6. In the CONTENT BODY section in the middle panel, scroll down and click SAVE.
7. In the button properties on the right side of the page, change the attribute of Hot from No to Yes.
8. Click Save at the upper right of the page.
9. In the page selector at the top of the page, change 3 to 1 and click Go.
10. Click the run button at the upper right.

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:

1. From the browser tab where you’re running your application, click the Application <n> link near the left end of the developer toolbar.
2. In the Application Builder, click Edit Application Properties at the upper right.
3. At the upper left, click Globalization.
4. For Application Date Format, enter any valid Oracle date format. In the United States, you’d probably enter MM/DD/RRRR. In Europe, you might enter DD/MM/RRRR.
5. Click Apply Changes.
6. Click the run button at the top right of the page.

Oracle Application Express is a powerful framework on top of Oracle Database, and it’s easy to begin building an Oracle Application Express application. Anything you can invoke via SQL or PL/SQL you can easily invoke from your Oracle Application Express application—but without needing to write a single line of SQL, PL/SQL, JavaScript, HTML, or CSS.

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.

Photography by Ricardo Gomez, Unsplash