Purpose

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

Time to Complete

Approximately 45 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.1 to create and modify a mobile web application.

What Do You Need?

• Visit Oracle Help Center for Cloud.
• Access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
• Oracle Application Express Release 5.1 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.
• The files.zip file into your working directory.

• All files for this hands-on activity are located in the Home/Desktop/APEX_Track/files folder.

• The following files are used in this hands-on activity:

  • OBE.sql

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. In the Application Express login page, enter the following login credentials and click Sign In:

   • Workspace: obe

   • Username: obe

   • Password: oracle

   

2. After logging in, click the down arrow next to the SQL Workshop tab, and then select SQL Scripts.

   

3. Click Upload.

   

4. Click Browse.

   

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

   Locate the OBE.sql file from the files folder.

   

   
   

   Click Upload.

   

5. Click Run button.

   

6. Click Run Now.

   

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 App Builder and select Create.

   
   

   

2. Click Mobile.
   

   
   

   

3. Enter Mobile App for Name. Click the Theme Style select list to view the available theme colors. Select Blue and click Next.

   
   

   

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

   
   

   

5. 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 Parent Page, DEPARTMENTS for Table Name, and DEPARTMENT_NAME for Display Column. Click Add Page.

   
   

   

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

   
   

   

7. Select List View and Form. Select Home(1) for Parent Page, PROJECTS for Table Name, and PROJECT_NAME for Display Column. Click Add Page.

   
   

   

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

   
   

   

9. 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 EMPLOYEES for Table Name. Click Add Page.

   
   

   

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

    
    

    

11. A Reflow report collapses the table columns into a stacked presentation. Click Reflow Report. Select EMPLOYEES for Table Name. Click Add Page.

    
    

    

12. 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 Page 6 of the Employees of Column Toggle Report page type.

    
    

    

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

    
    

    

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

    
    

    

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

    
    

    

16. Click Next.

    
    

    

17. Accept the default and click Next.

    
    

    

18. Click Next.

    
    

    

19. Click Create Application.

    
    

    

20. Your application has been successfully created. The application definition is displayed with the various pages that you added using the Create Application wizard.To view your application, click Run Application.

    
    

    

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

    
    

    

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

    
    

    

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

    

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

    

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

    

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

    
    

    

27. 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. Similarly, to view the Projects list view and Projects form details, click the panel icon.

    
    

    

28. Click the right arrow next to Home.

    
    

    

29. Click Projects.

    

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

    
    

    

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

    
    

    

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

   

4. Enter EmpDept Chart for Page Name. Click Next >.

   

5. Select Create a new navigation menu entry and click Next >.

   

6. Select SQL Query radio button and copy and paste the following query into the query field. Click Next.
   select null link, d.department_name label, e.emp_count value from (select department_id, count(*) emp_count from employees group by department_id) e, departments d where e.department_id = d.department_id

   

7. Select LABEL for Label Column and VALUE for Value Column. Click Create.

   

8. Click Save and Run Page icon.

   

9. The Employees per Department chart is displayed. Click the panel list button icon.

   

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

    

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 (Departments) page from the Popup List of Values of Page Finder.

   

2. Click the DEPARTMENTS List View region in the Rendering section of the Page Designer.

   

3. In the Property Editor - Region section of the Page Designer, replace the SQL Statement with the query provided below.

   select department_name, department_id, (select count(*) from PROJECTS p where p.project_dept = d.department_id) Projects from DEPARTMENTS d

   

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

   

5. 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 text box, enter 10 and click Save.

   

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

   

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

   

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

   

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

   

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

    

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

    

12. 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 Location. In the List of Values property, select SQL Query for Type and enter the below given SQL Statement. Click Save and Run Page.
    select city d, location_id r from locations

13. 

14. Click the Panel List icon.

    

15. Click Home.

    

16. Click Departments.

    

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

    

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

    

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

    

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

    

21. Select the right arrow next to Home.

    

22. Select Departments.

    

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

    

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

    

25. 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 (Projects) from the Popup List of Values of Page Finder.

   

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

   

4. In the property editor, under Settings, check Advanced Formatting and Show List Divider features, and enter the code given below for Text Formatting.

   <h1>&PROJECT_NAME.</h1>

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

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

   

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

   

6. Next, you select the Projects form page to make changes to the items. Click the Go

   

   
   

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

   

   
   

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

   

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

   

9. 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 departments

   

10. You want to change P5_STATUS also to a select list with status types. Click P5_STATUS under the Rendering tab.

    

11. 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 projects

    

12. Click the Panel list button icon on the page to go back to the Home page.

    

13. Click the right arrow icon next to Home.

    

14. Click Projects.

    

15. Notice that the projects have been divided based on the Department Id.

    

16. 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. Notice that the Projects are displayed based on the Search field.

    

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

    

18. Change the Status of the project from Open to Planned. Click Save.

    

    
    

    

19. The changes have been saved. To verify the changes, click Maintain Support Systems again. 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. In the Rendering section of the Maintain Projects page, 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

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

   

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

   

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

   

6. 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 and Run.
   select city from locations where location_id = (select location_id from departments where department_id = :P5_PROJECT_DEPT)

   

7. Click the panel list button icon.

   

8. Click the right arrow next to Home.

   

9. Click Projects.

   

10. Click Maintain Support Systems.

    

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

    

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

    

13. The Location changes based on the Department Name selected. Click panel

    

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. 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. Navigate back to the page designer, and click Popup List of Values: Page Finder.

   

6. Select Employees Column Toggle Report page.

   

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

   

8. The available columns are displayed. Click FIRST_NAME.

   

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

   

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

    

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

    

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

    

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

    

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

    

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

    

16. In the Rendering section of Page Designer, click EMPLOYEE_ID.In the Property Editor - Column section of the Page Designer, make sure 1 - Hide Last for Priority is selected. Click Save and Run Page.

    

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

    

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

    

19. 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. Click the panel list button icon.

   

2. Click Employees Reflow Report from 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.

   

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 icon, and click Home.

   

6. Click Projects.

   

7. Click one of the Projects.

   

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

   

9. Change the Project End Date to a different date, and click Save.

   

10. The date has been updated.

    

• Creating Mobile Applications with jQuery Mobile Support

• Oracle Technology Network Application Express Home Page

• Oracle Application Express Learning Library