Building a Calendar Using Oracle HTML DB Release 2
Building a Dial Chart Using Oracle
HTML DB
This tutorial shows you how to
build a dial chart using Oracle HTML DB. This chart will include the ability to change the time frame so you can see the values for different time periods.
Approximately 30 minutes
This tutorial will discuss the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
each individual icon in the following steps to load and view only the screenshot
associated with that step.
What Is HTML DB?
HTML DB provides a declarative development framework for creating
database-centric Web applications. Development is done using an online service.
Deployment is done by downloading a run-time module and your application and
running the application within your enterprise.
What Components Make Up HTML DB?
HTML DB contains three main components. You will use all three
components throughout this tutorial.
| Application Builder |
Using the Application Builder, you can build database-centric
interactive Web applications.
|
| SQL Workshop |
The SQL Workshop enables you
to run SQL statements and SQL scripts. |
| Data Workshop |
With the Data Workshop, you
can load data into and extract data from the database. |
Terminology
The following concepts are important to know when working
with HTML DB:
| Application |
An application is a collection of pages with branches
that connect them. Its attributes include the authentication method, default
UI templates, and authorization rules.
|
| Page |
A page is defined by how it is
rendered or displayed and by how it is processed. Processing refers to the
events and logic that occur when the page is submitted. Each page is rendered
dynamically at run time from metadata defined by the application developer.
How a page looks is controlled by page templates. |
| Region |
Content is displayed in regions,
which are logical subsections of a page. Each page can have any number of
regions of several different types. These types include: HTML text, SQL
Queries, PL/SQL-generated HTML, and charts. Each region is rendered using
a region template. Regions are positioned on the page using display points
defined in the page template. |
| Item |
Application items are used to
generate HTML form elements. The Applications engine manages the PL/SQL-generated
of the HTML, and you as the developer simply choose the item type. Applications
support more than 50 such types including date pickers, pop-up lists of
values, text areas with spell checking, and so on. The value of an item
is automatically stored into the application's session state, which can be referenced at any
point within the user's session.
|
In this Tutorial
In this tutorial you will use the built-in wizard for generating a Dial Chart. The specific Dial Chart you will build will be a "Spam-O-Meter". Many companies now use software to filter out spam (i.e. useless, unwanted email) before it even hits a user's inbox. The chart will reflect the percentage of incoming email that is identified as spam. Once you build the Chart, you will modify it to allow a user to change the time frame so that the user can see the values for the last day, week, month or the entire log.
For this tutorial, you will create 1 table:
| OBE_EMAIL_LOG |
This table tracks all incoming email and indicates whether or not the email is spam. |
Additional database objects, such as a sequence and trigger, will also be created to support the table. A system generated primary key will be used for the table.
Back to Topic List
Before starting this tutorial, you should have:
Back to Topic List
There are several ways that objects can be created using Oracle HTML DB. They are all functions of the SQL Workshop:
| 1. |
Use Create Object from the main SQL Workshop page. This function walks through all the choices necessary to create the selected object type. |
| 2. |
Use the SQL Command Processor from the main SQL Workshop page. This runs commands that you type or paste in. The processor can only process one command at a time. |
| 3. |
Upload a script, using Scripts from the main SQL Workshop page. This script would contain the create object statements. Once the script is uploaded, it can then be run. |
| 4. |
Create a script on-line. This way all the objects will be created with just a few clicks without the need to first save a file to your hard drive. This is the method you will use in this tutorial. |
To create the objects necessary for this Dial Chart application, perform
the following steps:
| 1. |
Enter the following URL to log in to HTML DB.
http://<host name>:<port>/pls/htmldb/htmldb
|
| 2. |
To log in to HTML DB, enter the following details, and
click Login.
Workspace: obe
Username: obe
Password: obe
|
| 3. |
To create the table that the application will be based
on, click SQL Workshop.
|
| 4. |
Under SQL Scripts, Click Scripts.
|
| 5. |
Click Create.
|
| 6. |
Enter DDL for Dial Chart for the Name and click Next.
|
| 7. |
Copy and paste the SQL located in the ddl_email_log.sql file in your working directory to the Script field. Then click Create Script .
|
| 8. |
Your script has been created. Click Run.
|
| 9. |
Make sure that OBE is selected for Parse As and click Run Script.
|
| 10. |
Your script was executed and the Summary Statistics page appears. You should see that there are 52 successes and no errors. Now you can view the objects you created. Click the breadcrumb SQL Workshop.
|
| 11. |
Under Database Browser, click Tables.
|
| 12. |
To view the details of an object, click the magnifying glass ( ) icon next to the OBE_EMAIL_LOG table.
|
| 13. |
The table details are displayed. Now you are ready to load the data into the objects you just created. Click the SQL Workshop tab.
|
Back to Topic List
To properly view the application that you will create, you need data loaded into the tables. Data can be loaded in manually, via an Oracle import, or using the Oracle HTML DB Data Workshop or SQL Workshop. You will use both the Data Workshop and SQL Workshop to load your data.
Note: If you look at the DDL, you notice that the sequences used for the primary keys were set to start at 40. That was to leave room for the data. The before insert triggers were coded in such a way that the sequence is only accessed if a primary key value is not provided. This was done so that they will not need to be disabled in order for you to load data.
In this tutorial, you will load the data using the same mechanism you used to create the objects. You will create a script and run it.
| 1. |
Under SQL Scripts, click Scripts.
|
| 2. |
Click Create.
|
| 3. |
Enter Load Email Log Data for the Script Name and click Next.
|
| 4. |
Copy and paste the text located in the load_email_log.sql file in your working directory into the Script field. Then click Create Script .
|
| 5. |
Your script has been created. Click Run.
|
| 6. |
Make sure that OBE is selected for Parse As and click Run Script.
|
| 7. |
Your script was executed and the Summary Statistics window appears. You should see that there a number of successes and no errors. Now you can create your Dial Chart Application. Click the breadcrumb Workspace OBE.
|
Back to Topic List
Now that the objects that support the application have been created and the data has been loaded, the user interface can be created. Using the Application Builder, an application will be created and then a chart will be added.
Before you can build a chart, you need to have an application for that chart to belong to. Perform the following steps:
| 1. |
On the HTML DB home page, click Create Application
>.
|
| 2. |
Because you are going to create an application from scratch,
keep the default value of From Scratch for the Creation Method, and click
Next >.
|
| 3. |
For Name, enter Dial Chart Application. To
specify that the application will have one page, select 1 from the Pages drop-down list. Click Next >.
|
| 4. |
Click No Tabs,
and click Next >.
|
| 5. |
Enter Dial Chart for the Page Name and click Next >.
|
| 6. |
Select Theme 9, and
click Next >.
|
| 7. |
Click Create Application.
|
Back to Topic List
Now that the application structure has been created, content can be added. You could create your chart on a new page or add it to an existing page. Because a blank page was created for you when you created the application, you will add your chart on page 1. Perform the following tasks:
| 1. |
Select Page 1, Dial Chart.
|
| 2. |
Under the Regions section, click the Create icon ( ).
|
| 3. |
Select Chart for Region Type and click Next >.
|
| 4. |
Select Dial - Sweep for the Chart Type and click Next >.
|
| 5. |
Enter Spam-O-Meter for the Title and click Next >.
|
| 6. |
Copy and paste the text located in the chart.sql file in your working directory into the SQL Query field and click Create Region.
Note: The syntax for a dial chart is:
| First Column |
Value |
| Second Column |
Maximum Value
|
| Third Column |
Low Value (optional)
|
| Fourth Column |
High Value (optional)
|
The initial chart creation presumes that you are selecting values to be used as a percent. The percent is calculated for you. The ’value’ column should be the total number of rows that are true. The ‘maximum_value’ column should be the total number of rows in the sample. The display will take the ‘value’ and divide it by the ‘maximum_value’ to calculate the percentage to display. If you want something other than a percent, you will need to modify the chart after it is created. When a chart is a percentage, there is no need to include the low and high values. These should be included for non-percentage dial charts such as speedometers. In a speedometer, they would control the lower and upper bounds of the dial display.
|
| 7. |
To see your newly created chart, click the run page icon ( ) in the upper right.
|
| 8. |
Enter obe for both your username and password and click Login.
|
| 9. |
Your chart is displayed. Note: you may be asked to download and install the SVG Viewer which you will need to install in order to see the chart.
|
Back to Topic List
Now that you have the chart, you will make a few modifications. First, you will create a List of Values to define the valid time frames. Next, you will add an item to allow the selection of the time frame and modify the chart query to use this value as a filter. You will add an alert value to the chart. This will make the sweep portion of the display change to red when the value goes above the alert value. You will remove the chart title “Chart 1” because you already have a region title and you will add a footer to explain the contents of the chart. Lastly, you will add a default branch to the page so the page will know which page to call when the select list is submitted. Perform the following:
Back to Topic List
Create a List of Values
| 1. |
From the developer links at the bottom of the display, select Edit Page 1.
|
| 2. |
Under Shared Components, in the Lists of Values section, click the create icon ( ).
|
| 3. |
For Source, keep the default of From Scratch and click Next >.
|
| 4. |
Enter TIME_FRAME for Name and make sure Static is selected for Type and click Next >.
|
| 5. |
Enter the following for the Display Value/Return Value List then click Create Static LOV.
| Entire Log |
A |
| Last 24 Hours |
1 |
| Last 7 Days |
7 |
| Last 30 Days |
30 |
Note: You will understand the reasoning behind the return values when you add the where clause to the chart select statement.
|
| 6. |
Your List of Values was created.
|
Back to Topic
Create an Item to Add to the Chart
| 1. |
Click the edit page icon in the upper right corner ( ). This will take you to page 1.
|
| 2. |
In the Items section, click the Create icon ( ) .
|
| 3. |
Select Select List for Item Type and click Next >.
|
| 4. |
Select Select List with Submit for Control Type and click Next >. This eliminates the need for a submit or go button to refresh the page.
|
| 5. |
Enter P1_TIME_FRAME for Item Name and click Next >.
|
| 6. |
Select TIME_FRAME for Named LOV, select No for Display Null Option and click Next >.
|
| 7. |
Keep all the defaults for Item Attributes and click Next >.
|
| 8. |
Enter A for Default and click Create Item.
|
| 9. |
Your item was created.
|
Back to Topic
Modify your Chart
| 1. |
In the Regions section, select Chart to the left of Spam-O-Meter.
|
| 2. |
Remove the value in the Chart Title field. In the Chart Query section, append the following to the query then click the Query link.
where :P1_TIME_FRAME is null
or :P1_TIME_FRAME = 'A'
or email_date >= (sysdate - :P1_TIME_FRAME)
|
| 3. |
Change the Alert Value to 15. Then click the Top icon ( )
|
| 4. |
Click the Report Definition tab. This will save your changes.
|
| 5. |
Select Header and Footer.
|
| 6. |
Enter the following in the Region Footer section and click the Top icon ( ).
<br>
This displays the percentage of incoming email that is spam.
|
| 7. |
Click Apply Changes.
|
| 8. |
Your Chart has been modified.
|
Back to Topic
Create a Branch
| 1. |
In the Branches section, click the Create icon ().
|
| 2. |
Keep all the defaults and click Next >.
|
| 3. |
Enter 1 for Page and click Next >.
|
| 4. |
Click Create Branch.
|
| 5. |
Your branch has been created.
|
Back to Topic
You are now ready to run the application to see the Dial Chart Application. Perform the following steps:
| 1. |
Click on the Run icon () in the upper right corner of the application window.
|
| 2. |
Your Dial Chart is displayed. You can use the Time Frame selector to change the time frame and notice how the chart changes. Notice that the spam display for the Entire log has hit the alert level. Change the Time Frame to Last 24 Hours.
|
| 3. |
Notice that in the last 24 hours, the level of spam is under the alert level.
|
Back to Topic List
In this lesson, you learned how to:
Back to Topic List
Place
the cursor on this icon to hide all screenshots.
|
Bookmark
