Oracle Application Express -- Building a Functional Application

Purpose

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

Time to Complete

Approximately 2 hours

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Creating a Table from a Spreadsheet
	Adding a Master Table
	Modifying a Table
	Adding a Table Using a Script
	Adding a Constraint
	Creating an Application
	Adding a Master Detail Form
	Editing Application Objects
	Creating Lists of Values (LOVs)
	Adding an Item Validation
	Updating the Home Page Navigation List
	Applying a New Theme
	Adding a Logo
	Creating Users
	Restricting Access
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What Is Oracle Application Express?

Oracle Application Express, formerly called HTML DB, 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.

What Components Make Up Oracle Application Express?

Oracle Application Express contains four main components:

Application Builder	Used to build database-centric interactive Web applications.
SQL Workshop	Used to access database objects, run SQL statements and SQL scripts.
Utilities	Used to Load and Unload data, Generate DDL, run Object Reports and manage the Recycle Bin.
Administration	Used to manage services and users and to monitor activity.

Terminology

The following concepts are important to know when working with Oracle Application Express:

Workspace	A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation but keeping their objects, data and applications private.
Application	An application is a collection of pages with branches that connect them. Its attributes include the authentication method, default UI templates, and authorization rules.
Page	A page is the basic building block of an application. When you build an application in Application Builder, you create pages that contain user interface elements, such as tabs, lists, buttons, items, and regions.
Region	Content is displayed in regions, which are logical subsections of a page. Each page can have any number of regions of several different types. These types include: HTML text, SQL Queries, PL/SQL-generated HTML, and charts. Each region is rendered using a region template. Regions are positioned on the page using display points defined in the page template.
Item	An item can be a text field, text area, password, select list, check box, and so on. Item attributes affect the display and behavior of items on a page. For example, these attributes can impact where a label displays, how large an item is, and whether or not the item is displayed next to, or below the previous item. The value of an item is automatically stored into the application's session state, which can be referenced at any point within the user's session.

Architecture

Oracle Application Express lives completely within your Oracle database. It is comprised of nothing more than data in tables and large amounts of PL/SQL code. The essence of Oracle Application Express is approximately 165 tables and 200 PL/SQL objects containing 300,000+ lines of code.

Whether you are running the Oracle Application Express development environment or an application you built using Oracle Application Express, the process is the same. Your browser sends a URL request that is translated into the appropriate Oracle Application Express PL/SQL call. After the database processes the PL/SQL, the results are relayed back to your browser as HTML. This cycle happens each time you either request a page or submit a page. The method behind the url translation depends on your Oracle version. Running Oracle Application Express in Oracle prior to 10.2.0.3 requires Apache and mod PL/SQL. In Oracle 10.2.0.3 and beyond, including Oracle Express Edition (XE), Oracle Application Express can use the embedded PL/SQL gateway or Apache.

The application session state is managed in database tables within Oracle Application Express and does not use a dedicated database connection. Minimal CPU resources are consumed in the Application Express environment. If a user requests a page and is mentally processing the resultant page before making another request, the database could be stopped and restarted during the user’s think time without any perceived outage by the user.

1.	Perform the Installing and Configuring Oracle HTML DB 2.2 on Windows tutorial.
2.	Download and unzip the apex.zip file into your working directory.

1.	Enter the following URL to log in to Oracle Application Express (change the <hostname> to localhost or your specific hostname). http://<hostname>:7777/pls/apex
2.	To log in to Oracle Application Express, enter the following details, and click Login. Workspace: obe Username: obe Password: oracle
3.	To create the table, click the down arrow next to Utilities > Data Load/Unload > Load.
4.	Click Load Spreadsheet Data.
5.	Ensure that the import target (Import To) is set to New table. For Import From, select the Upload file (comma separated or tab delimited) option. Click Next >.
6.	Click Browse.
7.	Locate the tasks.txt file in the c:\wkdir directory and click Open. Because the data in the text file is tab delimited, enter \t in the Separator field. Click Next >.
8.	The Table Information page displays the columns in the table and their formats, as well as the data to be inserted into the table after the table is created. For Table Name, enter OBE_TASKS, and click Next >.
9.	Using the Primary Key page, you can add a system-generated primary key to your table and populate that column with a new sequence. Review the default values, and click Load Data.
10.	After the table is created and the data is loaded, you are left on the Files page. You can see the file that you just uploaded with 16 rows successfully uploaded. To view your new table, click the SQL Workshop tab.
11.	Select the down arrow next to Object Browser > Browse > Tables.
12.	To view the table definition, click the OBE_TASKS table under the list of tables in the left navigator.
13.	This page displays the table definition. To view the data in the table, click the Data tab.
14.	You see all the data in the table. You can change any of the data in the table from this page, and also add rows to the table.

1.	Click the Table tab.
2.	Click Create Lookup Table button.
3.	To specify the column on which the master table is to be created, select PROJECT - varchar2, and click Next >.
4.	Enter the following values, and click Next >. New Table Name: OBE_PROJECTS New Sequence: OBE_PROJECT_SEQ
5.	Click Finish.
6.	Notice that the new OBE_PROJECTS table contains a numeric primary key along with the PROJECT column.

1.	Make sure the OBE_PROJECTS table is selected. You are going to add a column to the table. Click Add Column.
2.	Enter the following values, and click Next >. Add Column: PROJECT_DEADLINE Type: DATE
3.	Click Finish.
4.	You see the modified table definition with the new PROJECT_DEADLINE column added. You want to create one more column for PROJECT_PRIORITY. Click Add Column.
5.	Enter the following values, and click Next >. Add Column: PROJECT_PRIORITY Type: NUMBER Precision: 1
6.	Click Finish.
7.	The PROJECT_PRIORITY column is added. Click the Data tab to view the data.
8.	You see all the projects that were referenced in the OBE_TASKS table. You can change data from here. Click the Edit icon ( ) on the left of PROJECT_ID for Public Website.
9.	For Project Deadline, enter one month from today's date. Enter 1 for Priority. Then click Apply Changes.
10.	You see the data that you added. Another way to create database objects and load data is to use a script. Click the SQL Workshop breadcrumb.

1.	Select the down arrow next to SQL Scripts then select Upload.
2.	Click Browse...
3.	Select employees.sql and click Open.
4.	Enter OBE_EMPLOYEES for the Script Name and click Upload.
5.	Select the OBE_EMPLOYEES script.
6.	The script is shown. Click Run.
7.	Click Run to confirm.
8.	The script was executed. To view the results, click the View Results icon.
9.	The OBE_EMPLOYEES table was created and some data was inserted. Click the SQL Workshop breadcrumb.

1.	Select the down arrow next to Object Browser > Browse and then select Tables.
2.	Select the OBE_TASKS table and click the Constraints tab.
3.	Click Create.
4.	Select Foreign Key for the Constraint Type. Select ASSIGNED_TO for the Foreign Key Column, select OBE_EMPLOYEES for the Reference Table Name and select EMPLOYEE_ID from the Reference Table Column List. Then click Next > .
5.	Click Finish.
6.	The constraint was created successfully. You want to also create a check constraint on the PROJECT_PRIORITY column so that the data is validated when the data is inserted or updated. Select OBE_PROJECTS.
7.	Click the Create section tab.
8.	Select PROJECT_PRIORITY for the Constraint of Column field and enter in ('1','2','3') for the Constraint Expression. Then click Next >.
9.	Click Finish.
10.	The constraint was created successfully. Click the Home breadcrumb in the upper left corner of your window.

1.	From the Oracle Application Express home page, click the down arrow next to Application Builder > Create Application > Create Application.
2.	For Name, enter Project Tasks Application. Keep the default value of From Scratch for the Creation Application, and click Next >.
3.	The first page you want to create is a Home Page. Make sure Blank Page is selected for the Page Type, change the Name to Home and click Add Page.
4.	To be able to maintain the the employees that you have loaded, you need to create a report and form on the employees table. Select Report and Form for the Page Type, Select Home (1) for the Subordinate to Page and click the up arrow for Table Name.
5.	Select OBE_EMPLOYEES from the list of Tables.
6.	Click Add Page.
7.	You can change the name of a page when you create the application. Click the OBE_EMPLOYEES link for page 3.
8.	Change the Page Name to Maintain Employee and click Apply Changes.
9.	Click the OBE_EMPLOYEES link for page 2 to change it's name.
10.	Change the Page Name to Employee Information and click Apply Changes.
11.	Click Next >.
12.	Select No tabs and click Next >.
13.	You do not want to reference any components from other applications. Click Next >.
14.	Accept the defaults. Click Next >.
15.	Select the Theme 15. Click Next >.
16.	Review your choices. Click Create.
17.	The Application and its associated pages were created. To run the application, click the Run Application icon.
18.	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 User Name and oracle for the Password. Then click Login.
19.	The Home Page is displayed. The Employee Information link to the report is displayed. Click Employee Information.
20.	The List of Employees is displayed. You can edit the Employee information by clicking the icon to the left of the Employee you want to edit.
21.	The Employee Details are displayed. Click the Edit Application link in the Developer links at the bottom of the window.

1.	Click Create Page.
2.	Select the Form page type and click Next >.
3.	Select Master Detail Form and click Next >.
4.	You first select the Master table. Select OBE_PROJECTS for the Table/View Name and click the Select All () icon to move all the columns to the Displayed Columns area. Then click Next >.
5.	Next, you select the Detail table. Select OBE_TASKS for the Table / View Name and click the Select All ( ) icon to move all the columns to the Displayed Columns area. Then click Next >.
6.	When the table was created, a trigger was also created to invoke the sequence when a row is inserted. Accept the default to use an Existing Trigger and click Next >.
7.	Click Next > to use the Existing Trigger on the OBE_TASKS.
8.	For Include master row navigation, select Yes. This selection allows a user to scroll through projects on the Master-Detail page. For Master Row Navigation Order, select PROJECT. This selection makes the next project displayed, the next one alphabetically by project name. If you chose PROJECT_ID, the next record would be calculated by PROJECT_ID and that would be confusing to the user. For Include master report, select Yes. This section creates a page, in addition to the master/detail, that is a report of all projects. The report also contains navigation to the Master-Detail page. When done making your selections, click Next >.
9.	Make sure Edit detail as tabular form on same page and click Next >. By selecting to edit detail as a tabular form on the same page, the tasks report on the Master-Detail page is editable. If you selected edit detail on a separate page, the tasks report would just be a report and editing would be per record on a separate page.
10.	Under the Create Breadcrumb Entry section, change the Entry Name (Master Report) to Projects and Entry Name (Master Detail Page) to Projects and Tasks. Then click the Home link to set the parent breadcrumb of the new Projects report to Home. This selection makes it navigatable from the Home page.
11.	Click Next >.
12.	Accept the defaults of Do not use tabs and click Next > .
13.	Click Create to create the Master Detail Form.
14.	Click the Run Page icon.
15.	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.
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 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 button. This button is used in conjunction with the check boxes to the left of each Task. The check box in the header row is used to select all of the tasks displayed on the page. When you click the Delete 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. In addition, you see a Previous and Cancel button for each area on the form. The upper set of buttons correspond to the Project Information and the lower set of buttons correspond to the Tasks. The Previous button appears because you selected to include Master row navigation. The Cancel button returns the user to the Projects report. Change the name of the project to Public Website Modifications and click Apply Changes.
17.	Your changes were made. Click Edit Application.

1.	Select 4 - Obe Projects.
2.	Select the Report link under Regions.
3.	Click the Edit () icon next to PROJECT_DEADLINE.
4.	Under Column Formatting, enter MM/DD/YYYY for the Number/Date Format field and click Apply Changes.
5.	Click Apply Changes.
6.	Click the Edit () icon for the Page area on Page 4.
7.	In the Name section, enter Projects for the Name. In the Display Attributes section, enter Projects for the Title. Then click Apply Changes.
8.	Click Run.
9.	Notice that the Title in your Browser is set to Projects and the 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.
10.	Click the Edit () icon next to the Obe Projects Region title.
11.	Change the title to Projects and click Apply Changes.
12.	Click Cancel to close the Page Region window.
13.	Refresh your browser to show the change you just made. Click Hide Edit Links in the Developer Links.
14.	Your changes have been made to Page 4. Click Edit Application.

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 Create () icon in the Validations section for Page 5.
2.	Accept the default to create an Item level validation and click Next >.
3.	Select the item P5_PROJECT_DEADLINE and click Next >.
4.	Select SQL for the Validation Method and click Next >.
5.	Because you want to specify the condition when a PROJECT_DEADLINE is incorrect, click SQL Expression and click Next >.
6.	Accept the defaults and click Next >.
7.	Enter TO_DATE(:P5_PROJECT_DEADLINE,'MM/DD/YYYY') >= SYSDATE for the Validation and Date needs to be greater than today for the Error Message and click Next >.
8.	Select Request Is Contained within Expression1 for the Condition Type and enter SAVE,CREATE for Expression1. Then click Create.
9.	The validation has been created successfully. Click Run.
10.	Change the date to something prior to today's date and click Apply Changes.
11.	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 Edit Application.

1.	Click the Home page.
3.	Select the Regions () icon.
4.	In the regions area, click the List link.
5.	Click Create List Entry >.
6.	Enter 20 for the Sequence, Manage Projects and Tasks for the List Entry Label and click the up arrow for the Page.
7.	Select 4 Projects.
8.	Click reset pagination for this page and click Create. By selecting to reset pagination, the link always displays the first set of records when the user clicks the link regardless of which set they were viewing the last time they accessed the page.
9.	Your entry was added to the list. Click the Run Page 1 ( ) icon.
10.	Notice the new entry. Click Manage Projects and Tasks.
11.	The page is listed. Click Edit Application.

1.	Click the down arrow next to Shared Components > User Interface > Themes.
2.	To create a new theme, click Create.
3.	Accept the default From the Repository and click Next >.
4.	From the Theme options, select the Theme 14 option. Click Next >.
5.	Click Create.
6.	To switch to the Modern theme, click Switch Theme.
7.	For Switch to Theme, make sure 14. Modern is selected. Click Next >.
8.	Notice that the page displays the compatibility between the two themes. This utility checks whether there is a template in the new theme to replace each of the templates in the current theme. The check marks in the Status column indicate that the templates are compatible. Click Next >.
9.	To confirm the theme switch, click Switch Theme.
10.	To see the new theme, click the Run Page icon ( ) at the top right of the page.
11.	Notice that the entire appearance of your application has changed. The color scheme has changed. Click Edit Application from the Developer Links at the bottom.

1.	Select the down arrow next to Edit Attributes and select Definition.
2.	Click the Logo section button.
3.	Select Text for the Logo Type. Enter Project Tracker for Logo and style="font-family:Arial; color:#000000; font-size:18; white-space:nowrap; font-weight:bold;" for Logo Attributes. Then click Apply Changes.
4.	To see the logo, click the Run Page icon ( ) at the top right of the page.
5.	The text is displayed. Click Edit Application in the Developer links.

1.	Click the Home link in the breadcrumb menu.
2.	In the Administration box on the right, select Manage Application Express Users.
3.	Click Create End User.
4.	Enter the following information, and then click Create and Create Another. User Name: Brad.Knight Password and Confirm Password: welcome1 Email Address: brad.knight@oracle.com Default Schema: OBE User is a developer: No User is a Workspace Administrator: No
5.	Enter the following information, and then click Create and Create Another. User Name: Susie.Parker Password and Confirm Password: welcome1 Email Address: susie.parker@oracle.com Default Schema: OBE User is a developer: No User is a Workspace Administrator: No
6.	Enter the following information, and then click Create User. User Name: John.Bell Password and Confirm Password: welcome1 Email Address: john.bell@oracle.com Default Schema: OBE User is a developer: No User is a Workspace Administrator: No
7.	Notice that the three users have been created. You now set up administrator access to the application. Click the Application Builder tab.

A	Add an Access Control Page
B.	Identify Privileged Users
C.	Apply Authorization Schemes to Application Components.

1.	Click the Project Tasks Application.
2.	Click Create Page.
3.	Select the Access Control page type and click Next >.
4.	Accept the default page value, 8, and click Next >.
5.	Make sure Do not use tabs is selected and click Next >.
6.	Click Finish.
7.	Click Run Page.
8.	You see the access control page you just added to the application. The page is divided into two regions, and the default setting for Application Model is Full Access. In this case, you want to restrict certain users from certain parts of the application. Select Restricted Access and click Set Application Mode.
9.	The Application mode has been set. In the next topic, you identify your privileged users. Click Add User.

1.	Select the down arrow next to Edit Attributes and select Security.
2.	Under Authorization, change the scheme to access control - view and click Apply Changes.
3.	Now that you have given access to the application for view privileged users, you can restrict edit privileged users to Employee Information. Click 2 - Employee Information.
4.	In the Regions area, click the Report link. Note, you may need to click the Regions () icon under Page Rendering.
5.	Click the Edit () icon in front of EMPLOYEE_ID.
6.	Click the Authorization section button.
7.	Select access control - edit for the Authorization Scheme and click Apply Changes.
8.	Click Apply Changes.
9.	Because you only want the Create Button to appear if the user has Edit or Administrator privilege, you need to set the authorization scheme. Click the Buttons () icon under Page Rendering.
10.	Click the Create link.
11.	Click the Authorization section button.
12.	Select the access control - edit authorization scheme and click Apply Changes.
13.	You also want to protect against direct access to the page. So even though you restricted a user that didn't have edit privilege to edit or create users on page 2, they can still access page 3 if the correct URL is entered. To prevent this from happening, you need to restrict page 3 to only edit users. Click > for Page to advance to Page 3.
14.	Click the Show All () icon
15.	In the Page section, click the No link for the Authorization Page Attribute.
16.	For Authorization Scheme, select access control - edit. Click Apply Changes.
17.	Notice that the Authorization link is now set to Yes. Click Application #.
18.	Since users with the administrator privilege are only allowed to make changes to the access control list, you need to set the authorization scheme for page 8. Click 8 - Access Control Administration Page.
19.	Click the No link for Authorization.
20.	Select access control - administrator for the Authorization Scheme and click Apply Changes. Now you are ready to run the application.
21.	Enter 1 for Page and click <.
22.	Click Run.
23.	If you are already logged in as OBE, click Logout. Enter brad.knight and welcome1 for the username and password. Then click Login.
24.	Select Employee Information.
25.	Notice that Brad can edit the Employees. Click Logout.
26.	Enter john.bell and welcome1 for the username and password. Then click Login.
27.	Select Employee Information.
28.	John has only view privilege and therefore can not edit the Employees. He also does not see the Create button displayed.
29.	Change the page number in your URL to try and access Page 3. Example url …/f?p=101:2:2101953412249296357::NO Change to …/f?p=101:3:2101953412249296357::NO Press the ENTER key on your keyboard. Notice that you receive a message denying you access to the page because you restricted Page 3 to edit privilege users only.

	Create and modify a table
	Create an application
	Add a report
	Add and edit a Tabular form
	Apply a new theme
	Create a user and limit access to a user