Purpose

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

Time to Complete

Approximately 45 minutes.

Introduction

Mobile applications are designed to run specifically on smartphone devices and hence they must be built with extremely minimal, semantic HTML that is optimized for mobile connections. Mobile applications developed with Oracle Application Express are browser-based applications that run inside the browser on the mobile device. Therefore, these applications must have a connection in order to communicate with the Oracle Database and cannot operate in a disconnected environment. Mobile devices that have HTML5 capabilities can utilize all of the capabilities that can be built into the applications, including HTML5 date-pickers, sub-types that display different keypads based on field definition, and so. Older devices will still render the application they will offer less advanced features. The major advantage of developing browser-based applications is that you only need to develop them once for the majority of mobile devices.

To support mobile application development, Oracle Application Express incorporates jQuery Mobile. jQuery Mobile-based web pages use a lightweight framework that enables you to create compact and minimalistic web sites that include only a few images and CSS files. Using jQuery Mobile, these applications can run on the following mobile operating systems: iOS, Android, Blackberry, and Windows Mobile.

To test your mobile applications, you can also download and install some of these recommended emulators to view the mobile application.

• On a Mac, you can use the XCode iPhone Simulator
• On Windows, you can use the following:
• Emulator included in the Android SDK
• Genymotion - which is an Android running in a Virtual Machine
• Andy

Scenario

In this tutorial, you create a mobile application that is based on the Projects and Departments tables. You will create a List and a Form type of pages for both  Projects and Departments.

Prerequisites

Before starting this tutorial, you should have:

• Access to Oracle Database 11g or later
• Installed Oracle Application Express 5.0 into your Oracle Database
• Downloaded and installed the files.zip into your working directory.
• Created a workspace and user to perform this tutorial.

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 the Application Express Login page using your login credentials. Enter your login credentials and click Login.
   

   
   

   

2. To create the tables, you need to run the SQL Script against the database. Click the down arrow next to SQL Workshop and then select SQL Scripts.

   

3. Click Upload.

   

4. Click Choose File.

   

   Locate the OBE.sql file from the files folder that you have downloaded into your working directory.

   
   
   
   Click Upload.

   

5. Click Run button.

   

6. Click Run in Background.

   

7. Click View Results

   

8. Your tables have been successfully created and data has been inserted into them.

   

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

1. Click Application Builder.

   
   

   

2. Click Create.

   
   

   

3. Click Mobile.
   

   
   

   

4. Enter Mobile App for Name. Click the up arrow to select the Theme for the mobile application. APEX 5.0 ships with a new mobile theme called Theme 51, which includes a choice of theme styles. Theme styles provide different color schemes and styling.

   
   

   

5. The two types of Mobile themes that are available are jQuery Mobile Smartphone Theme 50 and Theme 51. The difference between these two theme styles is that Theme 51 uses a List template type and Theme 50 uses a Tab template type. Click jQuery Mobile Smartphone of ID 51.

   
   

   

6. Click the Theme Style select list to view the available theme colors. Blue and Red are the two Theme Styles that a re available. Select Blue and click Next.

   

7. Click Add Page to add pages in the application.

   
   

   

8. Select 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 Home(1) for Subordinate to Page, OBE_DEPARTMENTS for Table Name, and DEPARTMENT_NAME for Display Column. Click Add Page.

   
   

   

9. Click Add Page again to add more pages to the application.

   
   

   

10. Select List View and Form. Select Home(1) for Subordinate to Page, OBE_PROJECTS for Table Name, and PROJECT_NAME for Display Column. Click Add Page.

    
    

    

11. Click Add Page to add another report page type.

    
    

    

12. 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. Click Column Toggle Report. Select OBE_EMPLOYEES for Table Name. Click Add Page.

    
    

    

13. You want to add another report page type. Click Add Page.

    
    

    

14. A Reflow report collapses the table columns into a stacked presentation. Click Reflow Report. Select Home (1) for Subordinate to Page. Select OBE_EMPLOYEES for Table Name. Click Add Page.

    
    

    

15. The pages in your application have been created. You want to edit the names of some pages in your application. Click the Edit icon against ID 6 of the Obe Employees of Column Toggle Report page type.

    
    

    

16. Change the Name as Employees Column Toggle Report and click Apply Changes.

    
    

    

17. You also want to edit the Name of the Obe Employees (7) Reflow Report. Click the Edit icon against this page type.

    
    

    

18. Change the Name as Employees Reflow Report and click Apply Changes.

    
    

    

19. Click Next.

    
    

    

20. Accept the default and click Next.

    
    

    

21. Click Next.

    
    

    

22. Click Create Application.

    
    

    

23. Your application has been successfully created. The application definition is displayed with the various pages that you added using the Create Application wizard.

    
    

    

24. To view your application, click Run Application.

    
    

    

25. In the new tab, enter your login credentials and click Login.

    
    

    

26. The Navigation List shows the two pages that were created under the Home page. 

    
    

    

27. Click the panel button icon on the left corner to view the panel that contains the top level pages that were added at the time of creating the application.

    

28. A panel that contains the top level pages is displayed. Clicking any of the top level pages will display its associated subordinate pages.

    

29. Click Home.

    

30. The subordinate pages Obe Departments and Obe Projects are listed in the list under the Home page are displayed.

    

31. Click Obe Departments.

    

32. 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.

    
    

    

33. 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 and delete the selected record.

    
    

    

34. Similarly, to view the Projects list view and Projects form details, click the panel icon.

    
    

    

35. Click the right arrow next to Home.

    
    

    

36. Click Obe Projects.

    
    

    

37. 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.

    
    

    

38. The Project Details pertaining to that particular project is displayed. The Save and Delete buttons are available to save the modified record and delete the selected record.

    
    

    

39. Click the Application Builder tab to navigate back to the application and modify the application pages.

    
    

    

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 Chart.

   

3. Select Pie & Doughnut and click Next.

   

4. Select 3D Pie and click Next.

   

5. Enter EmpDept Chart for Page Name and Region Name. Click Configure.

   

6. You want to add this page to the Navigation List. Enter EmpDept Chart for Top Level List Item Label, select Yes for Add Page Link to Navigation List and enter EmpDept Chart for List Item Label. Click Submit.

   

7. Click Next.

   

8. Enter Employees per Department for Chart Title. Click Next.

   

9. Copy and paste the following query into the query field. Click Next.
   select null link, d.dname label, e.emp_count value from (select deptno, count(*) emp_count
from emp group by deptno) e, dept d where e.deptno = d.deptno

   

10. Click Create.

    

11. Click Run Application.

    

12. Click the panel list button icon.

    

13. Notice that the EmpDept Chart list entry is listed in the navigation list along with page link to the navigation list. Click EmpDept Chart page link in the navigation list.

    

14. The Employees per Department chart is displayed.

    

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

1. In the Application Builder Home page, click Home.

   

2. In the Rendering section of the Page Designer, click Navigation.

   

3. In the Property Editor - Region section of the Page Designer, notice that the region type is a List. To modify the list, click Shared Components icon.

   

4. In the Shared Components, under the Navigation section, select Lists.

   

5. Select Navigation.

   

6. Click Obe Departments.

   

7. Modify the List Entry Label to Departments and click Next icon.

   

8. Modify the List Entry Label to Projects and click Apply Changes.

   

9. Notice the changes in the Navigation List names. Click Edit Page 1 icon.

   

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.

1. Click the Go To Page icon.

   

   
   

   Select Page 2 (Obe Departments) page from the Popup List of Values of Page Finder.

   

2. Under the Identification heading of the Property Editor - Page section in the Page Designer, change the Name and Title as Departments.

   

3. Click Save.

   

4. Click the OBE_DEPARTMENTS List View region in the Rendering section of the Page Designer.

   

5. In the Property Editor - Region section of the Page Designer, change the Title as Departments, and replace the SQL Statement 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

   

6. Click Save.

   

7. Next, click Attributes to modify the Settings for this region.

   

8. In the Property Editor - Attributes section of the Paqe Designer, under Settings, uncheck the Enable Search feature. In the Counter Column, select Projects. In the Number of Rows textbox, enter 10 and click Save.

   

9. Next,you want to modify the Departments Form page. Click Go to Page icon.

   

10. In the Popup List of Values of Page Finder, select Page Number 3 - Obe Departments.

    

11. In the Property Editor - Page section of the Page Designer of Page 3, change the Name and Title to Maintain Departments and click Save.

    

12. Next, you will modify the Items in the Page. Currently, the item Department_Id is a hidden item and you want to change it to a Number field so that you can add a new Department Id in the form. In the Rendering section of the Page Designer, click P3_DEPARTMENT_ID

    

13. In the Property Editor - Page Item section of the Page Designer, change the Type as Number Field and Label as Department Id. Click Save.

    

14. Next, select P3_LOCATION_ID in the Rendering section of the Page Designer.

    

15. In the Property Editor - Page Item section of the Page Designer, notice that the Type is Number Field. However, you can change it to a Select List where you can select the Location Names from the locations table to be displayed in the form. To do this, change the Number Field to Select List in the Type property. Change the Label to Locations. In the List of Values property, select SQL Query for Type and enter the below given SQL Statement. Click Save.
    select city d, location_id r from mob_locations

    

16. Next, you want to view the changes you made to the pages. Navigate to the Home page that is already available in a different tab.

    

17. Notice that the Navigation items in the Home page have now been changed to Departments and Projects.

    

18. Click Departments.

    

19. Notice that the Label has been changed to Departments and a counter column with the number of projects associated with each Department is displayed.

    

20. Click Administration.

    

21. You will be taken to the Maintain Departments page where the Department Details of the department that you have selected is displayed. Notice that the Department Id (which was a Hidden item, but later changed to a Number Field) is displayed and also notice that the Location item is a Select List. You can modify the Department Details by changing any of these items. Click the down arrow in the Select List of the Location field and change the location to Bombay. Click Save.

    

22. The changes have been applied. Click Administration again to view the changes you made to the Location of that department.

    

23. Notice that the modified record is displayed. Click the panel list button icon to navigate back to the Home page.

    

24. Select the right arrow next to Home.

    

25. Select Departments.

    

26. You now want to create a new Department. Click Create.

    

27. Enter 50 for Department Id, IT for Department Name, and select South San Francisco for Location. Click Create.

    

28. Notice that a new department IT has been added.

    

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

1. Navigate back to the Page Designer tab of the Application of Page 3.

   

2. Click the Go To Page icon.

   

   
   

   Select Page Number 4 (Obe Projects) from the Popup List of Values of Page Finder.

   

3. In the Property Editor - Page section of the Page Designer of Page Number 4 (Obe Projects), change the Name and Title as Projects. Click Save.

   

4. In the Rendering section of the Page Designer section, click Attributes.

   

5. In the Property Editor - Attributes section, expand Settings. Select Advanced Formatting and Show List Divider under Features. Enter the code given below in the Text Formatting text field. Select PROJECT_DEPT in the List Divider Column to divide the Project lists based on the Department.

   <h1>&PROJECT_NAME.</h1>

   <p>Start Date: &PROJECT_START_DATE.</p>

   <p>End Date: &PROJECT_END_DATE.</p>

   

6. Further down, change the Search Type as Server: Like and Ignore Case. Select PROJECT_NAME for Search Column and enter Search By Project Name in Search Box Placeholder text field. In the Layout property, enter Number of Rows as 10 and click Save.

   

7. Next, you select the Projects form page to make changes to the items. Click the Go To Page icon.

   

   
   

   Select Page Number 5 - Obe Projects in the Popup List of Values of Page Finder.

   

   
   

8. In the Property Editor - Page section of the Page Designer of Page Number 5 (Obe Projects), change the Name and Title as Maintain Projects. Click Save.

   

9. In the Rendering section of the page, select P5_PROJECT_DEPT.

   

10. You want to change the Type of this item to be a select list so that you can select the Departments that are available and modify the Projects with a different Department if needed. In the Property Editor - Page Item section of the Page Designer, select the Type to Select List in the Identification property. Select Type as SQL Query in the List of Values property and enter the SQL Statement given below. Click Save.
    select department_name d, department_id r from obe_departments

    

11. In the Rendering section of the Page Designer, click P5_STATUS.

    

12. You want to have a Select List for the Status of the Projects. In the Property Editor - Page Item section of the Page Designer, change the Type to Select List in the Identification property. Select Type as SQL Query in the List of Values property and enter the SQL Statement given below. Click Save.
    select distinct status d, status r from obe_projects

    

13. You now want to run the application to view the changes you made to the Projects List View and Form pages. Select the tab that has the application open and running.

    

14. Click the panel list button icon on the page to go back to the Home page.

    

15. Click the right arrow icon next to Home.

    

16. Click Projects.

    

17. Notice that the projects have been 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 Enter key.

    

18. Notice that the Projects are displayed based on the Search field.

    

19. Click Maintain Support Systems to view the details of this project.

    

20. Change the Status of the project from Open to Planned.

    

21. Click Save.

    

22. The changes have been saved. To verify the changes, click the Project name.

    

23. The changes have been saved. To verify the changes, click the Project name again.

    

24. Notice that the Status has changed.

    

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 Department selected. 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. Navigate back to the Page Designer tab of the Application by clicking Page Designer tab. page.

   

2. In the Rendering section of the Maintain Projects page, right-click Items and select Create Page Item.

   

3. In the Property Editor - Page Item section of the Maintain Projects page, enter P5_LOCATION for Name and select Type as Display Only. In the Source property, select Type as Item and select P5_LOCATION from the Popup List of Values of Pick Item. Click Save.

   

4. Next, you want to create a Dynamic Action for the P5_PROJECT_DEPT item which will display the appropriate Location of the Department whenever the Department Name is changed. Right-click P5_PROJECT_DEPT and select Create Dynamic Action.

   

5. In the Property Editor - Dynamic Action section of the Page Designer, enter Name as Get Location and click Show All button.

   

6. Navigate to the Rendering section, expand Get Location, select True. Click Show.

   

7. In the Property Editor - Action section, change Action property to Set Value. Expand Settings and select Set Type as SQL Statement and enter the SQL Statement given below. Select P5_PROJECT_DEPT for Page Items to Submit. Note that under the Affected Elements, P5_LOCATION is selected with Selection Type as Item(s). Click Save.
   select city from obe_locations where location_id = (select location_id from obe_departments where department_id = :P5_PROJECT_DEPT)

   

8. To view the modifications, you have to run the application. Navigate to the Maintain Projects tab in the browser.

   

9. Click the panel list button icon.

   

10. Click the right arrow next to Home.

    

11. Click Projects.

    

12. Click Maintain Support Systems project. Notice that the Location is being displayed.

    

13. Change the Project Dept select list to a different department. Select IT from the select list.

    

14. The Location changes based on the Department Name selected.

    

15. Click panel list icon.

    

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 columnms 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. Click Employee Id and Email columns to deselect and not display in the report.

   

4. Notice that the Employee Id and Email columns are not displayed.

   

5. A Toggle report can also be used to assign priorities to different columns in the report. Click Page Designer tab.

   

6. Navigate to the Toggle Report page by clicking the down arrow of the Popup List of Values of page list. Home.

   

7. Select Employees Column Toggle Report page.

   

8. In the Rendering section of the Page Designer, click the arrow head aginst Columns to expand the columns available in the report.

   

9. The available columns are displayed. Click FIRST_NAME.

   

10. In the Property Editor - Column section of the Page Designer, select 3 for Priority.

    

11. In the Rendering section of Page Designer, click LAST_NAME.

    

12. In the Property Editor - Column section of the Page Designer, select 3 for Priority.

    

13. In the Rendering section of Page Designer, click EMAIL.

    

14. In the Property Editor - Column section of the Page Designer, select 2 for Priority.

    

15. In the Rendering section of Page Designer, click HIRE_DATE.

    

16. In the Property Editor - Column section of the Page Designer, select 1 - Hide Last for Priority.

    

17. In the Rendering section of Page Designer, click EMPLOYEE_ID.

    

18. In the Property Editor - Column section of the Page Designer, select 1 - Hide Last for Priority. Click Save.

    

19. Click Save and Run Page.

    

20. Notice that all the columns are displayed. To view the columns based on priority, resize the browser.

    

21. Notice that the columns are displayed in the report based on the priority specified.

    

22. By resizing the browser to the size of a mobile device will display only the columns of highest priority.

    

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. Resize your browser by clicking the maximize option.

   

2. Click the panel list button icon.

   

3. Click Employees Reflow Report from the panel list.

   

4. Notice that the report is displayed with all the columns available next to each other when displayed on a tablet or desktop.

   

5. Resize the browser.

   

6. Notice that the horizontal report reflows to a stacked vertical report as the browser width is resized to accommodate on a smartphone.

   

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 Departments.

   

3. The Departments page is displayed. Click one of the Departments to view the department details.

   

4. Notice that the labels have shifted above the items thereby providing a look and feel of a mobile device's browser.

   

5. Click the panel list button icon.

   

6. Click Home.

   

7. Click Projects.

   

8. Click one of the Projects.

   

9. Click the arrow head against the Project End Date.

   

10. A calendar is displayed. Select a different date.

    

11. Click Save.

    

12. The date has been updated. Click the panel list button icon.

    

In this tutorial, you 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 Departments List View and Form pages
• Modify the Projects List View and Form pages
• Enhance the Projects form page by adding a new Page Item and Dynamic Action
• Viewing the Employees Column Toggle report
• Viewing the Employees Reflow report
• View your mobile application by resizing the browser

Resources

• APEX OTN Home Page
• To learn more about Application Express 4.2, refer to additional OBEs in the Oracle Learning Library
• Oracle Application Express: Developing Web Applications
• Oracle University

Credits

• Lead Curriculum Developer: Anupama Mandya
• Other Contributors: David Peake, Marc Sewtz