Building a Functional Application Using Oracle Application Express 3.0
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
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.
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:
Back to Topic List
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
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
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
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
|
Bookmark