Creating a Database-Centric Web Application
Creating a Database-Centric Web Application Using Oracle
HTML DB
This tutorial shows you how to rapidly create an application
by using Oracle HTML DB.
Approximately 1 hour
This tutorial will discuss the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
each individual icon in the following steps to load and view only the screenshot
associated with that step.
What Is HTML DB?
HTML DB provides a declarative development framework for creating
database-centric Web applications. Development is done using an online service.
Deployment is done by downloading a run-time module and your application and
running the application within your enterprise.
What Components Make Up HTML DB?
HTML DB contains three main components. You will use all three
components throughout this tutorial.
| Application Builder |
Using the Application Builder, you can build database-centric
interactive Web applications.
|
| SQL Workshop |
The SQL Workshop enables you
to run SQL statements and SQL scripts. |
| Data Workshop |
With the Data Workshop, you
can load data into and extract data from the database. |
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 defined by how it is
rendered or displayed and by how it is processed. Processing refers to the
events and logic that occur when the page is submitted. Each page is rendered
dynamically at run time from metadata defined by the application developer.
How a page looks is controlled by page templates. |
| 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 |
Application items are used to
generate HTML form elements. The Applications engine manages the PL/SQL-generated
of the HTML, and you as the developer simply choose the item type. Applications
support more than 50 such types including date pickers, pop-up lists of
values, text areas with spell checking, and so on. 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.
All session states are 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 starting this tutorial, you should have:
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.
http://<host name>:7777/pls/htmldb/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 that the application will be based
on, click Data Workshop.
|
| 4. |
To create the table based on a spreadsheet, click Import
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 /home/oracle/wkdir
directory and click Open.
|
| 8. |
Because the data in the text file is tab delimited,
enter \t in the Separator field. Click Next >.
|
| 9. |
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 >.
|
| 10. |
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.
|
| 11. |
After the table is created and the data is loaded, you
will be 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.
|
| 12. |
Under Database Browser, click Tables.
|
| 13. |
To view the table definition, click the Object Detail
icon ( ) next
to the TASKS table.
|
| 14. |
This page displays only the table definition. To view
all the data in the table, click Query By Example in the Tasks list on the right of the window.
|
| 15. |
Select the Check All check box to enable it, and
click Query.
|
| 16. |
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.
To return to the summary page, click the SQL Workshop tab.
|
Back to Topic List
To create a master table on the Project column, perform the
following steps:
| 1. |
In the Database Browser section, click Tables.
|
| 2. |
Ensure that OBE and TABLE are selected as values for
the schema and type, respectively. To view the table definition, scroll
down to the bottom of the Database Object Results table, and click the
Object Detail ()
icon next to the TASKS table.
|
| 3. |
In the Tasks list on the right of the window, click
Create Lookup Table.
|
| 4. |
To specify the column on which the master table is to
be created, select PROJECT - varchar2, and click Next >.
|
| 5. |
Enter the following values, and click Next >.
New Table Name: PROJECTS
New Sequence: PROJECT_SEQ
|
| 6. |
Click Finish.
NOTE: You may receive error ORA-20001. Please
continue with the tutorial, because this message is the result of a bug in the
beta version of the Oracle HTML DB software being used. It will not affect
the tutorial.
|
| 7. |
To view the tables that are referenced by the TASKS
table, click the arrow to expand Foreign
Keys (Tables this table references) at the bottom of the page.
|
| 8. |
To see the definition of the new PROJECTS table, click PROJECTS in
the Parent Table column.
|
| 9. |
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, you will
enhance the PROJECTS table by adding a PROJECT LEAD column to it. To do this,
perform the following steps:
| 1. |
In the Tasks list on the right, click Manage Table.
|
| 2. |
Because you are going to add a column to the table, retain
the default selection of Add Column, and click Next >.
|
| 3. |
Enter the following values, and click Next >.
Add Column: PROJECT_LEAD
Type: VARCHAR2
Length: 30
|
| 4. |
Click Finish.
|
| 5. |
To view the table definition, click Browse Table.
|
| 6. |
You will see the modified table definition with the
new PROJECT_LEAD column added. Click
Query By Example in the Tasks list on the right of the page.
|
| 7. |
Select the Check All check box to enable it, and
click Query.
|
| 8. |
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 4, Public Website.
|
| 9. |
For Project Lead, enter Tom Suess. Click Apply
Changes.
|
| 10. |
You will see the data that you added. To begin creating
the application, click Workspace OBE on the breadcrumb menu.
|
Back to Topic List
To create the application framework and a few empty pages,
perform the following steps:
| 1. |
On the HTML DB home page, click Create Application
>.
|
| 2. |
Because you are going to create an application from scratch,
keep the default value of From Scratch for the Creation Method, and click
Next >.
|
| 3. |
For Name, enter Project Tasks Application. To
specify that the application will have two pages, select 2 from the Pages drop-down list. Click Next >.
|
| 4. |
Keep the default value of One Level of Tabs,
and click Next >.
|
| 5. |
Enter the following values, and click Next >.
Page Name 1: Overview
Page Name 2: All Tasks
|
| 6. |
Notice that by default the Page Tab names reflect the
page names that you specified earlier. Click Next >.
|
| 7. |
Keep the default value of Theme 1 (red), and
click Next >.
|
| 8. |
Click Create Application.
|
| 9. |
To view the application, click Run.
|
| 10. |
Your new application was created by using HTML DB Authentication.
You will need to log in with a username/password combination that has access
to the workspace that owns the application. Use your development login
of obe/obe and click Login.
|
| 11. |
Notice that the skeleton application has been created
with the pages and tabs that you specified. There is no content on the
pages right now. It will be added later. Click the All Tasks tab.
|
| 12. |
The next step will be to add a report to the All Tasks
page. After logging in as a developer, you can modify the application by using links at the bottom of the page. From the
developer links, click Edit Page 2.
|
Back to Topic List
To add a report to the All Tasks page, perform the following
steps:
| 1. |
In the Regions section, click the Create icon
( ).
|
| 2. |
To add a report, select Report and click
Next >.
|
| 3. |
Keep the default value of Easy Report, and click
Next >.
|
| 4. |
For Title, enter Tasks and click Next >.
|
| 5. |
To select the table whose columns will be added to
the report, select PROJECTS from the Table/View Name drop-down list.
|
| 6. |
From the Select Columns list, select PROJECT,
and then click 
to move it to the Columns Selected list.
|
| 7. |
You want to add columns from the TASKS table too. From the
Table/View Name drop-down list, select TASKS.
|
| 8. |
From the Select Columns list, select all columns except
ID and PROJECT_ID, and click
to move them to the Columns Selected list.
|
| 9. |
Click Next >.
|
| 10. |
The Join Condition should default
to:
"PROJECTS"."PROJECT_ID"
= "TASKS"."PROJECT_ID"
Click Next >.
|
| 11. |
Keep the default values for Report Attributes, and click
Create Region.
|
| 12. |
To run the page, click the Run Page icon ( ) at the top right of the page, just
below the Data Workshop tab.
|
| 13. |
The report that you just created appears. All the data
is retrieved from the TASKS table along with the PROJECT_NAME column from the
PROJECTS table. All the code was automatically generated by Oracle HTML
DB. To display the next 15 rows, click the Next link at the bottom
of the report.
|
Back to Topic List
In the report, the Tasks are being displayed in the order
that they were created. We can enable sorting based on the column heading to
allow the user to decide which column(s) to sort on. To do this, perform the
following steps:
| 1. |
From the developer links at the bottom, click Edit
Page 2.
|
| 2. |
In the Regions section, to the left of Tasks, click
the RPT link.
|
| 3. |
To enable column heading sorting for all the columns,
select
the respective check box for each column under the Sort column in the Report Column Attributes section.
|
| 4. |
Whenever you enable sorting, you should also provide
a default sort order. To do this, under Sort Sequence, select
1 from the drop-down list for PROJECT. For TASK_NAME, select 2 from
the drop-down list.
|
| 5. |
Click Apply Changes.
|
| 6. |
Click the Run Page icon (
) at the top right of the page.
|
| 7. |
To view the previous 15 records, click the Previous link at the bottom of the
report.
|
| 8. |
Notice that the report is now ordered alphabetically,
first by Project and then by Task Name. The column headings are now underlined
because they are links. To sort on the Start Date, click the Start Date
column heading.
|
| 9. |
Notice that the report is sorted with the oldest Start
Dates displaying first. To sort in descending order, click the Start
Date column heading again.
|
| 10. |
Now the newest task is displayed first.
|
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 Master-Detail form enables you to do just this. To
add a Master-Detail form, perform the following steps:
| 1. |
From the developer links at the bottom of the page,
click Edit Application.
|
| 2. |
To create a new page, click Create Page >.
|
| 3. |
Select the Page with Component option, 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 Master Detail Form option, and click
Next >.
|
| 6. |
To specify the master table, select PROJECTS from the Table/View Name
drop-down list.
|
| 7. |
To select all the columns, click the Add All
icon ( ).
|
| 8. |
The Displayed Columns list lists the columns that will
be displayed in both the report and the master region. Click Next >.
|
| 9. |
To specify the detail table, select TASKS from the Table/View Name
drop-down list.
|
| 10. |
To select all the columns, click the Add All
icon ( ).
|
| 11. |
Click Next >.
|
| 12. |
To specify that the primary key source is an existing
sequence, select the Existing sequence option.
|
| 13. |
This results in a new Sequence selection list getting
displayed. From the Selection drop-down list, select PROJECT_SEQ
and click Next >.
This identifies how the primary key of PROJECTS will
be generated. The page created will allow for the creation of new Projects.
|
| 14. |
For the detail table primary key source, select the
Existing Sequence option.
|
| 15. |
This will make the Sequence selection list appear. From the
Selection drop-down list, select TASKS_SEQ and click Next >.
This identifies how the primary key of TASKS will be
generated. The page created will allow for the creation of new Tasks.
You notice a Finish button on this page. Most
wizards offer this "early out" to speed development. For this process,
there are several options left unselected that would be beneficial to
the application.
|
| 16. |
The Define Master page options determine whether the user
will be able to scroll through Projects and, if so, in what order. From
the Master Row Navigation Order drop-down list, select the PROJECT
option. You could use PROJECT_ID but because this is a system-generated
primary key that has no real meaning, it is better to scroll through alphabetically
by Project. The default is to include a master report. It would be nice
to have a report displaying all Projects as well the Master-Detail, so
retain this default, and click Next >.
|
| 17. |
Using the Choose Layout page, you can specify whether you
want to view the details on the same page or on a separate page. The more
preferable method for this case is to view and edit the details of both
the Project and Tasks on the same page. So, leave the default value of
Edit detail as tabular form on same page, and click Next >.
|
| 18. |
With the Page Attributes page, you can identify the
page number, page title, and region title for the objects being created.
Keep the default values, and click Next >.
|
| 19. |
For Tab Options, select the Use an existing tab set
and create a new tab within the existing tab set option.
|
| 20. |
This results in the display of additional options. For
Tab Set, select the TS1 (Overview, All Tasks) option. This is the
tab set that was created when you defaulted the application. For New Tab
Label, enter Projects and then click Next >.
|
| 21. |
The Master Detail Confirmation page displays all your
choices and gives you the opportunity to use the < Previous button
to modify any selections. Click Create.
|
| 22. |
To see the new page that displays all the projects,
click the Run Page link.
|
| 23. |
Notice the new tab at the top right. Click the Edit
link ( ) to
the left of the Public Website Project.
|
| 24. |
The new Master-Detail page is displayed.
|
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. Because you included Master Row
Navigation, the < Previous and Next > buttons are displayed. They
will help to scroll through the projects. These buttons do not commit changes.
If you make a change to the data, you need to apply the changes with the help
of the Apply Changes button before scrolling.
In the detail region, you will 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" will be deleted. If you use this
feature, you will 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. There are two ways in which you can
edit the objects displayed on a page. The first is to use Show Edit Links from
the Developer Links at the bottom of the page. This displays an icon next to
each item that can be edited and, when selected, will display a pop-up allowing
you to make changes. This method can be used for most item types but not for
for Report Attributes. The second way is to go back into the Application Builder
Page Definition by selecting Edit Page 2 from the Developer Links.
You will:
To do all this, perform the following steps:
| 1. |
From the Developer Links at the bottom of the page,
click Show Edit Links.
|
| 2. |
Click the Edit link (
) to the right of the Project field.
Caution: Be careful not to select the one above the Project field.
That one is for the ID columns, which is hidden from display but can still
be edited using Edit Links.
|
| 3. |
You will now see a pop-up with the details of P4_PROJECT.
Under the Page Item section, select the Text Field option from the Display As drop-down list. Then click the Element link
at the top of the page.
|
|
Bookmark