Building a Calendar Using Oracle HTML DB Release 2
Building a Calendar Using Oracle
HTML DB
This tutorial shows you how to
build a calendar page using Oracle HTML DB. This calendar will include a link to a report displaying details.
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 calendar. Once you specify the table on which the calendar is based, you can create drill down links to information stored in specific columns.
Oracle HTML DB supports two calendar types:
 |
Easy Calendar: creates a calendar based on schema, table, and columns you specify. The wizard prompts you to select a date column and display column. |
|
SQL Calendar: creates a calendar based on a SQL query you provide. The SQL SELECT statement you provide must include at least two columns, a date column and display column. The calendar application you create in this tutorial will be of this type. |
The date column determines which days on the calendar will contain entries. The display column defines a specific row that will display a calendar date.
For this tutorial, you will create 3 tables:
| Projects |
This table will track all current projects. |
| People |
This table will contain information about who can be assigned to handle issues. |
| Issues |
This table will hold all the information about an issue, including the project to which it is related and the person assigned to rectify the issue. |
Below is a simple Entity Relationship Diagram (ERD) displaying how the three tables relate to one another.
Additional database objects, such as sequences and triggers, will also be needed to support the tables. System generated primary keys will be used for all tables so that all the data can be edited without needing to implement cascade update.
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 Calendar 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 Calendar for the Name and click Next.
|
| 7. |
Copy and paste the SQL located in the calddl.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. |
Make sure OBE is selected from the list of Schemas.
|
| 13. |
To view the details of an object, click the magnifying glass ( ) icon next to the HT_PEOPLE table.
|
| 14. |
The table details are displayed. Now you are ready to load the data into the objects you just created. Click the Data 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.
You need to perform the following tasks:
Back to Topic List
Load Project Data
The Projects data will be loaded using the Data Workshop. You can import text data, xml data or spreadsheet data. You will import text data using the Projects data contained in the load_projects.txt file. Perform the following steps:
| 1. |
From the Data Workshop tab, under Data Import, click Import Text Data.
|
| 2. |
Import To an Existing Table and Import From Copy and Paste, then click Next.
|
| 3. |
Make sure the OBE Schema is selected and click Next.
|
| 4. |
Click the icon to search for a Table.
|
| 5. |
Select HT_PROJECTS (table).
|
| 6. |
Click Next.
|
| 7. |
Copy and paste the text located in the load_projects.txt file in your working directory into the Data field. Then click Next.
|
| 8. |
Click Import Data.
|
| 9. |
The HT_PROJECTS table now contains data. You should see that 5 records succeeded and 0 records failed. Click the SQL Workshop tab.
|
Back to Topic
Update Project Dates
The Projects have been created but the dates need to be updated to make the projects current. To do this, you will create a script and run it. Perform the following steps:
| 1. |
Under SQL Scripts, click Scripts.
|
| 2. |
Click Create.
|
| 3. |
Enter Update Project Dates for the Script Name and click Next.
|
| 4. |
Copy and paste the text located in the update_project_dates.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 are 4 successes and no errors. Now you can load the people data. Click the breadcrumb Script Repository .
|
Back to Topic
Load People Data
You will load the People data by creating a script and running it. You will import text data using the People data contained in the load_people.sql file. Perform the following steps:
| 1. |
Click Create.
|
| 2. |
Enter Load People Data for the Name and click Next.
|
| 3. |
Copy and paste the text located in the load_people.sql file in your working directory into the Script field. Then click Create Script.
|
| 4. |
Your script has been created. Click Run.
|
| 5. |
Make sure that OBE is selected for Parse As and click Run Script.
|
| 6. |
Your script was executed and the Summary Statistics window appears. You should see that there are many successes and no errors. Now you can load the issue data. Click the breadcrumb Script Repository .
|
Back to Topic
Load Issues Data
You will load the Issues data by creating a script and running it. You will import text data using the Issues data contained in the load_issues.sql file. Perform the following steps:
| 1. |
Click Create.
|
| 2. |
Enter Load Issues Data for the Name and click Next.
|
| 3. |
Copy and paste the text located in the load_issues.sql file in your working directory into the Scripts field. Then click Create Script.
|
| 4. |
Your script has been created. Click Run.
|
| 5. |
Make sure that OBE is selected for Parse As and click Run Script.
|
| 6. |
Your script was executed and the Summary Statistics window appears. You should see that there are many successes and no errors. Now you are ready to create your application. Click the breadcrumb Workspace OBE.
|
Back to Topic
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 page will be created to display Project Tasks on a Calendar.
You can quickly default pages to both report on and create data for selected tables within a schema by using the Create Application Wizard. Alternatively, you can create the application definition and then add pages as desired. The latter method will be used in this tutorial. 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 Calendar 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 Calendar for the Page Name and click Next >.
|
| 6. |
Select Theme 10, and
click Next >.
|
| 7. |
Click Create Application.
|
| 8. |
Your application has been created. You can see a preview of the application theme. Click Run.
|
| 9. |
Enter obe for both your username and password and click Login.
|
| 10. |
Your see the Default Home Page.
Although the page has no content, the following items were created:
| Navigation Links |
In the upper right of the screen are two Navigation Bar Entries. They allow the user to Print the current page and to Logout. |
| Developer Links |
At the bottom of the page are developer links. These are displaying because the user who is logged in is also a developer of the application. Regular users, those who have only been granted access to run the application, will not see these links. From left to right, you can:
| Edit Application |
Edit the application |
| Edit Page 1 |
Edit the page that is running |
| New |
Add a new component to the current page |
| Session |
Open a new page with the session details |
| Debug |
Show the page in debug mode |
| Show Edit Links |
Displays quicklinks next to each object on the page that can be edited. The link will appear to the right of the NavBar items, tabs, region titles, buttons, and items. Clicking on the link will popup a window to edit the object. |
|
| Login Page |
This is the page that you used to login to the application.
|
| Templates |
A basic set of templates was created within the new application that fit the UI Theme selected.
|
| Authentication Schemes |
Two authentication schemes were created. Internal Account Credentials is used by default but a scheme for database authentication was also created.
|
|
Back to Topic List
Now that the application structure has been created, content can be added. You could begin with the calendar but because the calendar will link to a report displaying all the details of the selected Issue, you will begin with the report. Perform the following tasks:
Back to Topic List
Create a Report Page
| 1. |
Click Edit Application in the developer links area at the bottom of the page.
|
| 2. |
The Application Builder home page appears with your new application selected. Click Create Page >.
|
| 3. |
Select the Page with Component option, and click Next >.
|
| 4. |
To specify the component that is going to be added to the page, select the Report option. Click Next >.
|
| 5. |
Select the SQL Report option, and click Next >.
|
| 6. |
Enter 2 for the Page and Issue Details for the Page Name. Then click Next >.
|
| 7. |
Select Do not use tabs and click Next >.
|
| 8. |
Copy and paste the text located in the issue_details_rpt.sql file in your working directory into the SQL SELECT area. Then click Next > .
|
| 9. |
Make sure that Region Template is set to Reports Region, and Report Template is set to default: vertical report, look 1 (include null columns).
Enter View Issue for Region Name and click Next>.
|
| 10. |
Review your selections and click Finish.
|
| 11. |
Your Report page was created successfully.
|
Back to Topic
Create an Item for Query Reference
Before you can link to this report, you need to create the item that your query referenced.
| 1. |
Click Edit Page.
|
| 2. |
Under the Items section, click the Create icon ( ).
|
| 3. |
Select Hidden for the Item Type, and click Next >.
|
| 4. |
Enter P2_ISSUE_ID for the Item Name and click Next >.
|
| 5. |
The value of the item will be set when the page is called so there is nothing to specify on this page. Click Create Item.
|
| 6. |
Your Item was created.
|
Back to Topic
Create a Cancel Button
You need to create a button that will return the user back to the Calendar, or calling page.
| 1. |
Under the Buttons section, click the Create icon ().
|
| 2. |
Select View Issue and click Next >.
|
| 3. |
Make sure Create a button in a region position is selected and click Next >.
|
| 4. |
Enter CANCEL for the Button Name. This will automatically appear as the Button Label. Make sure Template Driven is selected for Button Type and Redirect to URL without submitting page is selected for Action. This is appropriate since this page is just for viewing. No processes would fire if the page were submitted so submitting is not necessary. Then click Next >.
|
| 5. |
Make sure Button is selected for Button Template and click Next >.
|
| 6. |
Select Region Template Position #CLOSE# for Position and click Next >.
|
| 7. |
Enter 1 for Page. This will redirect the user back to the Calendar page. Enter 2 for Clear Cache. Whenever a user clicks the Cancel button, the values on page 2 will be cleared. This protects against the user accessing the page without coming from the Calendar and seeing the previous issue. Then click Create Button.
|
| 8. |
Your button was created. Because this tutorial is about how to create a calendar, not how to customize a report, you will leave the report in its default state, however, for a real application, you would want to edit the Report Attributes to refine the column headings.
|
Back to Topic
The Calendar you create will be called Target Resolution Dates. It will display each issue that is not yet closed, along with the person that the issue is assigned to and the project that it belongs to, on the day that corresponds to its target resolution date. Perform the following tasks:
Back to Topic List
Create a Calendar Page
| 1. |
Enter 1 for Page and click Go.
|
| 2. |
Under the Regions section, click the Create icon ().
|
| 3. |
Select Calendar for Region Type and click Next >.
|
| 4. |
Select SQL Calendar and click Next >.
|
| 5. |
Enter Target Resolution Dates for the Title and click Next >.
|
| 6. |
Copy and paste the text located in the target_resolution.sql file in your working directory into the SQL Query field and click Next >.
|
| 7. |
Select TARGET_RESOLUTION_DATE for the Date Column and DISP for the Display Column. Then click Create Region.
|
| 8. |
The Calendar Page was created successfully.
|
Back to Topic
Creating a Link to the Report Page
| 1. |
In the Regions section, select CAL to the left of Target Resolution Dates.
|
| 2. |
Click Column Link.
|
| 3. |
Enter 2 for Page, 2 for Clear Cache, P2_ISSUE_ID for Set these items and #ISSUE_ID# for With these values. Then click the Go To Top icon ( ).
|
| 4. |
Click the Region Definition tab.
|
| 5. |
Click Header and Footer.
|
| 6. |
Enter This excludes Closed issues. for the Region Header and click the Go To Top icon ().
|
| 7. |
Click Apply Changes.
|
| 8. |
The region has been updated.
|
Back to Topic
You are now ready to run the application to see the Calendar Application and the Report you created. Perform the following steps:
| 1. |
Click on the Run icon in the upper right corner of the application window.
|
| 2. |
Your Calendar is displayed. You can use the buttons to navigate to the Previous or Next Months. The Today button takes you to the current month. Click on any text displayed for an issue.
|
| 3. |
The View Issue page is displayed with the detail. You can click Cancel to return to the Calendar.
|
Back to Topic List
In this lesson, you learned how to:
Back to Topic List
|
Bookmark