Creating and Running a Database Application Using Oracle Application Express 5.0 in Oracle Database Exadata Express Cloud Service

Purpose

This tutorial shows you how to create a database application using Oracle Application Express in Oracle Exadata Express Cloud Service.

Time to Complete

Approximately 80 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 run a database application in the Oracle Cloud, using Oracle Exadata Express Cloud Service.

Please keep in mind the following while running this tutorial:

• Logging into your Oracle Application Express workspace in the Exadata Express 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.
• Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
• Note: If you access the workspace in an on-premises Oracle database, rather than in the Exadata Express Cloud Service showcased in this tutorial, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

Before starting this tutorial, you should:

• Have access to an Oracle Database 11g or later release on your local, non-cloud computer. 
• Download and unzip the files.zip file into your working directory.
• Configure the database environment by performing the following:
• Execute the following environment setup steps in the specified sequence:
• Create an Oracle Application Express user looking at the instructions in Creating New User Accounts in Oracle Application Express Administration Guide.  
• Download the files.zip to your working directory.
• From the Oracle Application Express home page, click the down arrow next to the SQL Workshop tab and select SQL Scripts.
• Upload and run the deinstall_database_obj.sql to reset the application environment.
• Upload and run the install_database_obj.sql to create tables and data used for these labs.

To create a database application with a few initial pages, perform the following steps:

1. From the Oracle Application Express home page, click the down arrow next to the Application Builder tab and select Database Applications.

   

2. Click the Create icon.

   

3. Select Desktop, and click Next >.

   

4. For Name, enter Project Tasks Application, and click Next >.

   

5. By default, a Home page gets created. To add another page, click Add Page.

   

6. Select Master Detail for Page Type. Master Detail builds a two page report and form combination.  Select Home(1) for Parent Page, OBE_PROJECTS for Master Table Name, and OBE_TASKS for Detail Table Name. Click Add Page.

   

7. The Master Detail pages are created. Click Next >.

   

8. Accept the defaults, and click Next >.

   

9. You want to specify the type of authentication used to access this application. For Authentication Scheme, select Application Express Accounts.

   

10. You can also define a date format to be used throughout your application. This date format will be used to alter the NLS_DATE_FORMAT database session setting prior to showing or submitting any page within the application. This format is used by all reports showing dates and is also picked up by form items of type 'Date Picker (use Application Date Format)'. Select an DD-MON-YYYY as Application Date Format for your Application, and click Next >.

    

11. Click Create Application.
    
    

    

12. Project Tasks Application is created successfully with 4 pages. Click the Run Application icon.

    

13. If a Log In screen appears, enter your Oracle Application Express credentials, and click Log In.
    Note:  You can add an Application Express user by selecting Manage Users and Groups from the Administration drop-down menu in the Application Express home page.

    

14. Project Tasks Application home page displays with the navigation menu on the left side of the page. Click Obe Projects.

    

15. A report with the list of projects displays. Select the Edit icon next to one of the projects to view the Master detail Form.

    

16. The Master Detail Form is shown. This page allows a user to edit the details of the Project as well as the details of the Tasks associated with the Project. It also allows users to add new tasks to the current project.
    
    In the master region, you can use the backward and forward buttons to move to previous and next projects. For example, if you have clicked the edit icon next to the Public Website project in the previous step, you see a backward button here.
    
    In the detail region, you see an Add Row button. This button saves any pending changes and then adds another row so that a new task can be added to the project. You should also have a Delete Checked button. This button is used in conjunction with the check boxes to the left of each Task. When you click the Delete Checked button, any tasks that have been "checked" are deleted. If you use this feature, you notice that a deletion confirmation has been built in. This asks you to confirm your deletion before proceeding.
    
    Change the name of the project and click Save.

    

Although the Master-Detail form is fully functional, the presentation can be improved. In this topic, you change the names of the Page Title, Region Title, and change the format of an item on the Master Report. Lastly, you change the sequence of items in the Projects and Tasks page using the Drag and Drop feature. Perform the following steps:

1. You see that the name of the project last edited is modified. Click the Edit Page<n> link on the developer toolbar.

   

2. Expand Columns under Content Body in the Rendering tab.

   

3. Click PROJECT_DEADLINE column to open its properties in the property editor on the right side of the page.

   

4. To change the format of PROJECT_DEADLINE, click the List of Values icon for Format Mask, under Appearance, .

   

5. Select 12-JAN-2004.

   

6. Now, you want to change the title of the page to PROJECTS. Select Page 2: obe Projects in the Rendering tab.

   

7. Under Identification, enter Projects for Name and Title. Click the Save and Run Page icon.

   

8. Notice that the date format of the PROJECT_DEADLINE has changed. But, the Region title is still set to Obe Projects. Click Quick Edit in the developer toolbar.

   

9. Select the Obe Projects region for quick editing.

   

10. You notice that the region title is not changed. Under Identification, enter Projects for Title. Click the Run and Save Page icon.

    

11. You notice that the title is now changed to Projects. You can search projects by entering a key word in the search text box. Enter email for Search, and click Go.

    

12. Observe that the associated row text is highlighted in red color. Click Reset.

    

13. All the records are displayed again. Click the edit icon in front of the row for Public Website Modified project.

    

14. You notice a small encircled question mark beside Project, Project Deadline, and Project Priority item labels. Click encircled question mark beside Project. You see a help window. You can add help text by editing it. Close the Help Window.

    

15. Click the Edit Page<n> link on the developer toolbar.

    

16. In the Rendering tab, under Items, select P3_PROJECT.

    

17. In its property editor, under Help, enter some text in Help Text. Click the Save and Run Page icon.

    

18. Click the encircled question mark beside Project. A Help window opens with the help text you entered in the previous step.

    

19. You can reorder the items on your page. Click Edit Page<n> on the developer toolbar.
    
    

    

20. To reorder the sequencing of items in this page, you can drag and drop an item to a new location under the Rendering tab or in the Grid Layout. Drag the P3_PROJECT_ PRIORITY item and drop it above the P3_PROJECT_ DEADLINE item in the Rendering tab.

    

21. Click the Save and Run Page icon.

    

22. Notice that Project Priority appears before Project Deadline in the Master Detail page. Click the Application<n> link in the developer toolbar.

    

Another way to improve the presentation of the application is to create a List of Values. In this topic, you create the following LOVs:

EMPLOYEES	A Dynamic LOV to show the list of Employees. This LOV is then associated with the Assigned To column in the List of Tasks.
STATUSES	A Static LOV to show the list of Statuses. This LOV is then associated with the Status column in the List of Tasks.
PRIORITIES	A Static LOV to show the list of Priorities. This LOV is then associated with the Priority column in the Projects Master area.

1. Click the 3 - Master Detail page.

   

2. Click the Page Shared Components tab in the tree view of the page.

   

3. Right-click Lists of Values and select Create...

   

4. For Create List of Values, retain the default value, From Scratch, and click Next >.

   

5. Enter EMPLOYEES for Name, select Dynamic for Type, and click Next >.

   

6. For a dynamic LOV, you need to enter the SQL query to be executed when the page is rendered. To build the SQL query, click the Create Dynamic List of Values link.

   

7. The Create Dynamic List of Values dialog window appears. Click the List of Values icon for Table or View.

   

8. Select OBE_EMPLOYEES.

   

9. Click Next >.

   

10. The Display column in a dynamic list of values is the column displayed to the user. The Return column is the corresponding value to be stored in the database. They can both be the same value when appropriate.
    Select FIRST_NAME (Varchar2) for Display Column, EMPLOYEE_ID (Number) for Return Value, and click Next >.

    

11. The SQL query is built. Click Finish.

    

12. You want to display the employee name as first name followed by last name. Modify the SQL query as given below, and click Create List of Values.
    select FIRST_NAME || ',' || LAST_NAME as display_value,
EMPLOYEE_ID as return_value
from OBE_EMPLOYEES
order by 1

    

13. Your LOV is created, but it does not appear under Lists of Values in the Page Shared Components tab. This is because, you have not associated the newly created LOV with this page. Before associating it with this page, you want to create two more static LOVs. Right-click Lists of Values, and Click Create...

    

14. Click Next >.

    

15. Enter STATUSES for Name, select Static for Type, and click Next >.

    

16. Enter the following entries for static values, and click Create List of Values.

    Display Value	Return Value
    Closed	closed
    Open	open
    On Hold	on-hold

    

17. To create another static LOV, right-click Lists of Values, and Click Create...

    

18. Click Next >.

    

19. Enter PRIORITIES for Name, select Static for Type, and click Next >.

    

20. Enter the following entries for static values, and click Create List of Values.

    Display Value	Return Value
    High	1
    Medium	2
    Low	3

    

21. Now you can associate the LOVs with the page. Expand Columns under Obe Tasks in the Rendering tab.

    

22. Select ASSIGNED_TO under columns in the Rendering tab.

    

23. Under Identification, select Select List for Type. Under List of Values, select Shared Component for Type and EMPLOYEES for List of Values.

    

24. Select STATUS under columns in the Rendering tab.

    

25. Under Identification, select Radio Group for Type. Under List of Values, select Shared Component for Type and STATUSES for List of Values.

    

26. Notice the two LOVs are listed under Lists of Values in the Page Shared Components tab. Click the Save and Run Page icon.

    

27. Select the drop down list for Assigned To. You see that the dynamic LOV executes and shows the list of Employees. Also, a radio group is created for Status with the static LOV values.

    

28. Next, you need to assign the the PRIORITIES LOV to the Priority item in the Master area of the page. Click the Edit Page<n> link in the developer toolbar.
    

    

29. In the Rendering tab, select P3_PROJECT_PRIORITY under Columns in the Edit OBE_PROJECTS region.

    

30. Under Identification, select Radio Group for Type. Select the following values for properties under List of Values, and click the Save and Run Page icon.

    Property	Value
    Type	Shared Component
    List of Values	PRIORITIES
    Display Extra Values	No
    Null Display Value	None

    

31. The Priority field is now a radio group populated with the static LOV values. Click the Edit Page<n> link in the developer toolbar.

    

You do not want a user entering a date prior to today's date. This type of check can be done through an item validation. Perform the following steps:

1. Click the Processing tab.

   

2. Right-click Validations, and select Create Validation.

   

3. Select the new validation in the Rendering tab, and update the properties in its property editor as follows:

   Property Group	Property	Value
   Identification	Name	P3_PROJECT_DEADLINE greater than today
   Validation	Type	SQL Expression
   Validation	SQL Expression	TO_DATE(:P3_PROJECT_DEADLINE,'DD-MM-YYYY') >= SYSDATE
   Validation	Always Execute	No

   

4. Under Error, enter Date needs to be greater than today! for Error Message, select the Inline with Field and in Notification for Display Location, and P3_PROJECT_DEADLINE for Associated Item. Click the Save and Run Page icon.

   

5. Change the Project Deadline to a date before today, and click Save.

   

6. Notice that you receive an item (or field) error message as well as a page notification message. This is because you specified that you wanted both when you created the validation.

   

7. Click the Application<n> link in the developer toolbar.

   

In this tutorial, you have learned how to:

• Create a database application
• Edit application objects
• Create List of Values (LOVs)
• Create and test a validation

• Oracle Cloud Home Page

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

• Curriculum Developer: Richard Green
  

• Other Contributors: Anjani Pothula, Drue Swadener