0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

OBE Home > Database 10 g > Single Instance > Application Development > HTML DB

Creating a Database-Centric Web Application Using Oracle HTML DB

Purpose

This tutorial shows you how to rapidly create an application by using Oracle HTML DB.

Time to Complete

Approximately 1 hour

Topics

This tutorial will discuss the following topics:

 Overview
 Prerequisites
 Creating a Table from a Spreadsheet
 Adding a Master Table
 Modifying a Table

Creating an Application

 Adding a Report
 Enabling Column Heading Sorting
 Adding a Master-Detail Form
 Editing Application Objects
 Applying a New Theme
 Creating Users
 Limiting Access
 Adding Content to the Overview Page
 Summary
 Related Information

Viewing Screenshots

 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.

Overview

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

Prerequisites

Before starting this tutorial, you should have:

1.

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

 

2.

Downloaded and unzipped htmldb.zip into your working directory.

 

Back to Topic List

Creating a Table from a Spreadsheet

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

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

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

                               
                                 Move your mouse over this icon to see the image
                              
                            

 

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

Move your mouse over this icon to see the image

 

6.

Click Browse.

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

12.

Under Database Browser, click Tables.

Move your mouse over this icon to see the image

 

13.

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

Move your mouse over this icon to see the image

 

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 .

Move your mouse over this icon to see the image

 

15.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

Back to Topic List

Adding a Master Table

To create a master table on the Project column, perform the following steps:

1.

In the Database Browser section, click Tables.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

Enter the following values, and click Next >.

New Table Name: PROJECTS
New Sequence: PROJECT_SEQ

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

Back to Topic List

Modifying a Table

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.

Move your mouse over this icon to see the image

 

2.

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

Move your mouse over this icon to see the image

 

3.

Enter the following values, and click Next >.

Add Column: PROJECT_LEAD
Type: VARCHAR2
Length: 30

Move your mouse over this icon to see the image

 

4.

Click Finish.

Move your mouse over this icon to see the image

 

5.

To view the table definition, click Browse Table.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

Back to Topic List

Creating an Application

To create the application framework and a few empty pages, perform the following steps:

1.

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

Enter the following values, and click Next >.

Page Name 1: Overview
Page Name 2: All Tasks

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

Click Create Application.

Move your mouse over this icon to see the image

 

9.

To view the application, click Run.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

Back to Topic List

Adding a Report 

To add a report to the All Tasks page, perform the following steps:

1.

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

Move your mouse over this icon to see the image

 

2.

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

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

For Title, enter Tasks and click Next >.

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

9.

Click Next >.

Move your mouse over this icon to see the image

 

10.

The Join Condition should default to:

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

Click Next >.

Move your mouse over this icon to see the image

 

11.

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

Move your mouse over this icon to see the image

 

12.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

Back to Topic List

Enabling Column Heading Sorting    

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.

Move your mouse over this icon to see the image

 

2.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

5.

Click Apply Changes.

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

10.

Now the newest task is displayed first.

Move your mouse over this icon to see the image

 

Back to Topic List

Adding a Master-Detail Form    

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.

Move your mouse over this icon to see the image

 

2.

To create a new page, click Create Page >.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

11.

Click Next >.

Move your mouse over this icon to see the image

 

12.

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

Move your mouse over this icon to see the image

 

13.

This results in a new Sequence selection list getting displayed. From the Selection drop-down list, select PROJECT_SEQ and click Next >.

Move your mouse over this icon to see the image

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.

Move your mouse over this icon to see the image

 

15.

This will make the Sequence selection list appear. From the Selection drop-down list, select TASKS_SEQ and click Next >.

Move your mouse over this icon to see the image

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

19.

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

22.

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

Move your mouse over this icon to see the image

 

23.

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

Move your mouse over this icon to see the image

 

24.

The new Master-Detail page is displayed.

Move your mouse over this icon to see the image

 

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

Editing Application Objects

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:

 Reduce the Project display size
 Increase the Task Name display size
 Increase the Date display size

Change the date format

 Change the Status field to a drop-down list

To do all this, perform the following steps:

1.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

To close the edit window, click Cancel.

Move your mouse over this icon to see the image

 

6.

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.

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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.

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

11.

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

Move your mouse over this icon to see the image

 

12.

For Display Value and Return Value, enter the following:

Display Value Return Value
open open
on-hold on-hold
closed closed

Click Create Static LOV.

Move your mouse over this icon to see the image

 

13.

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

Move your mouse over this icon to see the image

 

14.

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.

Move your mouse over this icon to see the image

 

15.

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

Move your mouse over this icon to see the image

 

16.

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

Move your mouse over this icon to see the image

 

17.

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

Move your mouse over this icon to see the image

 

18.

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

Move your mouse over this icon to see the image

 

19. You will now be on the START_DATE column page. Under the Tabular Form Element section, select Date Picker (DD-MON-YYYY) from the Date Picker Format Mask drop-down list. For Element Width, enter 12. Click the Next Column button (  ).

Move your mouse over this icon to see the image

 

20. You will now be on the END_DATE column page. Under the Tabular Form Element section, select Date Picker (DD-MON-YYYY) from the Date Picker Format Mask drop-down list. For Element Width, enter 12. Click the Next Column button (  ).

Move your mouse over this icon to see the image

 

21. You will now be on the STATUS column. Under the Tabular Form Element section, select Select List (named LOV) from the Display As drop-down list. From the links at the top of the page, click the List of Values link.

Move your mouse over this icon to see the image

 

22. Under the List of Values section, select STATUSES from the Named LOV drop-down list. Click the Top icon (  ).

Move your mouse over this icon to see the image

 

23. To save all the changes, click Apply Changes.

Move your mouse over this icon to see the image

 

24. To run the page and see your changes, click the Run Page icon (  ).

Move your mouse over this icon to see the image

 

25.

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.

Move your mouse over this icon to see the image

 

Back to Topic List

Applying a New Theme   

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

Move your mouse over this icon to see the image

 

2.

Click Shared Components.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

To create a new theme, click Create Theme.

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

Click Create.

Move your mouse over this icon to see the image

 

8.

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.

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

11.

To confirm the theme switch, click Switch Theme.

Move your mouse over this icon to see the image

 

12.

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

Move your mouse over this icon to see the image

 

13.

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.

Move your mouse over this icon to see the image

 

Back to Topic List

Creating Users   

As mentioned earlier, this application uses HTML DB Authentication. To create new users, you use the functions already available in Oracle HTML DB. You will 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 Workspace OBE link in the breadcrumb menu.

Move your mouse over this icon to see the image

 

2.

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

Move your mouse over this icon to see the image

 

3.

Click Create New User.

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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.

Move your mouse over this icon to see the image

 

Back to Topic List

Limiting Access

Many a time, we need to limit access to our applications. To do this, perform the following steps:

1. Set up a user group.
2. Create an authorization scheme.
3. Apply the authorization scheme.

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 will hold the names of all the administrators (namely, Brad and Susie). To do this, perform the following steps:

1.

Click Data Workshop.

Place the cursor over this icon to see the image

 

2.

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

Place the cursor over this icon to see the image

 

3.

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

Place the cursor over this icon to see the image

 

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

Place the cursor over this icon to see the image

 

6.

For Table Name, enter ADMINS. Click Next >.

Place the cursor over this icon to see the image

 

7.

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

Place the cursor over this icon to see the image

 

8.

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

Place the cursor over this icon to see the image

 

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 will 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 will succeed. Else it will fail.

To create an authorization scheme, perform the following steps:

1.

Click Shared Components.

Place the cursor over this icon to see the image

 

2.

In the Security section, click the Authorization link.

Place the cursor over this icon to see the image

 

3.

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

Place the cursor over this icon to see the image

 

4.

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

Place the cursor over this icon to see the image

 

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

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

 

7.

Click Create.

Place the cursor over this icon to see the image

 

Back to Topic

3. Applying the Authorization Scheme

With your authorization scheme created, you will now associate the scheme with the ID (Edit) column of the Projects report. You will 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 Builder tab, even though it is the current tab.

Place the cursor over this icon to see the image

 

2.

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

Place the cursor over this icon to see the image

 

3.

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

Place the cursor over this icon to see the image

 

4.

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

Place the cursor over this icon to see the image

 

5.

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

Place the cursor over this icon to see the image

 

6.

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.

Place the cursor over this icon to see the image

 

7.

Click Apply Changes.

Place the cursor over this icon to see the image

 

8.

To navigate back to Page Definition, click Cancel.

Place the cursor over this icon to see the image

 

9.

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.

Place the cursor over this icon to see the image

 

10.

Click the Authorization link.

Place the cursor over this icon to see the image

 

11.

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

Place the cursor over this icon to see the image

 

12.

Click Apply Changes.

Place the cursor over this icon to see the image

 

13.

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

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

 

15.

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

Place the cursor over this icon to see the image

 

16.

Click the Projects tab.

Place the cursor over this icon to see the image

 

17.

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

Place the cursor over this icon to see the image

 

18.

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

Place the cursor over this icon to see the image

 

19.

Click the Projects tab.

Place the cursor over this icon to see the image

 

20.

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.

Place the cursor over this icon to see the image

 

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

Place the cursor over this icon to see the image

 

22.

In the Page bar, click the Edit Attributes button.

Place the cursor over this icon to see the image

 

23.

Click the Security Link.

Place the cursor over this icon to see the image

 

24.

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

Place the cursor over this icon to see the image

 

25.

Click Apply Changes.

Place the cursor over this icon to see the image

 

26.

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

Place the cursor over this icon to see the image

 

27.

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.

Place the cursor over this icon to see the image

 

Back to Topic List

Adding Content to the Overview Page    

You created an Overview page when you created the application. Now, you will add content to that page. You will 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.

Move your mouse over this icon to see the image

 

2.

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

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

For Title, enter Task Status . Click Next >.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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.

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

11.

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.

Move your mouse over this icon to see the image

 

12.

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

Move your mouse over this icon to see the image

 

13.

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.

Move your mouse over this icon to see the image

 

14.

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

Move your mouse over this icon to see the image

 

15.

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

Move your mouse over this icon to see the image

 

16.

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

Move your mouse over this icon to see the image

 

17.

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

19.

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

Move your mouse over this icon to see the image

 

20.

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

Move your mouse over this icon to see the image

 

Back to Topic List

 

Summary

In this tutorial, you've learned how to:

 Create and modify a table
 Create an application
 Add a report
 Add and edit a Master-Detail form
 Apply a new theme
 Create a user and limit access to a user
 Add content to a page

Back to Topic List

Related Information

 To learn more about Oracle Database 10g, refer to additional OBEs on the OTN Web site.
 To ask a question about this OBE tutorial, post a query on the OBE Discussion Forum.

Back to Topic List

 Place the cursor on this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve