Creating and Running a Database Application Using Oracle Application Express 4.2

Overview

    Purpose

    This tutorial shows you how to rapidly create a database application using Oracle Application Express.

    Time to Complete

    Approximately 80 minutes

    Prerequisites

    Before starting this tutorial, you should:

Creating a Database Application

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

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

    Click Create >.


    For Name, enter Project Tasks Application. Select Start from Scratch for the Create Options, and click Next >.

    Enter Home for Page Name and click Add Page.

    You see the Master Detail page type. Master Detail builds a two page report and form combination. Select Master Detail for Page Type. Select Home(1) for Subordinate to Page, OBE_PROJECTS for Master Table Name, and OBE_TASKS for Detail Table Name. Click Add Page.

    The Master Detail pages were created, click Next >.

    Accept the default and click Next >.

    You can define a date format that will 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 >.

    Select All Themes from the Show menu and then select Theme 1 and click Next>.

    Click Create Application.

    Your application was created successfully. Click Run Application.

    Because you chose the default of Application Express Authentication, you need to login to the new application using the same login you use to develop. Enter obe for the Username and obe for the Password. Then click Login.

    Select Obe Projects.

    A report containing the list of projects is automatically created. Select the Edit ( )icon next to one of the projects to view the Master detail Form.

    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.

Editing Application Objects

    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 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:

    Select Edit Page 2.

    You see the Tree View of Page 2. In the Page Rendering section, under Regions node, locate and expand the Obe Projects > Report Columns node. This displays the report columns.

    Right-click PROJECT_DEADLINE and select Edit.

    Under Column Attributes, enter DD-MON-YYYY for the Number/Date Format field and click Apply Changes.

    Click Apply Changes.

    In the Page Rendering section, right-click Obe Projects page title, and select Edit.

    In the Name section, enter Projects for the Name. In the Display Attributes section, enter Projects for the Title. Then click Apply Changes.

    Click Run.

    Notice that the date format of the PROJECT_DEADLINE has changed. You notice that the Region title is still set to OBE_Projects. Click Show Edit Links in the Developer links area.

    Click the Edit () icon next to the Obe Projects Region title.

    Change the title to Projects and click Apply Changes.

    Click Cancel to close the Page Region window.

    Refresh your browser to show the change you just made. Click Hide Edit Links in the Developer Links.

    You can search projects by entering a key word in the Search text box. Enter email in the Search text box and click Go.

    Observe that the associated row text is highlighted in red color. Click Reset. Then, click the Edit Page 2 link in the developer tool bar.



    Select the edit icon in front of the row for the Public Website project.

    If you move your mouse over Project, Project Deadline, or Project Priority item label, you see a ? symbol. If you click any of these item labels, you see a help window. You can add edit text by editing the item. Close the Help window.

    Click Edit Page<n>.

    In the Page Rendering section, under Regions, double-click the P3_PROJECT item.

    Select the Help Text subtab.

    Change the Help Text to something else and click Apply Changes.

    Click Run.

    Click the Project label again and notice that your help text is displayed.

    You can reorder the items on your page. Click Edit Page 3 .

    The page definition for Page 3 is displayed. To reorder the sequencing of items in this page, you can drag and drop an item to a new location. Drag and drop the P3_PROJECT_ PRIORITY item above the P3_PROJECT_ DEADLINE item and release your mouse.

    The item was moved. Click Run.

    Note how the Project Priority field now appears above the Project Deadline field. Click the Application<n> link in the developer tool bar.

Creating Lists of Values (LOVs)

    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.

    Perform the following steps:

    Click the 3 - Master Detail page.

    In the Shared Components column, right-click List of Values and select Create.

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

    For Name, enter EMPLOYEES. For Type, change the type to Dynamic. Then click Next >.

    For a dynamic LOV, you need to enter the SQL you want to execute when the page is rendered. To find out what the names of your columns are, you can select the item finder () icon.

    Click the Tables tab.

    Select the OBE_EMPLOYEES table link.

    Note the names of the columns. Exit the Item Finder window.

    Enter the following SQL statement and click Create List of Values. The first column in a dynamic list of values is the column displayed to the user. The second column is the corresponding value to be stored in the database. They can both be the same value when appropriate. 

    select FIRST_NAME||', '||LAST_NAME d,
    EMPLOYEE_ID r
from OBE_EMPLOYEES
order by 1

    Your LOV was created. Note that the List of Values does not appear like an expandable node because you haven't yet associated it with this page. You want to create 2 more static LOVs. Right-click and select Create.

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

    Enter STATUSES for the Name and make sure Static is selected for the type and click Next >.

    For Display Value and Return Value, enter the following:

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

    Click Create List of Values.

    The STATUSES LOV was created. There is one more LOV to create for the Master portion of the form, Priorities. Click Create >.

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

    Enter PRIORITIES for the Name and make sure Static is selected for the type and click Next >.

    For Display Value and Return Value, enter the following:

    Display Value Return Value
    High 1
    Medium 2
    Low 3

    Click Create List of Values.

    The PRIORITIES LOV was created. Now you can associate the LOVs with the page. Click the Edit Page 3 icon.

    In the Page Rendering section, under OBE_Tasks node, expand Report Columns.

    Then, right-click ASSIGNED_TO column and select Edit.

    Change the Display As to Select List (named LOV) and click the List of Values section button.

    Select EMPLOYEES for the Named LOV and click Apply Changes.

    Click the Edit () icon in front of the STATUS column. 

    The List of Values section button should be already selected. Select STATUSES for the Named LOV and click the Column Attributes section button.

    Select Radio Group (named LOV) for the Display As and click Apply Changes. Click Apply Changes again.




    Note that List of Values now appears as an expandable node. Now you can run the page again to see the results. Click Run.


    Select the dropdown list for Assigned To. You see that the dynamic LOV executed and the list of Employees is shown.


    Note that the radiogroup is created for Status. You see that the static LOV values are shown.


    Next, you need to assign the the PRIORITIES LOV to the Priority item in the Master area of the page. Click Edit Page 3.

    Under Page Rendering, Items node, right-click P3_PROJECT_PRIORITY and select Edit.

    Select the Radio quick link under the Display As field or select Radio Group from the drop down list and click the List of Values tab.

    Select PRIORITIES for the Named LOV, select Yes for Display Null Value, select No for Display Extra Values, and enter None for the Null display value. Then click Apply Changes.

    Click Run.

    The Priority field is now a Radiogroup populated with the LOV values. Click Edit Page 3 in the Developer links.

Adding an Item Validation

    You don't 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:

    In the Page Processing section for Page 3, right-click Validations and select Create Validation.

    Accept the default to create an Item level validation and click Next >.

    Select the item P3_PROJECT_DEADLINE and click Next >.

    Enter P3_PROJECT_DEADLINE greater than today as Validation Name and click Next >.

    Select SQL for the Validation Method and click Next >.

    Because you want to specify the condition when a PROJECT_DEADLINE is incorrect, select SQL Expression and click Next >.

    Enter TO_DATE(:P3_PROJECT_DEADLINE,'DD-MM-YYYY') >= SYSDATE for the Validation and Date needs to be greater than today for the Error Message and click Next >.

    Select Request Is Contained within Expression1 for the Condition Type and enter SAVE, CREATE for Expression1. Then click Create Validation.

    The validation has been created successfully. Click Run.

    Change the date to something prior to today's date and click Save.

    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.

    Click the Application <n> link in the developer tool bar.

Summary

    In this tutorial, you have learned how to:

    • Create an application
    • Edit Application Objects
    • Create List of Values (LOVs)
    • Create and test a validation

    Resources

    Credits

    • Lead Curriculum Developer: Dimpi Sarmah
    • Other Contributors: David Peake, Chaitanya Koratamaddi

To navigate this Oracle by Example tutorial, note the following:

Hide Header Buttons:
Click the title to hide the buttons in the header. To show the buttons again, click the title again.
Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.