Express Lane Development

Use Oracle Application Express to quickly build roadworthy mobile applications.

By Joel Kallman

July/August 2016

Oracle Application Express is a no-cost feature of Oracle Database for building web applications. But in addition to building modern, responsive, and accessible web applications, Oracle Application Express enables you to easily build mobile applications.

Oracle Application Express mobile application pages use the open source jQuery Mobile framework. Using Oracle Application Express with the jQuery Mobile framework, you can quickly create jQuery Mobile–based web pages in applications for mobile devices such as smartphones and tablets.

In this issue of Oracle Magazine, you’re going to build a simple Oracle Application Express and jQuery Mobile–based application to manage events from your mobile device. You’ll also see how this application can be easily customized and extended from the declarative development interface of Oracle Application Express.

This article’s sample application is built in Oracle Application Express 5. If you’re not already running Oracle Application Express 5 or later locally, you can request a free workspace. You will also need to download and unzip the SQL script file for this article to build the sample data.

Creating the Database Objects

Before you begin building the application, you will first need to create the database objects that will support the mobile application. The supplied SQL script will drop these demonstration objects (if they exist) and re-create them, so be careful when rerunning this script.

  1. In a web browser, log in to Oracle Application Express and click SQL Workshop.
  2. Click SQL Scripts.
  3. Click Upload, choose the downloaded and unzipped apex_mobile.sql file, and then click Upload.
  4. Click Run in the Run column.
  5. Click Run Now.

The database objects for this sample application—including the EVENTS and EVENT_TYPES tables—should now be created.

Creating the Application
  1. Click the Application Builder tab.
  2. Click Create and then Mobile.
  3. Enter Mobile Events for Name, and click Next.
  4. Click Add Page.
  5. Click List View and Form.
  6. Choose EVENTS for Table Name, NAME for Display Column, and Modal Dialog for Form Page Mode.
  7. Click Add Page.
  8. Click Add Page again.
  9. Click List View and Form.
  10. Choose EVENT_TYPES for Table Name, NAME for Display Column, and Modal Dialog for Form Page Mode.
  11. Click Add Page.
  12. Click Create Application.
  13. Click Create Application.

And voilà! In just a couple of minutes, you’ve created a fully functional mobile application in Oracle Database.

By selecting Mobile when you created the application, you elected to use a mobile user interface. User interfaces in Oracle Application Express specify a collection of HTML templates and user interface controls. The HTML templates for the mobile user interface are based on jQuery Mobile, which is designed to work with all popular smartphones and tablets. It is touch-optimized, it can be used in low-bandwidth network environments, and the look and feel approximates a native mobile application.

Click Run Application, and log in with your workspace credentials. You’ll initially be presented with a rather empty-looking home page. In the navigation menu in the upper left of the application, click Events. Your application should look similar to Figure 1.

Figure 1. First look at Events page

Experiment by using your newly created application. Enter interview in the search bar at the top of the page, and press Enter. Click the X in the search bar to clear the search bar text. Click one of the event names to edit the details of the event. Click the X in the upper corner of the edit window to close it. In the navigation menu, click Event Types to display the list of event types. Although this application is not quite complete, it’s easy to see how simple it was to create this foundation.

Customizing the Form

When you explored your new application, you might have noticed that the edit form for events didn’t look quite right. Where the type of event should have been displayed, instead there was a big number. Also, no time was displayed for the start and end dates. Let’s enhance the application by making slight modifications to the form. To do this, use the Page Designer interface of Oracle Application Express.

  1. Navigate in your web browser to the browser tab running the Application Builder (not the browser tab running your application).
  2. In the list of pages toward the bottom of the page, click the icon for page 3.
  3. In the left tree of the Page Designer, click P3_START_DATE. You’ll notice that the P3_START_DATE item is also selected in the Grid Layout section in the middle of the Page Designer and the properties of P3_START_DATE are displayed in the Property Editor on the right.
  4. In the Property Editor on the right side of the page, change Type to Date and Time.
  5. In the Grid Layout section (in the middle of the page), click P3_END_DATE.
  6. In the Property Editor on the right side of the page, change Type to Date and Time.
  7. In the Grid Layout section, click P3_EVENT_TYPE_ID.
  8. In the Property Editor, change Label to Event Type and change Type to Select List.
  9. Still in the properties for P3_EVENT_TYPE_ID, in List of Values Type, select SQL Query.
  10. For the SQL Query attribute, enter select name, id from event_types order by 1
  11. For Display Null Value, select No.
  12. Click Save in the upper right of the Page Designer to save all the changes to the application definition.

Navigate to the tab in your web browser where you first ran the application to run the updated version. Note that Event Type is now rendered as a select list. Also note that the time component of the start and end dates is correctly shown. Although the actual value of the start and end dates may appear to be in a rather unfriendly format, these are, in fact, being rendered as HTML5 datetime-local input types. Web browsers on modern mobile devices will interpret this input type and present a corresponding mobile-appropriate input control for the date and time. For example, Figure 2 presents a customized form running on an iPhone. Note the mobile-friendly date control for editing a date field.

Figure 2. Events page after application updates, running on an iPhone

Completing the Picture As you add more events in this application, it might become difficult to view the information in a list view. And what better way to present relevant date-based information than in a calendar? Oracle Application Express includes an easy-to-use calendar component that can render date- and time-based information in monthly, weekly, daily, and list views. And all you need to know to create this is a simple SQL statement.
About Oracle Application Express
Oracle Application Express is a no-cost web development framework for Oracle Database. Oracle Application Express runs wherever Oracle Database runs, from the free Oracle Database, Express Edition on a laptop all the way up to an Oracle Exadata system or Oracle Cloud.
  1. Navigate in your web browser to the browser tab running the Application Builder (not the browser tab running your application).
  2. Navigate to page 1 in the Application Builder. If you’re currently displaying the Page Designer, enter 1 in the Page Finder in the top middle of the page and click Go.
  3. The bottom middle of the Page Designer is the Gallery section. Click the Calendar region component in the Gallery, and drag it up to the CONTENT_BODY of the page.
  4. In the Property Editor on the right, enter Events for Title.
  5. Enter the following statement for SQL Query:
    select, e.start_date, e.end_date, e.description, event_type
     from events e, event_types t
     where e.event_type_id =
     order by e.start_date;
  6. Press the Tab key on your keyboard to navigate to the next attribute. SQL Query will be validated, and additional required attributes will be highlighted.
  7. In the tree on the left side, click the red-highlighted Attributes.
  8. For Display Column, select NAME. For Start Date Column, select START_DATE. For End Date Column, select END_DATE.
  9. For Show Time, select Yes.
  10. In Supplemental Information, enter &DESCRIPTION. (Include the trailing period; the value is a shortcut syntax for the Description column.)
  11. Click Save.

Navigate in your browser to the tab running your application, and refresh your browser. Navigate to the home page of your application by clicking the navigation menu and clicking Home. You should now be presented with a calendar view of your Events data, enabling you to quickly switch between the yearly, monthly, weekly, and daily views.

Using a tablet or smartphone, enter the URL (the one up to and including the Application ID) to run your application. You should be able to log in and navigate through your application just as easily as in a native application. Your application should look similar to what is shown in Figure 3.

Figure 3. Events page on a mobile device


It should now be apparent how easy it is to create mobile applications with Oracle Application Express. In just a few minutes, you were able to create a very elegant database application that looks and feels like a mobile application. To “distribute” this application, all you need to do is give someone the URL to your application. And other than writing one SQL statement, you accomplished this by writing no code.

In addition to mobile user interfaces, Oracle Application Express supports the creation of applications with a responsive user interface. Which type of user interface is better for mobile device users: responsive or jQuery Mobile? Each has its merit, but with Oracle Application Express, it’s entirely up to you. You can do both!

Oracle Application Express runs wherever Oracle Database runs, and Oracle Application Express is also available in Oracle Database Cloud. This is a powerful combination and part of the reason why Oracle Application Express is so compelling for Oracle Cloud: within minutes, you can begin building and deploying desktop and mobile applications on Oracle Cloud infrastructure, all managed by Oracle.

Next Steps

 DOWNLOAD Oracle Application Express 5.

 DOWNLOAD the SQL script for this article.

 READ more about Oracle Application Express.

 READ more about Oracle Database Cloud Service.

 REQUEST a free workspace.


Photography by Meric Dagli, Unsplash