Using the Application Builder, you can build database-centric interactive Web applications.

An application is a collection of pages with branches that connect them. Its attributes include the authentication method, default UI templates, and authorization rules.

Completed the Installing and Configuring Oracle HTML DB 1.6 tutorial or have access to an already installed HTML DB 1.6 installation.

Downloaded and unzipped htmldb.zip into your working directory.

Enter the following URL to log in to HTML DB.

http://< host name>:7777/pls/htmldb/htmldb

To log in to HTML DB, enter the following details, and click Login.

Workspace: obe
Username: obe
Password: obe

To create the table that the application will be based on, click Data Workshop.

To create the table based on a spreadsheet, click Import Spreadsheet Data.

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

Click Browse.

Locate the tasks.txt file in the /home/oracle/wkdir directory and click Open.

Because the data in the text file is tab delimited, enter \t in the Separator field. Click Next >.

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

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.

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.

Under Database Browser, click Tables.

To view the table definition, click the Object Detail icon ( ) next to the TASKS table.

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 .

Select the Check All check box to enable it, and click Query.

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.

In the Database Browser section, click Tables.

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.

In the Tasks list on the right of the window, click Create Lookup Table.

To specify the column on which the master table is to be created, select PROJECT - varchar2, and click Next >.

Enter the following values, and click Next >.

New Table Name: PROJECTS
New Sequence: PROJECT_SEQ

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.

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.

To see the definition of the new PROJECTS table, click PROJECTS in the Parent Table column.

Notice that the new PROJECTS table contains a numeric primary key along with the PROJECT column.

In the Tasks list on the right, click Manage Table.

Because you are going to add a column to the table, retain the default selection of Add Column, and click Next >.

Enter the following values, and click Next >.

Add Column: PROJECT_LEAD
Type: VARCHAR2
Length: 30

Click Finish.

To view the table definition, click Browse Table.

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.

Select the Check All check box to enable it, and click Query.

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.

For Project Lead, enter Tom Suess. Click Apply Changes.

You will see the data that you added. To begin creating the application, click Workspace OBE on the breadcrumb menu .

On the HTML DB home page, click Create Application >.

Because you are going to create an application from scratch, keep the default value of From Scratch for the Creation Method, and click Next >.

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

Keep the default value of One Level of Tabs, and click Next >.

Enter the following values, and click Next >.

Page Name 1: Overview
Page Name 2: All Tasks

Notice that by default the Page Tab names reflect the page names that you specified earlier. Click Next >.

Keep the default value of Theme 1 (red), and click Next >.

Click Create Application.

To view the application, click Run.

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.

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.

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.

In the Regions section, click the Create icon ( ).

To add a report, select Report and click Next >.

Keep the default value of Easy Report, and click Next >.

For Title, enter Tasks and click Next >.

To select the table whose columns will be added to the report, select PROJECTS from the Table/View Name drop-down list.

From the Select Columns list, select PROJECT, and then click to move it to the Columns Selected list.

You want to add columns from the TASKS table too. From the Table/View Name drop-down list, select TASKS.

From the Select Columns list, select all columns except ID and PROJECT_ID, and click to move them to the Columns Selected list.

Click Next >.

The Join Condition should default to:

"PROJECTS"."PROJECT_ID" = "TASKS"."PROJECT_ID"

Click Next >.

Keep the default values for Report Attributes, and click Create Region.

To run the page, click the Run Page icon ( ) at the top right of the page, just below the Data Workshop tab.

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.

From the developer links at the bottom, click Edit Page 2.

In the Regions section, to the left of Tasks, click the RPT link.

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.

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.

Click Apply Changes.

Click the Run Page icon ( ) at the top right of the page.

To view the previous 15 records, click the Previous link at the bottom of the report.

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.

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.

Now the newest task is displayed first.

From the developer links at the bottom of the page, click Edit Application.

To create a new page, click Create Page >.

Select the Page with Component option, and click Next >.

To specify the component that is going to be added to the page, select the Form option. Click Next >.

Select the Master Detail Form option, and click Next >.

To specify the master table, select PROJECTS from the Table/View Name drop-down list.

To select all the columns, click the Add All icon ( ).

The Displayed Columns list lists the columns that will be displayed in both the report and the master region. Click Next >.

To specify the detail table, select TASKS from the Table/View Name drop-down list.

To select all the columns, click the Add All icon ( ).

Click Next >.

To specify that the primary key source is an existing sequence, select the Existing sequence option.

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.

For the detail table primary key source, select the Existing Sequence option.

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.

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

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

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

For Tab Options, select the Use an existing tab set and create a new tab within the existing tab set option.

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

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.

To see the new page that displays all the projects, click the Run Page link.

Notice the new tab at the top right. Click the Edit link ( ) to the left of the Public Website Project.

The new Master-Detail page is displayed.

Change the date format

From the Developer Links at the bottom of the page, click Show Edit Links.

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.

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.

In the Element section, change the Height to 1. Scroll to the top of the page and then click Apply Changes.

To close the edit window, click Cancel.

The changes have been made to the application, but to see them, you need to refresh your page. First, turn off Edit Links by clicking Hide Edit Links from the Developer Links at the bottom of the page.

To refresh the page, click the Reload button on your browser's standard toolbar. This button would differ based on the browser being used.

Now, you will edit the page to make the changes to the Tasks report. Click the Edit Page 4 link from the Developer Links at the bottom of the page.

Before you edit the report, you need to create the Named LOV that will be 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 ( ).

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

For Name, enter STATUSES. For Type, keep the default Static. Then click Next >.

For Display Value and Return Value, enter the following:

Click Create Static LOV.

To return to the page, click the Edit Page icon ( ) at the top right, under the Data Workshop tab.

To display the columns attributed of the Tasks report, In the Regions section, click the Q link to the left of Tasks in the Regions section.

Click the Edit Attributes link ( ) to the left of TASK_NAME.

Click the Tabular Form Element link at the top of the page.

Enter 50 for Element Width. To return to the top of the page, click the Top icon ( ).

Click the Next Column button ( ), to the right of the Apply Changes button.

All the changes are complete. Notice that you have been returned to the last page that you were viewing. This is because the Session state is remembered by Oracle HTML DB.

Click the Edit Application link from the Developer Links at the bottom of the page.

Click Shared Components.

In the User Interface section, click the Themes and Templates link.

To create a new theme, click Create Theme.

As you are using a theme from the HTML DB Repository, keep the default value. Click Next >.

From the Theme options, select the Theme 10 option. Click Next >.

Click Create.

Notice that there is a check mark to the right of Red, indicating that it is the current theme. To switch to the Sand theme, click Switch Theme.

For Switch to Theme, keep the default value of 10. Sand . Click Next >.

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

To confirm the theme switch, click Switch Theme.

To see the new theme, click the Run Page icon ( ) at the top right of the page.

Notice that the entire appearance of your application has changed. The tabs have shifted to the right. The color scheme has changed. Click Edit Application from the Developer Links at the bottom.

Click the Workspace OBE link in the breadcrumb menu.

In the Workspace Administration list at the right of the window, click the Manage Users link.

Click Create New User.

Enter the following information, and then click Create and Create Another .

User Name: Brad.Knight
Password: welcome1
Email Address: brad.knight@oracle.com
First Name: Brad
Last Name: Knight
Default Schema: OBE

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

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

Notice that the three users have been created. You will now set up administrator access to the application. Click the Workspace OBE link in the breadcrumb menu.

Click Data Workshop.

In the Data Import section, click the Import Text Data link.

Click New table for Import To and Copy and paste (up to 30KB) for Import From. Click Next >.

Select the following text, and press [Ctrl] + [C] to copy it:

USERID
BRAD.KNIGHT
SUSIE.PARKER

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

For Table Name, enter ADMINS. Click Next >.

Keep the default values for the Primary Key details. To import the data, click Import Data.

To return to your application details, click the Application Builder tab.

Click Shared Components.

In the Security section, click the Authorization link.

To begin creating a new authorization scheme, click Create >.

Because you are creating the scheme from scratch, keep the default value for Creation Method, and click Next >.

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

Enter the following, and scroll up to the top of the page.

Identify error message displayed when scheme violated:
Access denied. Administrator Privilege Required.

Click Create.

Click the Application Builder tab, even though it is the current tab.

Under Pages, click the Projects link in the Name column.

To display the column attributes for the Projects report, click the RPT link to the left of Projects in the Regions section.

Click the Edit Attributes icon ( ) to the left of PROJECT_ID.

There are links just under the Column Attributes heading for each section of details. Click the Conditions link.

Just above the Conditional Display section is the Authorization section. In the Authorization section, select ADMIN for Authorization Scheme. To return to the top of the page, click the Top icon ( ) at the top-right corner of the Authorization section.

Click Apply Changes.

To navigate back to Page Definition, click Cancel.

Now, you will 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.

Click the Authorization link.

For Authorization Scheme, select ADMIN. Click the Top icon ( ) at the top-right corner of the Authorization section.

Click Apply Changes.

To see the changes, click the Run Page icon ( ) at the top right of the page.

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.

For User Name, enter brad.knight and for Password, enter welcome1. Click Login.

Click the Projects tab.

Notice that because Brad is an administrator, both the Edit link and the Create button is being displayed. Click the Logout link.

For User Name, enter obe and for Password, enter obe. Click Login.

Click the Projects tab.

The edit icon and Create button are not displayed again. The last thing to do is to protect against direct access to the Master-Detail page. You can change your URL and access it directly now.

You will 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 3 to 4 in the URL.

Example url  …/f?p=120:3:2101953412249296357::NO
Change to    …/f?p=120: 4:2101953412249296357::NO

Press the ENTER key on your keyboard.

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 4 from the Developer Links.

In the Page bar, click the Edit Attributes button.

Click the Security Link.

For Authorization Scheme, select ADMIN. Click the Top icon ( ) at the top-right corner of the Authorization section.

Click Apply Changes.

To run the page, click the Run Page icon ( ) at the top right of the page.

Instead of seeing the page, you will now see your message denying you access to the page:
Error Access denied. Administrator Privilege Required.

Click the OK link.

In the Page bar, for Page, enter 1 and click Go.

In the Regions section, click the Create icon ( ).

For Region Type, select the Report option, and click Next >.

For Report Implementation, select SQL Report, and click Next >.

For Title, enter Task Status . Click Next >.

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.

To see the report, click the Run Page icon ( ) at the top right.

The report is displayed. You will 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.

In the Regions section, click Q to the left of Task Status.

The number of tasks should display before the status. To reorder the columns, click the change column sequence icon to the right of the STATUS column ( ).

For Headings Type, select Custom. This will change 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.

To see your changes, click the Run Page icon ( ) at the top right.

Now that you have the summary report, you will add a bar chart. The chart will be displayed next to, not below, the summary report. You can do this by putting the new region in column 2. Click Edit Page 1 from the Developer Links at the bottom of the page.

In the Regions section, click the Create icon ( ).

For Region Type, select the Chart option, and click Next >.

For Chart Type, select the Bar (HTML) option, and click Next >.

For Title, enter Open Issues by Assignee. From the Column drop-down list, select 2. Click Next >.

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 will make only Sum of all values display at the bottom of the chart.

To see the revised Overview page with both charts, click the Run Page icon ( ) at the top right.

The report and chart are now displayed. You have just built a fully functional application.