Creating a Database-Centric Web Application Using Oracle Application Express (formerly HTML DB)
Creating a Database-Centric Web Application Using Oracle
Application Express (formerly HTML DB)
This tutorial shows you how to rapidly create an application
by using Oracle Application Express (formerly HTML DB).
Approximately 1 hour
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 (formerly known as Oracle HTML DB)?
Oracle has recently announced that the Oracle HTML DB products name has changed to Oracle Application Express.
Oracle Application Express is a tool for building Web-based applications on an Oracle database. In Oracle Application Express, you do all development via a Web browser based on database objects such as tables and views in an Oracle database. Oracle Application Express makes development easy via pre-built templates and wizards to create reports, charts and forms.
What Components Make Up Oracle Application Express?
Oracle Application Express contains three 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, and load data into and extract data from the database. |
| Administration |
Used to manage services and users and to monitor activity |
Terminology
The following concepts are important to know when working with HTML DB:
| 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 isl display 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
All applications are rendered in real time from data stored
in database tables. When you create or extend your application, no code is generated;
metadata is created or modified and stored in database tables. The applications-rendering
engine reads the metadata and displays the page accordingly.
A session state is also stored in the database. Each page
view results in a new database session, so when the applications engine is not
processing a page, no database resources are consumed (except for the storage
space used by table rows). The unique session identifier used to manage your
application's session state as the user runs the application from start to finish
is unrelated to the many individual database sessions created and run by the
run-time engine for each page view.
Applications that execute SQL or PL/SQL are parsed as the
"owner" of the application. When your workspace is provisioned, you
are given the right to parse as a specific schema. Thus all your Oracle database
rights and privileges are those of the schema you choose to parse as.
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 HTML DB (Change the <hostname> to localhost or your specific hostname).
http://<hostname>:7777/pls/htmldb
|
| 2. |
To log in to HTML DB, enter the following details, and
click Login.
Workspace: obe
Username: obe
Password: obe
|
| 3. |
To create the table based on a spreadsheet, click the down arrow next to SQL Workshop > Utilities > Data Import/Export.
|
| 4. |
Click the down arrow next to Import, then click 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 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 Import 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 Tasks table under the list of tables in the left navigator.
|
| 13. |
This page displays only the table definition. To view
all 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 database from this page, and also add rows to the table.
|
Back to Topic List
To create a master table on the Project column, 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: PROJECTS
New Sequence: PROJECT_SEQ
|
| 5. |
Click Finish.
|
| 6. |
To see the definition of the new PROJECTS table, click PROJECTS in the Parent Table column.
|
| 7. |
Notice that the new 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 PROJECTS table by adding a PROJECT LEAD column to it.
To do this,
perform the following steps:
| 1. |
Make sure the 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_LEAD
Type: VARCHAR2
Length: 30
|
| 3. |
Click Finish.
|
| 4. |
You see the modified table definition with the
new PROJECT_LEAD column added. Click the Data tab to view the data.
|
| 5. |
You see all the projects that were referenced in the
TASKS table. You can change data from here. Click the Edit icon
( ) on the left
of Project Id for Public Website.
|
| 6. |
For Project Lead, enter Tom Suess. Click Apply
Changes.
|
| 7. |
You see the data that you added. To begin creating
the application, click Home on the breadcrumb menu.
|
Back to Topic List
To create the application framework and a few empty pages,
perform the following steps:
| 1. |
From the HTML DB 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. |
Select the Report Page Type and click the up arrow to select the table you want the report to be based on.
|
| 4. |
Click PROJECTS.
|
| 5. |
Click Add Page.
|
| 6. |
Select Report and Form Page Type and select PROJECTS(1) for Subordinate to Page. Then click the up arrow to select the table.
|
| 7. |
Click TASKS.
|
| 8. |
Click Add Page.
|
| 9. |
Click on the link Tasks for Page 2.
|
| 10. |
You need to link the PROJECTS Master Page with this page. In the Parent Page to This Page Column Associations section, select PROJECT for the Link Column, select PROJECT_ID for the parent report column - column 1, select PROJECT_ID for the link to this report column - report column. Deselect the Use theme-specific edit button check box. The variable #PROJECT# should appear in the Link Text field. Click Apply Changes.
|
| 11. |
Select the PROJECTS link for Page 1.
|
| 12. |
For the PROJECT_ID column, select No for the Show parameter and click Apply Changes.
|
| 13. |
Click Next >.
|
| 14. |
Accept the default to create One Level of Tabs. Click Next > .
|
| 15. |
You do not want to shared these components with other applications. Click Next >.
|
| 16. |
Accept the defaults. Click Next >.
|
17.
|
Accept the default Theme 1. Click Next >.
|
18.
|
Review your choices. Click Create.
|
| 19. |
The Application and its associated pages were created. To run the application, click the Run Application icon.
|
| 20. |
Enter obe for both the User Name and Password. Then click Login.
|
| 21. |
The List of Projects is displayed. Notice that the PROJECT_ID column is not displayed and the link is shown on the PROJECT column. Click the Public Website Project.
|
| 22. |
The Projects list of Tasks is displayed. You can edit the task by clicking on the edit icon right in front of the task you want to edit.
|
| 23. |
The Task Details are displayed.
|
Back to Topic List
If there are very few tasks, it is all right if they are all
displayed together. However, when there are many tasks, it is
better to
see
the tasks sorted by Project. A Tabular (or a Master-Detail) form enables you to do just this. To
add a Tabular form, perform the
following steps:
| 1. |
From the developer links at the bottom of the page,
click Edit Page 3.
|
| 2. |
To create a new page, click Create.
|
| 3. |
Make sure Region on this Page is selected, and click
Next >.
|
| 4. |
To specify the component that is going to be added to
the page, select the Form option. Click Next >.
|
| 5. |
Select the Tabular Form option, and click
Next >.
|
| 6. |
Click Next >.
|
| 7. |
Select the up arrow to select a table.
|
| 8. |
Select the PROJECTS link.
|
| 9. |
Click Next >.
|
| 10. |
Shift-select all the columns and click Next >.
|
| 11. |
Make sure the PROJECT_ID is select for the Primary Key column and click Next >.
|
| 12. |
To specify that the primary key source is an existing sequence, select the Existing sequence option. This results in a new Sequence selection list being displayed. From the Selection drop-down list, select PROJECT_SEQ and click Next >.
This identifies how the primary key of PROJECTS is generated. The page created allows for the creation of new Projects.
|
| 13. |
The columns on the form that are updateable are listed and selected. Click Next > .
|
| 14. |
Change the Region Title to Projects and make sure the Page is set to 3. Then, click Next > .
|
| 15. |
Accept the default buttons to be created. Click Next >.
|
| 16. |
Change When Cancel Button Pressed Branch to this Page to 2 and click Next >.
|
| 17. |
Review your choices and click Finish.
|
| 18. |
In order for this form to only show the tasks for a particular project, you need to add a where clause to the SELECT statement in the Projects region. Click Edit Page .
|
| 19. |
Under Regions, click the Projects report link.
|
| 20. |
Click the Source link.
|
| 21. |
Add the following WHERE clause at the end of the SELECT statement. Then click the Top arrow.
WHERE PROJECT_ID = :P3_PROJECT_ID
|
| 22. |
Click Apply Changes.
|
| 23. |
To see the new page that displays all the projects,
click the Run ( )icon.
|
| 24. |
Change the name of the project and click Submit.
|
| 25. |
The Project has been updated.
|
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 the tasks. 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
Back to Topic List
Although the Master-Detail form is
fully functional, the looks can be improved. Perform the following tasks in this topic:
 |
Create a List of Values |
|
Change the Date Format |
|
Change the Status field to use the LOV |
To do all this, perform the following steps:
| 1. |
Click the Edit icon in front of the Task Check software licenses. Page 3 appears which is the page you want to make changes to.
|
| 2. |
Click the Edit Page 3 Link.
|
| 3. |
Before you edit the report, you need to create the Named
LOV that is referenced by the Status field. By creating it as a Named
LOV, it can be used by other pages in the application too. Under the List
of Values section on the right, click the Create button (  ).
|
| 4. |
For Create List of Values, keep the default value
From Scratch, and click Next >.
|
| 5. |
For Name, enter STATUSES. For Type, keep the
default Static. Then click Next >.
|
| 6. |
For Display Value and Return
Value, enter the following:
| Display Value |
Return Value |
| open |
open |
| on-hold |
on-hold |
| closed |
closed |
Click Create List of Values.
|
| 7. |
To return to the page, click the Run Page 3 icon
at the top right, under the Administration tab.
|
| 8. |
Click the Show Edit Links link.
|
| 9. |
Click the Edit icon ( ) to the right of Start Date.
|
| 10. |
Under the Page Item section, select Date Picker (DD-MON-YYYY) from the Display As drop-down list. Click Apply Changes.
|
| 11. |
Click the Edit icon ( ) to the right of End Date.
|
| 12. |
Under the Page Item section, select Date Picker (DD-MON-YYYY) from the Display As drop-down list. Click Apply Changes.
|
| 13. |
Click the Edit icon ( ) to the right of Status.
|
| 14. |
Under
the Page Item section, select Select List from the Display As drop-down list. From the links at the top of the page, click the
LOV link.
|
| 15. |
Under the List of Values section, select STATUSES from the
Named LOV drop-down list. Click the Top icon ( ).
|
| 16. |
Click Apply Changes.
|
| 17. |
Click the Refresh button in your browse and notice the changes that occur. The Start Date and End Date format changes and the Status field now uses the List of Values. Click Hide Edit Links.
|
Back to Topic List
When you created the application, you selected the red theme.
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 HTML DB Repository.
To apply a new theme, perform the following steps:
| 1. |
Click the Edit Application link from the Developer
Links at the bottom of the page.
|
| 2. |
Click the down arrow next to Shared Components > User Interface > Themes.
|
| 3. |
To create a new theme, click Create.
|
| 4. |
Accept the default HTML DB Repository and click Next >.
|
| 5. |
From the Theme options, select the Theme 10 option.
Click Next >.
|
| 6. |
Click Create.
|
| 7. |
To switch to the Sand theme,
click Switch Theme.
|
| 8. |
For Switch to Theme, make sure 10.
Sand is selected. Click Next >.
|
| 9. |
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 >.
|
| 10. |
To confirm the theme switch, click Switch Theme.
|
| 11. |
To see the new theme, click the Run Page icon
( ) at the top
right of the page.
|
| 12. |
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.
|
Back to Topic List
As mentioned earlier, this application uses HTML DB Authentication.
To create new users, you use the functions already available in Oracle HTML
DB. You create some new users and then 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. |
Select the down arrow next to Administration > Manage HTML DB Users > Create Developer link.
|
| 3. |
Enter the following information, and then click Create
and Create Another.
User Name: Brad.Knight
Password: welcome1
Email Address: brad.knight@oracle.com
Default Schema: OBE
|
| 4. |
Enter the following information, and then click Create
and Create Another.
User Name: Susie.Parker
Password: welcome1
Email Address: susie.parker@oracle.com
First Name: Susie
Last Name: Parker
Default Schema: OBE
|
| 5. |
Enter the following information, and then click Create
User.
User Name: John.Bell
Password: welcome1
Email Address: john.bell@oracle.com
First Name: John
Last Name: Bell
Default Schema: OBE
|
| 6. |
Notice that the three users have been created. You
now set up administrator access to the application. Click the Home link in the breadcrumb menu.
|
Back to Topic List
In this tutorial, you only allow Brad Knight and Susie Parker to edit the Tasks. John Bell is not allowed to change a Task.
To do this, perform the following steps:
Back to Topic List
1. Setting Up a User Group
You have created three users called Brad, Susie, and John.
Out of these, Brad and Susie are administrators and the only people who are
allowed access to the Master-Detail report. John is not an administrator, and
therefore should not be given access to the same. To accomplish this, you first
create a table that holds the names of all the administrators (namely, Brad
and Susie). To do this, perform the following steps:
| 1. |
Select the down arrow next to SQL Workshop > Utilities > Data Import/Export.
|
| 2. |
Select the down arrow next to Import and click Text Data.
|
| 3. |
Click New table for Import To and Copy and paste (up to 30KB)
for Import From. Click Next >.
|
| 4. |
Select the following text, and press [Ctrl] + [C] to copy it:
USERID
BRAD.KNIGHT
SUSIE.PARKER
|
| 5. |
Switch back to the HTML DB window. Click in the Data text box, and press
[Ctrl] + [V] to paste the list of users. Click Next >.
|
| 6. |
For Table Name, enter ADMINS. Click Next >.
|
| 7. |
Keep the default values for the Primary Key details. To import the data,
click Import Data.
|
| 8. |
To return to your application details, click the Application Builder
tab.
|
Back to Topic
2. Creating an Authorization
Scheme
Now that you have the table populated with a list
of the administrators of your application, you create an authorization
scheme to validate the current user against the table of administrators. If
the user is specified in the table, the authorization scheme succeeds. Otherwise,
it fails.
To create an authorization scheme, peform the following steps:
| 1. |
Click Project Tasks Application .
|
| 2. |
Click the down arrow next to Shared Components > Security > Authorization Schemes.
|
| 3. |
To begin creating a new authorization scheme, click Create >.
|
| 4. |
Because you are creating the scheme from scratch, keep the default value for
Creation Method, and click Next >.
|
| 5. |
Enter the following, and scroll down to the Identify error message
displayed when scheme violated section.
Name: ADMIN
Scheme Type: Exists SQL Query
Expression 1:
SELECT '1'
FROM admins
WHERE userid = :APP_USER
|
| 6. |
Enter the following, and scroll up to the top of the page.
Identify error message displayed when scheme violated:
Access denied. Administrator Privilege Required.
|
| 7. |
Click Create.
|
Back to Topic
3. Applying the Authorization
Scheme
With your authorization scheme created, you associate
the scheme with the ID (Edit) column of the Projects report. You then associate
it with the Create button and lastly with the Master-Detail page itself. This
is to protect against someone trying to access the page by changing the URL.
To apply the authorization scheme, perform the following steps:
| 1. |
Click the Application # breadcrumb link .
|
| 2. |
Under Pages, click the Tasks link in the Name column.
|
| 3. |
To display the column attributes for the Tasks report, click the Report link to the left of Tasks in the Regions
section.
|
| 4. |
Click the Edit Attributes icon ()
to the left of ID.
|
| 5. |
There are links just under the Column Attributes heading for each section
of details. Click the Autorizations link.
|
| 6. |
Select ADMIN for Authorization Scheme.
Then, click the Top icon ().
|
| 7. |
Click Apply Changes.
|
| 8. |
To navigate back to Page Definition, click the Page Definition breadcrumb link.
|
| 9. |
Now, you associate the authorization scheme with the Create button
so that only administrators can create new projects. To do this, in the
Buttons section, click the CREATE link to the left of Redirect.
Caution: Do not click the
icon, but the link to edit the current CREATE button.
|
| 10. |
Click the Authorization link.
|
| 11. |
For Authorization Scheme, select ADMIN. Click the Top icon
() at the top-right corner of the Authorization section.
|
| 12. |
Click Apply Changes.
|
| 13. |
To see the changes, click the Run Page icon (
) at the top right of the page.
|
| 14. |
|
Bookmark