Building a Mobile Web Application Using Oracle Application Express 5.0

This tutorial covers how to create and enhance a mobile web application using Oracle Application Express 5.0.

Time to Complete

Approximately 30 minutes.

Overview

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in the Oracle Cloud.

In this tutorial, you use Oracle Application Express Release 5.0 to create and modify a mobile web application. For more information on Mobile Web Applications, please refer to Creating Mobile Applications with jQuery Mobile Support on Oracle Help Center.

Please keep in mind the following while running this tutorial:

• Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
  • Logging into Oracle Application Express in a Oracle Database Cloud Service: Reference the Oracle Help Center for your Oracle Database Cloud Service. To do this, go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
  • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.

  What Do You Need?

  Before starting this tutorial, you should have:

  • Access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
  • Installed Oracle Application Express Release 5.0 into your Oracle Database (for on-premises only).
  • At least one Oracle Application Express user provisioned. See Creating New User Accounts in Oracle Application Express Administration Guide.
  • Download and unzipped the files.zip file into your working directory.

  Running SQL Script to Load Tables

  Before you build the application, you should run the SQL Script that is used to load tables into the database. The mobile application is built using loaded tables.

  1. Log into Oracle Application Express. Click down arrow next to the SQL Workshop tab, and then select SQL Scripts.
     
  2. Click Upload >.
  3. Click Choose File.
  4. Locate the OBE.sql file in your working directory and click Open.
  5. Click Upload.
     
  6. Scroll to the right, and click the Run icon for the script just uploaded.
  7. Click Run Now.
  8. Once the script completes execution, scroll to the right, and click the View Results icon.
  9. Your tables have been successfully created and data has been inserted into them.

  Creating a Mobile Web Application

  In this topic, you create a Mobile application and add list and form pages to the application.

  1. Click down arrow next to the Application Builder tab, and select Create.
  2. Click Mobile.
  3. Enter Mobile App for Name, select Blue for Theme Style, and click Next >.
  4. A Home page is created by default. You want to add some more pages to this application, click Add Page.
  5. Select the List View and Form page type. List View is optimized to display data and provide easy navigation on Smartphones. This page type builds a two page List View and Form combination of pages. On the first page, you can select a row to update from the List View. On the second page, you can add a new record, and update or delete the selected record.

  Select OBE_DEPARTMENTS for Table Name, DEPARTMENT_NAME for Display Column, and click Add Page .

• Click Add Page again to add more pages to the application.
• Select the List View and Form page type, OBE_PROJECTS for Table Name, PROJECT_NAME for Display Column, and click Add Page.
• Click Add Page again to add a report page to the application.
• You want to add a Column Toggle Report. A column Toggle Report hides less important columns at narrower widths and surfaces a button to open a menu allowing the user to choose the columns to be viewed. Select the Column Toggle Report page type, OBE_EMPLOYEES for Table Name, and click Add Page.
• Click Add Page again to add another report page to the application.
• You want to add a Reflow Report. A Reflow Report collapses the table columns into a stacked presentation when the display size gets smaller. Select the Reflow Report page type, OBE_EMPLOYEES for Table Name, and click Add Page.
• The pages in your application have been created. You want to edit the names of some pages in your application. Click the Edit icon next to Page 6 .
• Change the Page Name to Employees Column Toggle Report, and click Apply Changes.
• You also want to change the Page Name of Page 7. Click the edit icon beside Page 7.
• Change the Page Name to Employees Reflow Report, and click Apply Changes.
• Click Next >.
• Accept the default, and click Next >.
• Select Application Express Accounts for Authentication Scheme, and click Next >.
• Click Create Application.
• Your application is successfully created. The application definition is displayed with the various pages that you added using the Create Application wizard. Click the Run Application icon.
• Enter the Oracle Application Express credentials, and click Log In.
• The Navigation List shows the two pages that were created under the Home page. Click the panel button icon on the top left corner to view the list of the top level pages that were added while creating the application.
• A panel that contains the top level pages is displayed. Clicking any of the top level pages will display its associated subordinate pages. Click Home.
• The subordinate pages Obe Departments and Obe Projects are displayed in the list under the Home page. Click Obe Departments.
• A List View containing the Department Names is displayed. The Create button is available to create a new record. Click Administration to view the Administration department details.
• The department details such as the Department Name and Location Id are displayed. The Save and Delete buttons are available to save the modified record or delete the selected record.
• Similarly, to view the Projects list view and Projects form details, click the panel icon, and click Home again.
• Click Obe Projects.
• A list view of Projects is displayed. The Create button is available to create a new record. Click one of the Project Names to view the Project Details. In this case, click Maintain Support Systems.
• The Project Details pertaining to that particular project is displayed. The Save and Delete buttons are available to save the modified record or delete the selected record.

Adding a New Page to the Panel Menu

In this section, you add a new page of type chart to the Panel menu using the Create Page wizard.

1. In the Application Builder home page, click Create Page >.
2. Click the Chart icon.
3. Select Pie & Doughnut, and click Next >.
4. Select 3D Pie, and click Next >.
5. Enter EmpDept Chart for Page Name and Region Name, and click Next >.
6. Select Create a new navigation menu entry for Navigation Preference, enter EmpDept Chart for New Navigation Menu Entry, and click Next > .
7. Enter Employees per Department for Chart Title, and click Next > .
8. Copy and paste the following SQL query in the SQL query text area, and click Next >.
   select null link, d.department_name label, e.emp_count value
   from (select department, count(*) emp_count
   from obe_employees group by department) e, obe_departments d
   where e.department = d.department_id
9. Click Create.
10. Click the Save and Run Page icon.
11. The Employees per Department chart is displayed.
12. Click the Panel icon.
13. Notice that the EmpDept Chart list entry is listed in the navigation list along with a link to the page.

Modifying the List on the Home Page

In this section, you modify the Name and Title of the Navigation List in the Home page.

1. Go back to the Oracle Application Express, and click the Application breadcrumb from the Page 8 in page designer view.
2. Click the 1 - Home icon.
3. Under the Rendering tab, click Menu Page to open its properties in the property editor.
4. Notice that the Type under Identification is List. To modify the list, click the Shared Components icon.
5. Click Lists under Navigation.
6. Click Menu Page 1.
7. Click Obe Departments.
8. Change the List Entry Label to Departments, and click > to edit next list entry.
9. Change the List Entry Label to Projects, and click Apply Changes.
10. Notice that the list entry names have changed. Click the Edit Page 1 icon.

Modifying Departments List View and Departments Form Pages

In this section, you modify the Name, Title, Region, and Query definitions of the Departments List View and Departments Form pages appropriately. You can make use of the settings available in the List View attributes to display the report based on your requirement. The List View supports the following features:

• Advanced Formatting: This feature allows you to include text formatting conventions for common list patterns like header/descriptions, secondary information and counts through semantic HTML markup.
• Show Image:You can add an image inside a list item as the first child element.
• Show List Divider: This feature allows List items to be turned into dividers to organize and group the list items.
• Has Split Button: In cases where there is more than one possible action per list item, you can use a split button to offer two independently clickable items - the list item and a small arrow icon in the far right.
• Enable Search: jQuery Mobile provides a very easy way to filter a list with a simple client side search feature. The framework will have a search box above the list to filter out list items that do not contain the current search string as the user types.
• Is Nested List View: This feature can be used to populate a new page with the title of the parent in the header and the list of child elements when a list item with a child list is clicked.
• Inset List: You can use this feature if lists are embedded in a page with other types of content. An inset list packages the list into a block that sits inside the content area with a bit of margin and rounded corners.
• From the Page 1 in page designer mode, click Popup List of Values: Page Finder.
• Click 2 (Obe Departments).
• In the property editor, under Identification, change Name and Title to Departments.
• Under the Rendering tab, click OBE_DEPARTMENTS to open its properties in the property editor.
• In the property editor, under Identification, enter DEPARTMENTS for Title. Under Source, replace SQL Query with the query provided below:
  select department_name, department_id, (select count(*) from OBE_PROJECTS p where p.project_dept = d.department_id) Projects from OBE_DEPARTMENTS d
• Under the Rendering tab, click Attributes.
• In the property editor, under Settings, uncheck Enable Search feature, and select PROJECTS for Counter Column. Under Layout, enter 10 for Number of Rows.
• Next,you want to modify the Departments Form page. Click Popup List of Values: Page Finder.
• Click 3 (Obe Departments)
• In the property editor, under Identification, change the Name and Title to Maintain Departments, and click Save.
• By default, P3_DEPARTMENT_ID is created as a hidden item. To change it to a Number field so that you can add a new Department Id from the form, click P3_DEPARTMENT_ID under Items.
• In the property editor, under Identification, select Number Field for Type. Under Label, enter Department Id for Label, and click Save.
• Under the Rendering tab, click P3_LOCATION_ID to open its properties in the property editor.
• Notice that the Type under Identification is Number Field. However, you can change it to a select list displaying Location Names instead of Location IDs. To do this, set the following properties as per the table given below, and click Save and Run Page.

  Property Group	Property	Value
  Identification	Type	Select List
  Label	Label	Location
  List of Values	Type	SQL Query
  List of Values	SQL Query	select city d,location_id r from obe_locations

  

• Click the Panel icon.
• Click Home.
• Notice that the Navigation menu entries in the Home page are now changed to Departments and Projects.
• Click Departments.
• Notice that the Page Title is changed to Departments and a counter column with the number of projects associated with each department is displayed.
• Click Administration.
• Notice that the page title is now changed to Maintain Departments and Location is changed to a Select List with Location Names. Select Bombay for Location, and click Save.
• The changes are applied. To view the changes, click Administration again.
• Notice that the Location is now changed to Bombay.
• Click the Panel icon, and click Home.
• Click Departments.
• You want to create a new Department. Click Create.
• Enter 50 for Department Id, IT for Department Name, select South San Francisco for Location, and click Create.
• Notice that a new department IT is now added to the List View.

Modifying Projects List View and Projects Form Pages

In this section, you modify the Name, Title, Region, and Query definitions of the Projects List View and Projects Form pages appropriately.

1. Go back to the Oracle Application Express, and click Popup List of Values: Page Finder from the Maintain Departments page.
2. Click 4 (Obe Projects).
3. In the property editor, under Identification, change the Name and Title to Projects, and click Save.
4. Under the Rendering tab, click Attributes.
5. In the property editor, under Settings, check Advanced Formatting and Show List Divider features, and enter the code given below for Text Formatting.
   

   &PROJECT_NAME.

   
   

   Start Date: &PROJECT_START_DATE.

   
   

   End Date: &PROJECT_END_DATE.

   
6. Select PROJECT_DEPT for List Divider Column, Server:Like & Ignore Case for Search Type, PROJECT_NAME for Search Column. Enter Search by Project Name for Search Box Placeholder, 10 for Number of Rows, and click Save.
7. Under the Rendering tab, click Attributes.
8. To display the List View report as a list grouped under each PROJECT_DEPT, you have to modify the SQL Query under Source to sort the records by PROJECT_DEPT. Under Source, replace the SQL Query with the below given query, and click Save.
   select *
   from "OBE_PROJECTS"
   order by project_dept
9. Next,you want to modify the Projects Form page. Click Popup List of Values: Page Finder.
10. Click 5 (Obe Projects).
11. In the property editor, under Identification, change the Name and Title to Maintain Projects , and click Save.
12. Under the Rendering tab, click P5_PROJECT_DEPT.
13. Notice that the Type under Identification is Number Field. However, you can change it to a select list displaying Department Names instead of Department IDs. To do this, set the following properties as per the table given below, and click Save.
    

Property Group	Property	Value
Identification	Type	Select List
List of Values	Type	SQL Query
List of Values	SQL Query	select department_name d,department_id r from obe_departments



• You want to change P5_STATUS also to a select list with status types. Click P5_STATUS under the Rendering tab.
• In the property editor, under Identification, select Select List for Type. Under List of Values, select SQL Query for Type, and enter the below given query for SQL Query. Then, click the Save and Run Page icon.
  select distinct status d, status r
  from obe_projects
• Click the Panel icon.
• Click Home.
• Click Projects.
• Notice that the projects are divided based on the Department Id.

  

• Enter a term in the Search field to search the Projects based on the Search item. Type Maintain in the Search field, and press the Enter key to view all the projects having Maintain in its name.
• Click Maintain Support Systems.
• Notice that the Project Dept and Status fields are now select lists. Select Planned for Status from the select list.
• Click Save.
• The changes are saved. To view the changes, click Maintain Support Systems again.
• Notice that the status is now displayed as Planned.

Enhancing the Projects Form Page by Adding a New Page Item and Dynamic Action

In this section, you add a new page item called LOCATION to the Projects form page. You will then associate the P5_PROJECT_DEPT item with a Dynamic Action that will display the Location based on the selected department.

Dynamic Actions are used to define complex client-side behavior declaratively without the need for JavaScript. Using the Dynamic Actions, you specify an action that is performed when a defined set of conditions occur. You can also specify which elements are affected by the action, and when and how they are affected.



1. In the Maintain Projects page, under the Rendering tab, right-click Items, and select Create Page Item.
2. In the property editor of the new page item, set the following properties as per the below given table:

Property Group	Property	Value
Identification	Name	P5_LOCATION
Identification	Type	Display Only
Label	Label	Location
Settings	Save Session State	No
Source	Type	Item
Source	Item	P5_LOCATION



• You want to create a dynamic action so that whenever a different value is selected for P5_PROJECT_DEPT, its corresponding location is displayed in the P5_LOCATION field on the form. RIght-click P5_PROJECT_DEPT, and click Create Dynamic Action.
• Under Identification, enter Get Location for Name, and click Show All.
• Under the Rendering tab, Under Get Location, click Show under True.
• Under Identification, select Set Value for Action. Under Settings, Select SQL Statement for Set Type, P5_PROJECT_DEPT for Page Items to Submit, and enter the below given query for SQL Statement.
  select city
  from obe_locations
  where location_id = (select location_id from obe_departments where department_id = :P5_PROJECT_DEPT)

  Under Affected Elements, select Item(s) for Selection Type, P5_LOCATION for Item(s), and click the Save and Run Page icon.

• Click the Panel icon.
• Click Home.
• Click Projects.
• Notice that Location is displayed based on the Project Dept field's value.
• Change the Project Dept value by selecting IT from the select list.
• Notice that Location value changed. Click the Panel icon.

Viewing Employees Column Toggle Report

In this topic, you view the Employees Column Toggle report. In this report, you can toggle the columns and hide the columns which are less important in the report thereby allowing the user to choose the columns to be viewed.

1. Click Employees Column Toggle Report in the panel list.
2. Notice that all the columns in the report are displayed. You can choose to display only columns that are of importance to you by clicking the Columns button on the top right corner of the screen. Click Columns.
   
   
3. All the columns that are available are displayed here. You can uncheck the columns that you do not want in your report. Uncheck Employee Id, Email, and Department columns.
4. Notice that the Employee Id, Email, and Department columns are not displayed on the report now.
5. A Column Toggle report can also be used to assign priorities to different columns in the report. Navigate back to the page designer, and click Popup List of Values: Page Finder.
6. Click 6 (Employees Column Toggle Report).
7. Under the Rendering tab, click the arrow head against Columns to expand the columns available in the report.
8. Click FIRST_NAME to open its properties in the property editor.
9. In the property editor, under Settings, select 3 for Priority.
10. Click LAST_NAME to open its properties in the property editor.
11. In the property editor, under Settings, select 3 for Priority.
12. Click EMAIL to open its properties in the property editor.
13. In the property editor, under Settings, select 2 for Priority.
14. Click HIRE_DATE to open its properties in the property editor.
15. In the property editor, under Settings, select 1 - Hide Last for Priority.
16. Click EMPLOYEE_ID to open its properties in the property editor.
17. In the property editor, under Settings, select 1 - Hide Last for Priority, and click the Save and Run Page icon.
    
18. Notice that all the columns are displayed. To view the columns based on their priority, resize the browser.
19. As you resize the browser, the columns with lesser priority gets hidden and only the columns with higher priority gets displayed.
20. By resizing the browser to the size of a mobile device will display only the columns of highest priority.
21. Click Maximize to restore the browser size.

Viewing Employees Reflow Report

In this topic, you view the Employees Reflow report. In this report, the table columns are in a stacked presentation that look like block of label/data pair for each row.

1. Click the Panel icon.
2. Click Employees Reflow Report in the panel list.
3. Notice that the report is displayed with all the columns available next to each other when displayed on a tablet or desktop.
4. Resize the browser to a size of a smart phone and notice that the horizontal report reflows to a stacked vertical report.

Viewing your Mobile Application by Resizing the Browser

In this topic, you resize your browser to experience the look and feel of a mobile screen.

1. Click the maximize button of your browser and reduce the width to look like a mobile screen.
2. Click the Panel icon, and click Home.
3. Click Departments.
4. The Departments page is displayed. Click one of the Departments to view the department details.
5. Notice that the labels have shifted above the items thereby providing a look and feel of a mobile device's browser.
6. Click the Panel icon, and click Home.
7. Click Projects.
8. Click one of the Projects.
9. Notice that the labels have shifted above the items thereby providing a look and feel of a mobile device's browser.
10. Change the Project End Date to a different date, and click Save.
11. Notice that the Project End Date is updated.

Summary

In this tutorial, you have learned how to:

• Run SQL Script and load tables
• Create a Mobile Web Application
• Add a new page to the Panel menu
• Modify List on the Home page
• Modify the List View and Form pages
• Enhance a form page by adding a new Page Item and Dynamic Action
• Viewing a Column Toggle report
• Viewing a Reflow report
• View your mobile application by resizing the browser

Want to Learn More?

• Oracle Cloud Home Page
• Other tutorials on Oracle Database Cloud Service - Database Schema Service