Creating and Running a Database Application Using Oracle Application Express 4.1

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

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

Note: Some screenshots in this tutorial may be slightly different depending on what browser you use.

Prerequisites

Before you perform this tutorial, you should:

Perform the Manipulating Database Objects Using Application Express 4.1 tutorial.

Creating an Instant Application

To create an instant application , 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.
.	Select Database for Application Type and click Next .
.	Select Instant Application.
.	For the Name field, enter Project Tasks Application and click Create.
.	The application is created successfully.

Creating a Master Detail Form

To create a master detail form, perform the following steps:

.	Click Create Page.
.	Select Form for page type and click Next.
.	You will build a Master Detail page which builds a two page report and form combination. Select Master Detail Form for Page Type and click Next.
.	For the Master table, select OBE_PROJECTS from the Table/View Name dropdown and click Next.
.	For the Detail table, select OBE_TASKS from the Table/View Name dropdown and click Next.
.	Select the radio button Select Primary key Column(s) for both the Master Table and Detail Table and click Next.
.	For the Master Table primary key source, you will use the existing trigger. Accept the default and click Next.
.	For the Detail Table primary key source, you will use the existing trigger. Accept the default and click Next.
.	You want to include Master Row navigation and also include a master report. Accept the default and click Next.
.	You want the Master Detail page to include both the master and detail information on the same page. Accept the default and click Next.
.	Accept the defaults and click Next.
.	Select Use an Existing tab set and create new tab for Tab Options, enter Projects for New Tab Label and click Next.
.	Click Create.
.	Your application was created successfully. Click Run Page.
.	Because you created an Instant Application, Application Express authentication is used. Enter your login information and click Login.
.	A report containing the list of projects is automatically displayed. 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 Apply Changes.

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:

.	Select Edit Page 2.
.	You see the Tree View on Page 2. In the Page Rendering section, under Regions node, locate and expand Report Columns under the Obe Projects node. This displays the report columns. Right-click PROJECT_DEADLINE and select Edit.
.	Under Column Attributes, select Standard Report Column for Display As field and 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 Projects List 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.
.	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 P3_PROJECT .
.	Select Text Field from the dropdown list for Display As field and click 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 <n>.
.	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.

Perform the following steps:

Click the 3 - Master Detail page.

In the Shared Components area on the page definition, right-click List of Values and select Create.

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

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

For a dynamic LOV, you need to enter the SQL Query 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. select Query on Selected Table from the Show pulldown window.

The query is displayed. You will use a slightly modified query for the List of Values. Close the window to return to the List of Values 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 one more static LOVs. Right-click and select Create again.

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

Click the Column Attributes section button.

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.

Click the Column Attributes section button.

Select Radio Group (named LOV) for the Display As and click List of Values section button.

Select STATUSES for the Named LOV and click Apply Changes. Then 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. In the next section, you add an item validation. Click the Edit Page 3 link in the developer toolbar.

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, review the list of validations already available. Notice that there are some validations already available. You want to create an additional one for the PROJECT_DEADLINE item.
.	Right-click Validations and select Create.
.	You want to create a validation on the P3_PROJECT_DEADLINE page item. Accept the default and click Next .
.	Select the item Obe Projects: 50. P3_PROJECT_DEADLINE (Project Deadline) and click Next .
.	Enter P3_PROJECT_DEADLINE greater than today for Validation Name and click Next.
.	Select SQL for the validation type and click Next.
.	Because you want to specify the condition when a PROJECT_DEADLINE is incorrect, click SQL Expression and click Next .
.	Enter TO_DATE(:P3_PROJECT_DEADLINE,'DD-MM-YYYY') >= SYSDATE for the Validation Code and Date needs to be greater than today for the Error Message and click Create Validation. .
.	The validation has been created successfully. Click Run.
.	Change the date to something prior to today's date.
.	Click Apply Changes.
.	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. Select the Application link in the developer toolbar.