Building a Functional Application Using Oracle Application Express 3.0

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

Approximately 2 hours

Topics

This tutorial covers the following topics:

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

What is new in Oracle Application Express 3.0?

This release includes several major new features plus dozens of other smaller, timesavings enhancements.

PDF Printing

You can export a report region to PDF. When you click to edit a report region, you notice a new tab, Print Attributes. You can also define Report Queries and Report Layouts under Shared Components. For Printing, please note that you can have the end user access this using a built-in link or via your own custom button.

Access Migration

Recently, a new Application Migration Workshop was introduced for Oracle Application Express. That component, more details here, is a standard component of Application Express 3.0. To access the new component, select Application Migrations from the Home page.

Flash Charts When you create a new page or region of type chart, there is a new type - Flash Chart. There are 18 types of charts that are supported. During the creation process, you can preview the selected type and set most attributes for your resulting chart. In addition to creating new Flash Charts, you can also convert your existing SVG Charts to Flash Charts. The migration utility is available under Application Reports > Page Components and also under Tasks if you edit a region that contains an SVG Chart.
Drag and Drop Item Layout On the Page Definition page,there is a new icon called Drag and Drop to the right of the Reorder Items icons. You can reorder items within the current region, change select attributes (item name, label, and type within the current item-type range), delete items and quickly create new items. You can place items to the left or right of an existing item and can insert a new row to quickly add items in the middle of an existing form. To remove an item, simply drag it into the recycle bucket at the bottom.
Improved Web Services The Application Express Web services engine supports more loosely defined types and document style Web services. Both enhancements make it possible to support JDeveloper created Web services and Oracle BPEL synchronous Web services. You can interact with Web services over SSL (HTTPS) and Web services that require basic authentication. In addition, you can manually creating a Web service reference if the Application Express engine cannot correctly parse the WSDL document for a particular service. You supply the SOAP request envelope and the URL endpoint among other information about the Web service.
New Item Types Several new items types have been added. These include:
   - Shuttle
   - HTML Editor Minimal - available under Textarea
   - HTML Editor Standard - available under Textarea
   - Popup Color Picker - available under Popup List of Values
   - Date Picker (use format mask)
Calendar Enhancement When you create a calendar page or region, you automatically get a Monthly, Weekly, and Daily Calendar. The user can toggle between the mode that they want using buttons.
Supporting Objects Enhancement The Supporting Objects feature that was introduced in Application Express 2.2 has been enhanced to allow for the definition of upgrade scripts. As a result, you can post upgrades to the Packaged Applications distributed on OTN and you can install an upgrade using the same wizard as for initial installation. You can also use this feature for distributing upgrades.
Page and Region Caching Page and Region caching allows you to write parts of your application to a cache to improve performance. The cache attributes are set on the Page and Region attributes pages. This is useful for static pages and regions such as lists that do not have any conditions and regions containing HTML text.
Item Finder Enhancements In the Application Builder, there are two new tabs on the Item Finder. One for CSS - this allows you to identify cascading style sheets that are available to any Application Express application, regardless of theme. The second is for Images - it displays an Icon gallery of generic art for use with applications. A Table Finder has also been added to both the Object Browser and SQL Commands components of the SQL Workshop. Next time you are creating a trigger in the Object Browser or writing some custom SQL in SQL Commands and just can't recall the column name you need, click the flashlight icon.

Application and Schema Comparison

A new Application Comparison utility under Application Reports > Cross Application can be used to identify differences between two selected applications. The Schema Comparison is found under Utilities. It is used to identify differences between objects in two different schemas.
Friendly URL Syntax to Facilitate Bookmarks This feature allows links to use a session ID of zero for public applications. This makes all public page links consistent which makes them much more search engine and bookmark friendly. The actual session ID is stored in a cookie (this is not a security issue because this feature is only for public pages).
New Password and Account Controls At the instance and Workspace level, you can specify rules for password expiration, mandate strong passwords (minimum number of characters, containing number and letters, etc), require change password on first use (very useful for system generated initial passwords), and lock accounts.
Improved Workspace Management In addition to the password controls above, there are several other workspace management improvements. You can define the sizes that you want for your workspaces (selection presented to the user requesting and the default that will be selected), request an email of your Workspace Names (this is useful when you just can't recall the name of but you always know your email address), and review a log of login attempts.

Back to Topic List

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g

2.

Perform the Configuring Oracle Application Express in Oracle Database 11g tutorial

3.

Download and unzip the apex30.zip file into your working directory.

Back to Topic List

Creating a Table from a Spreadsheet

To load the data for this tutorial from a spreadsheet, perform the following steps:

1.

Enter the following URL to log in to Oracle Application Express (change the <hostname> to localhost, your specific hostname or apex.oracle.com).

http://<hostname>:7777/pls/apex

 

2.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

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

 

Back to Topic List

To make the project name maintainable and to be able to track other information about a project, you can move the Project information into a separate table. Perform the following steps:

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.

 

Back to Topic List

Now that you have the two main tables, enhance the OBE_PROJECTS table by adding some additional columns.
To do this, perform the following steps:

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. Select SQL Scripts from the drop down list box.

Back to Topic List

You may have noticed that the OBE_TASKS.ASSIGNED_TO column was a number. In this topic, you run a script to create the OBE_EMPLOYEES table and load a list of current employees. Each employee has an EMPLOYEE_ID that corresponds to the numbers found in the OBE_TASKS.ASSIGNED_TO column. Perform the following steps:

1.

Click Upload.

 

2.

Click Browse...

 

3.

Select employees.sql and click Open. Enter OBE_EMPLOYEES for the Script Name and click Upload.

 

4.

Select the OBE_EMPLOYEES script.

5.

The script is shown. Click Run.

6.

Click Run to confirm.

7.

The script was executed. To view the results, click the View Results icon.

8.

The OBE_EMPLOYEES table was created and some data was inserted. Click the SQL Workshop breadcrumb.

Back to Topic List

You now have the employee data that is referenced by the OBE_TASKS.ASSIGNED_TO column but need to identify a foreign key between the tables. The foreign key ensures that each ASSIGNED_TO column references a valid Employee. It also ensures that an Employee cannot be deleted who has tasks assigned. Perform the following steps:

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. Ensure that Check Constraint Type is selected. Then click Next >.

9.

Click Finish.

10.

The constraint was created successfully. Click the Home breadcrumb.

Back to Topic List

Creating an Application

To create the application framework and default a few initial pages, perform the following steps:

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 obe 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 Application <n> link in the Developer links at the bottom of the window.

 

Back to Topic List

Adding a Master Detail Form    

Even though Projects and Tasks are stored in two separate tables, it is useful to display their information on one page. A Master-Detail form enables you to do just that. To add a Master-Detail form, perform the following steps:

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 is selected 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 and click Apply Changes.

 

17.

Your changes were made. Click the Application <n>link in the developer toolbar.

 

Back to Topic List

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. Additionally, you print the Projects report in the PDF format. Lastly, you change the sequence of items in the Projects and Tasks page using the Drag and Drop feature. Perform the following steps:

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

Click the Edit Page 4 link in the developer tool bar.

 

15.

Click the Report link next to the Projects region.

 

16.

Click the Print Attributes tab.

 

17.

Select Yes in the Enable Report Printing drop-down menu and select Portrait in the Orientation drop-down menu under Page Attributes. Then click the Page Header tab.

 

18.

Enter Project Report for Page Header and and set the Alignment to center. Click Apply Changes.

 

19.

Notice that now Print appears to the right of the Report link since you have enabled Report Printing. Click Run.

 

20.

Notice the Print link at the bottom of the report. Click the Print link.

 

21.

Click the Open with radio button and click OK to view the report in PDF format.

 

22.

The Projects report is displayed in the PDF format.

 

23.

Click the Edit Page 4 link in the developer tool bar.

 

24.

Click the > arrow to navigate to the page definition for the Projects and Tasks page (Page 5).

 

25.

The page definition for Page 5 is displayed. To reorder the sequencing of items in this page, click the Items tab.

 

26.

Click the Drag and drop icon in the Items section.

 

27.

The Drag and Drop layout is displayed. You can use this intuitive graphical layout to edit, create, or delete items in this page.

 

28.

In order for the Project Priority item to appear above Project Deadline item in Page 5, drag and drop the P5_PROJECT_ PRIORITY item above the P5_PROJECT_ DEADLINE item and click Next>.

 

29.

Click Apply Changes.

 

30.

Click Run.

 

31.

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

 

Back to Topic List

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:

1.

Click the 5 - Master Detail page.

 

2.

In the Shared Components column, under List of Values, click the Create ( ) icon.

 

3.

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

 

4.

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

 

5.

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.

 

6.

Click the Tables tab.

 

7.

Select the OBE_EMPLOYEES table link.

 

8.

Note the names of the columns. You could select the SQL statement shown and copy this to the clipboard. Exit the Item Finder window.

 

9.

Change the SQL statement to the following and then 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

 

10.

Your LOV was created. Note that the LOV is not listed in the List of Values area because you haven't yet associated it with this page. You want to create 2 more static LOVs. Click the Create ( ) icon again.

 

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

 

12.

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

 

13.

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.

 

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

 

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

 

16.

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

 

17.

For Display Value and Return Value, enter the following:

Display Value Return Value
High 1
Medium 2
Low 3

Click Create List of Values.

 

18. The PRIORITIES LOV was created. Now you can associate the LOVs with the page. Click the Edit Page 5 () icon.

 

19. Click the Show All icon to reveal all Page Components.

 

20.. In the Regions area, click the Report link.

 

21.

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

 

22.

Click the Tabular Form Element section button.

 

23.

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

 

24.

Select EMPLOYEES for the Named LOV, select YES for Display Null and enter - None Assigned - for the Null display value. Then click Apply Changes.

 

25.

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

 

26.

The List of Values section button should be already selected. Select STATUSES for the Named LOV, Yes for Display Null and enter - No Status - for the Null display value. Then click the Tabular Form Element section button.

 

27.

Select Select List (named LOV) for the Display As and click Apply Changes.

 

28.

Now you can run the page again to see the results. Click Run Page 5 () icon.

 

29.

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

 

30.

Select the dropdown list for Status. You see that the static LOV values are shown.

 

31.

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

 

32.

Under Page Rendering, click the item () icon.

 

33.

Select P5_PROJECT_PRIORITY.

 

34.

Select the Radio link under the Display As field and click the LOV section button.

 

35.

Select PRIORITIES for the Named LOV, enter 4 for Number of Columns, select Yes for Display Nulls and enter None for the Null display value. Then click Apply Changes.

 

36.

Click Run.

 

37.

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

 

Back to Topic List

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:

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.

Enter P5_PROJECT_DEADLINE as Validation Name 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.

 

12.

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

 

Back to Topic List

Adding a Flash Chart 

You want to create a flash chart. You need to create a 2D pie chart that shows the number of tasks per project. Perform the following steps:

1.

Click Create Page > in the Project Tasks Application home page.

 

2.

Select the Chart Page Type and click Next >.

 

3.

Select the Flash Chart radio button and click Next >.

 

4.

Enter the page name as Tasks per Project Chart, accept the other default values, and click Next >.

 

5.

Select the Do not use tabs option and click Next >.

 

6.

Select 2D Pie as the Chart Type, enter Tasks per Project as the Chart Title and click the Update button to refresh the chart preview.

 

7.

The preview of the Tasks per Project flash chart is displayed. Click Next >.

 

8.

Copy and paste the following query into the query field. Click Next >.

select null link,
       p.project label,
       t.task_count value
  from (select project_id,
               count(*) task_count
          from obe_tasks
         group by project_id) t,
       obe_projects p
 where t.project_id = p.project_id

 

9.

Click Finish to create the chart.

 

10.

The flash chart has been created. Click the Run Page icon to view the flash chart.

 

11.

The flash chart shows the number of tasks per project. The first number for each slice of pie is the project and the second number is the number of tasks. Click the Application <n> link in the developer tool bar.

 

Back to Topic List

Adding a Calendar  

You want to add a calendar that shows the deadlines of each project and then add a button on the Projects report to link to the Calendar. Perform the following steps:

1.

Click Create Page > in the Project Tasks Application home page.

 

2.

Select the Calendar Page Type and click Next >.

 

3.

Select the Easy Calendar radio button and click Next >.

 

4.

Enter Project Tasks Calendar for the Page Name and click Next >.

 

5.

Select the Do not use tabs option and click Next >.

 

6.

Select OBE_PROJECTS for the Table / View Name and click Next > .

 

7.

Ensure that PROJECT_DEADLINE is selected for the Date Column and PROJECT is select for the Display Column. Then click Next >.

 

8.

Click Finish to create the calendar.

 

9.

The calendar has been created. Click the Run Page icon to view the calendar.

 

10.

The calendar shows the projects on the date due. You can also view the calendar in daily or weekly mode. Click Weekly.

 

11.

The Weekly calendar mode is displayed. You can advance to next week. Click Next.

 

12.

You want to create a button to this calendar from the Projects report. Scroll down to the bottom of the weekly calendar and click the Application <n> link in the developer tool bar.

 

13.

Click the Projects page.

 

14.

In the buttons area, Click the Create () icon.

 

15.

Select the second Projects (10) region and click Next >.

 

16.

Select Create a button in a region position and click Next >.

 

17.

Enter View Calendar for the Button Name and click Next >.

 

18.

Accept the defaults and click Next >.

 

19.

Accept the defaults and click Next >.

 

20.

Click the up area to select a page to branch to.

 

21.

Select Project Tasks Calendar.

 

22.

Click Create Button.

 

23.

Click Run to view the Report with the button.

 

24.

Click View Calendar.

 

25.

The calendar is displayed. Click the Application <n> link in the developer tool bar.

 

Back to Topic List

Updating the Home Page Navigation List   

Now that you have completed your Master Detail Form and Flash Chart, you need to add them to the Home Page Navigation List . Perform the following steps:

1.

Click the Home page.

 

2.

Select the Regions () icon.

 

3.

In the regions area, click the List link.

 

4.

Click Create List Entry >.

 

5.

Enter 20 for the Sequence, Manage Projects and Tasks in the List Entry Label field. Ensure that Page in this Application is selected in the Target type and then click the up arrow for the Page.

 

6.

Select 4 Projects.

 

7.

Click reset pagination for this page and click Create and Create Another. 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.

 

8.

Enter 30 for the Sequence, View Tasks per Project Chart in the List Entry Label field. Ensure that Page in this Application is selected in the Target type and then click the up arrow for the Page.

 

9.

Select 8 Tasks per Project Chart.

 

10.

Click reset pagination for this page and click Create.

 

11.

Your entry was added to the list. Click the Run Page 1 ( ) icon.

 

12.

Notice the new two entries. Click Manage Projects and Tasks.

 

13.

The Projects report is displayed. Click the Home breadcrumb.

 

13.

Click View Tasks per Project Chart.

 

13.

The page is listed. Click the Application <n> link in the developer tool bar.

 

Back to Topic List

Applying a New Theme  

When you created the application, you selected the theme 15. You can change the look and feel of the application by changing the theme. You first create a new theme by selecting one from the Oracle Application Express Repository. To apply a new theme, perform the following steps:

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 the Application <n> link from the developer tool bar.

Back to Topic List

Adding a Logo  

You can very easily add a text or image logo to the application. In this case, you add a text logo. Perform the following steps:

1.

Select the down arrow next to Shared Components and select Application>Definition.

 

2.

Click the Logo tab.

 

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 the Application <n> link from the developer tool bar.

 

Back to Topic List

Creating Users  

As mentioned earlier, this application uses Oracle Application Express Authentication. To create new users, you use the functions already available in Oracle Application Express. You create some new users and then in the next topic you restrict access to certain areas of the application to certain people. To do this, perform the following steps:

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: [email protected]
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: [email protected]
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: [email protected]
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.

 

Back to Topic List

Restricting Access

Now that you have users defined, you can restrict access to certain portions of the application. In this topic, you allow only certain users to edit tasks. To do this, perform the following steps:

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

Back to Topic List

A. Add an Access Control Page

To secure the application so that only privileged users can perform certain operations, you create an Access Control Page that is used to define which users can access which part of the application. Perform the following steps:

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

 

Back to Topic

B. Identify Privileged Users

In a previous topic, you created 3 users: Brad.Knight, John.Bell and Susie.Parker. In this topic, you identify Brad.Knight to be allowed to edit the application but he can't change any user access. John.Bell can only view the information in the application, he can not make any changes. And finally Susie.Parker is the administrator of the application so she can change anything in addition to changing the user privileges. Peform the following steps:

1.

Enter john.bell for the username and select View for the privilege, then click Add User again.

 

2.

Enter brad.knight for the username and select Edit for the privilege, then click Add User again.

 

3.

Enter susie.parker for the username and select Administrator for the privilege, then click Apply Changes.

 

4.

Next you can define which areas of the application are restricted. Click the Application <n> link from the developer tool bar.

 

Back to Topic

C. Apply Authorization Schemes to Application Components

With your authorization scheme created, users with View privilege can review the Employee Information but can not change it. Users with Edit privilege can make changes to Employee Information but can not make changes to the access control list. Users with Administrator privilege, can make any changes including to the access control list. Perform the following steps:

1.

Select the down arrow next to Shared Components and select Application>Definition.

 

2.

Click the Security tab.

 

3.

Under Authorization, change the scheme to access control - view and click Apply Changes.

 

4.

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.

 

5.

In the Regions area, click the Report link. Note, you may need to click the Regions () icon under Page Rendering.

 

6.

Click the Edit () icon in front of EMPLOYEE_ID.

 

7.

Click the Authorization tab.

 

8.

Select access control - edit for the Authorization Scheme and click Apply Changes.

 

9.

Click Apply Changes.

 

10.

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 Button () icon under Page Rendering.

 

11.

Click the Create link.

 

12.

Click the Authorization section button.

 

13.

Select the access control - edit authorization scheme and click Apply Changes.

14.

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.

 

15.

Click the Show All () icon

 

16.

In the Page section, click the No link for the Authorization Page Attribute.

 

17.

For Authorization Scheme, select access control - edit. Click Apply Changes. Click the Run Page icon.

 

18.

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

 

19.

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 9. Click 9 - Access Control Administration Page.

 

20.

Click the No link for Authorization.

21.

Select access control - administrator for the Authorization Scheme and click Apply Changes. Now you are ready to run the application.

22.

Enter 1 for Page and click <.

 

23.

Click Run.

24.

If you are already logged in as OBE, click Logout. Enter brad.knight and welcome1 for the username and password. Then click Login.

25.

Select Employee Information.

26.

Notice that Brad can edit the Employees. Click Logout.

27.

Enter john.bell and welcome1 for the username and password. Then click Login.

28.

Select Employee Information.

29.

John has only view privilege and therefore can not edit the Employees. He also does not see the Create button displayed.

30.

Change the page number in your URL to try and access Page 3.

Example url  …/f?p=426:2:2101953412249296357::NO
Change to    …/f?p=426: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.

 

Back to Topic

 

In this tutorial, you learned how to:

Create and modify a table
Create an application
Add and modify a report, form, chart, and calendar
Create LOVs and validation items
Update a navigation list
Apply a new theme
Create a user and limit access to a user

Back to Topic List

Place the cursor over this icon to hide all screenshots.