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. |
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.
Before you perform this tutorial, you should:
1. | Perform the Installing Oracle Database 10g on Windows tutorial. |
|
2. | Perform the Installing and Configuring Oracle HTML DB 2.0 on Windows tutorial. |
|
3. | Download and unzip the htmldb.zip file into your working directory (c:\wkdir). |
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.
|
|
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.
|
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
|
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.
|
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
|
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
(
|
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.
|
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.
|
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 (
|
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
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:
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 (
|
||||||||
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 (
|
||||||||
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 (
|
||||||||
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.
|
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
(
|
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.
|
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
|
4. |
Enter the following information, and then click Create and Create Another. User Name: Susie.Parker
|
5. |
Enter the following information, and then click Create User. User Name: John.Bell
|
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.
|
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:
1. | Setting Up a User Group |
2. | Creating an Authorization Scheme |
3. | Applying the Authorization Scheme. |
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
|
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.
|
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
|
6. |
Enter the following, and scroll up to the top of the page. Identify error message displayed when scheme violated:
|
7. |
Click Create.
|
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 (
|
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.
|
10. |
Click the Authorization link.
|
11. |
For Authorization Scheme, select ADMIN. Click the Top icon
(
|
12. |
Click Apply Changes.
|
13. |
To see the changes, click the Run Page icon (
|
14. |
Notice that neither the Edit link nor the Create button is being displayed. This is because you are logged in as the OBE user and the OBE user is not an administrator. To log out of the application, click the Logout link at the top right of the window.
|
15. |
For User Name, enter brad.knight and for Password, enter welcome1.
Click Login.
|
16. |
Click a Project link (i.e. Public Website).
|
17. |
Notice that because Brad is an administrator, both the Edit link and the Create button is being displayed. Click the Logout link.
|
18. |
For User Name, enter obe and for Password, enter obe. Click Login.
|
19. |
Click Project link.
|
20. |
The edit icon and Create button are not displayed again. The last task to do is to protect against direct access to the Master-Detail page. You can change your URL and access it directly now. You notice that your URL, ends with f?p=…. Following this are the application number, the page number, and the session number. You can manually change the page number from 3 to 4 and still access the Master-Detail page. Change the page number from 2 to 3 in the URL.
Example url …/f?p=101:2:2101953412249296357::NO Press the ENTER key on your keyboard.
|
21. |
Notice that you are now at the Projects/Tasks page, ready to enter a new Project and create Tasks for it. But ideally, because the OBE user is not an administrator, this page should be hidden from him or her. To protect against this, you need to associate the authorization scheme with the Master-Detail page itself, not just its access mechanisms. Click Edit Page 3 from the Developer Links.
|
22. |
In the Page bar, click the Edit Attributes button.
|
23. |
Click the Security Link.
|
24. |
For Authorization Scheme, select ADMIN. Click the Top icon
(
|
25. |
Click Apply Changes.
|
26. |
To run the page, click the Run Page icon (
|
27. |
Instead of seeing the page, you now see your message denying you
access to the page: Click the OK link.
|
You created an Overview page when you created the application. Now, you can add content to that page. You can also add a report and a bar chart. To do this, perform the following steps:
1. |
In the Page bar, for Page, enter 1 and click Go.
|
2. |
In the Regions section, click the Create icon
(
|
3. |
For Region Type, select the Report option, and click Next >.
|
4. |
For Report Implementation, select SQL Report, and click Next >.
|
5. |
For Title, enter Task Status. Click Next >.
|
6. |
For SQL Query, enter:
SELECT null link,
status,
count(id) Number_Tasks
FROM tasks
GROUP BY null, status
Because there is no need to conditionally display this region, you can now click Create Region.
|
7. |
To see the report, click the Run Page icon (
|
8. |
The report is displayed. You can rearrange the columns,
change the headings, and turn off the display of the number of rows. Click
the Edit Page 1 link from the Developer Links at the bottom of
the page.
|
9. |
In the Regions section, click Report to the left of Task Status.
|
10. |
The number of tasks should display before the status.
To reorder the columns, click the change column sequence icon (
|
11. |
For Headings Type, select Custom. This changes the headings to initcaps. For NUMBER_TASKS, change Heading to Number of Tasks. Select right as the Column Alignment for NUMBER_TASKS. Also, make sure that No Pagination Selected is selected for the Pagination Scheme. Then, click Apply Changes.
|
12. |
To see your changes, click the Run Page icon
(
|
13. |
Now that you have the summary report, you can add
a bar chart. Click
Edit Page 1 from the Developer Links at the bottom of the page.
|
14. |
In the Regions section, click the Create icon
(
|
15. |
For Region Type, select the Chart option, and click Next >.
|
16. |
For Chart Type, select the Bar (HTML) option,
and click Next >.
|
17. |
For Title, enter Open Issues by Assignee. Click Next >.
|
18. |
For Enter SQL Query, enter:
SELECT null link, nvl(assigned_to,'unassigned') label, count(id) Number_Tasks FROM tasks WHERE status = 'open' GROUP BY null, assigned_to
In the Include in summary area, deselect the Axis and Number of data points check boxes. This makes only Sum of all values display at the bottom of the chart. There is no need to conditionally display this region, so you can now click Create Region.
|
19. |
To see the revised Overview page with both charts, click
the Run Page icon (
|
20. |
The report and chart are now displayed. You have just built a fully functional application.
|
In this tutorial, you learned how to:
![]() |
Create and modify a table | |
![]() |
Create an application | |
![]() |
Add a report | |
![]() |
Add and edit a Tabular form | |
![]() |
Apply a new theme | |
![]() |
Create a user and limit access to a user | |
![]() |
Add content to a page |