3
Building Components
Estimated completion time: 1 hour
This chapter shows you how to use Oracle WebDB to build components. Components are Web pages with content based on data stored in the Oracle database. Some components, such as reports, display data in a graphical format. Others, such as forms, provide interfaces that end users can use to change data. You can create the following components in WebDB:
There is a separate component build wizard for each type of component. Each wizard creates PL/SQL stored procedures. When executed, the stored procedures dynamically create the HTML and JavaScript code to display the component.
In this chapter, you'll learn how to build forms, reports, and charts using Oracle WebDB's component build wizards. You'll also learn how to build Lists of Values which you can use in your components and share with other developers.
This chapter includes the following sections:
3.1 Before You Begin
- To complete this chapter, you must be a WebDB Developer (that is, you must be a member of the WEBDB_DEVELOPER role) and have Build In privileges on your own schema. If you are not a WebDB Developer, you will not be able to access WebDB's component building features. If you do not have Build In privileges on your own schema, you will not be able to build components in that schema. You must also be a member of the RESOURCE role. If you need to be added to the WEBDB_DEVELOPER or RESOURCE roles or granted Build In privileges on your own schema, ask your DBA.
- You must have completed Section 2.2, "Building a Table" and Section 2.4, "Querying a Table" before starting this chapter.
- Your DBA must have created the HR_TUTORIAL role. Instructions for creating this role are provided in Appendix A, "Tutorial Setup Instructions".
3.2 Building a Form Based on a Table
In Section 2.2, "Building a Table" you created a table (TUTORIAL_TABLE) to contain information about a company's employees. End users in the company's Human Resources department need to be able to add new employees to this table. Your next development task is to build a form that the Human Resources staff can use to perform this part of their job.
- In the toolbar at the bottom of the page, click
.
The page displayed is divided into three panels. For this exercise you will use the Create a New Form panel.
You can build the following types of forms:
- Form based on stored procedures End users can select values to pass to a stored procedure, then execute the procedure. You used a form based on a stored procedure in Section 2.5, "Executing a Procedure".
- Form based on tables or views End users can query, insert, update, or delete data contained in a table or view. Forms based on tables or views offer more advanced features than simple Query by Example forms.
- Master-detail form End users can display a master table row and multiple detail rows within a single HTML page. Values in the master row determine which detail rows are displayed for updating, inserting, and deleting.
- Query by Example (QBE) form End users can query or insert values in a database table or view. A Query by Example form contains fields that correspond to the columns in the database table or view on which the form is built. You used a Query by Example form in Section 2.4, "Querying a Table".
In this exercise, you will build a form based a table (TUTORIAL_TABLE).
- Click Forms on Tables/Views.
- Click Create to display the Create Forms Wizard.
- In the Schema list, choose your own schema.
Note:
Your schema name is the same as your user name.
|
You can build components in the schema that owns the database objects on which the component is to be based, or in a schema that has been granted explicit database object privileges on the objects on which the component is to be based.
- In the Form Name field, type
TUTORIAL_FORM
.
- Click
to display the Tables or Views page.
WARNING:
Click if you need to go back to a previous page of the wizard. Do not click your browser's Back button.
|
- In the Tables/Views list, choose <YOUR_SCHEMA>.TUTORIAL_TABLE, where YOUR_SCHEMA is the name of your own schema.
- Click
to display the Form Layout Selection page.
- Click Structured.
Clicking Unstructured enables you to supply your own HTML code for the form.
- Click
to display the Formatting and Validation page.
- You can accept the default values on the remaining pages. Click
to create the form and display the Manage Component page.
Note:
is only displayed when you have reached a point in the wizard where you have provided all the information needed to create the component, and can use the default values in the remaining pages. You can change these default values later, if desired.
|
In the Manage Component page, you can choose from the following options:
- Edit Edit the form. For example, you can change the look and feel, add text, or add PL/SQL. You will use this option in Section 3.3, "Editing a Form".
- Run Run the current Production with VALID package version of the form. You will use this option in the next step of this exercise.
Note:
If the component contains errors (indicated by the words INVALID Package in the Version(s) Status link), you will not be able to run it. You will need to edit the component first to correct the errors.
|
- Parameters Test the behavior of the form when it is passed certain values.
Note:
For components with a parameter entry form, such as reports, choosing this option displays the parameter entry from. For more information see Section 3.5, "Building a Report".
|
- Privileges Grant and revoke privileges to execute the form to other WebDB users. You will use this option in Section 3.4, "Making a Component Available to End Users".
- Monitor View a chart of all requests for the form and the end users who made those requests.
- Manage Display additional options for managing the form. For example, Export enables you to move the form to a different database, and Drop enables you to delete the form or old versions of the form.
- Click Run. Your form is displayed on a Web page.
You can now use the form to query, insert, update, or delete data into TUTORIAL_TABLE.
Note:
If you use the form to query a table and more than one row matches the query criteria, Next and Previous buttons are displayed at the bottom of the form so that you can view the query results one row at a time.
|
- Use the following information to fill in the form.
Click Insert.
3.3 Editing a Form
As you can see, the form you created, while functional, is not particularly sophisticated. However, you can easily edit the form to improve the appearance and add more functionality.
3.3.1 Editing the basic appearance of forms
You can improve the usability of a form by making some changes to the basic appearance. For example, you can add color to make the form more visually appealing, or provide meaningful labels so the end user can easily see what each field in the form is for.
- Click your browser's Back button twice to return to the Manage Component page.
- Click Edit to display the Edit Form: Formatting and Validation page.
In this page, you can specify options that control the appearance of the form. You can also choose the columns you want to display as fields in the form and for each field you can specify a selectable List of Values, a default value, a display format, and choose a JavaScript application for validating the field in the form.
The page is divided into two. The left side of the page shows the columns that will be displayed as fields in the form. You can perform actions on the fields in the form, such as deleting them, changing their order on the form, or editing their options. The right side of the page is where you edit the options for the currently selected form or field.
3.3.1.1 Adding colors
Adding color is one way of improving the basic appearance of a form and improving the overall end user experience.
- Currently, FORM is selected in the left side of the page and the right side of the page is displaying the form-level options, so let's edit those.
- In the Box Background Color list, choose a color, for example Turquoise.
- In the Box Border list, choose Thin Border.
- Make sure that Yes is chosen in the Log Activity list. This means that every time an end user uses the form, details about that usage will be recorded in the activity log. You can view reports on the activity log from the Monitor option on the Manage Component page, or the WebDB Monitor menu.
3.3.1.2 Changing field labels
By default, WebDB uses the column name as the label for the field in the form. Let's change the labels in the form to something more descriptive.
- In the left frame, click EMPNO to edit the EMPNO field-level options.
- In the Display Name field, type
Employee Id
.
- In the left side of the page, click ENAME. Note that the fields in the right side of the page are updated with information for ENAME.
- In the Display Name field, type
Last Name
.
- Change the labels of the rest of the columns to something more descriptive.
You can change the font of labels to something that's easier to read.
- For each column:
- In the left side of the page, click the column name, for example EMPNO.
- In the Font Face list, choose Arial.
You can also change the color and size of labels.
Note:
The label for EMPNO is already colored red because EMPNO is a mandatory column.
The value of the label Font Size is relative to your browser's default text size. For example, if your browser's default text size is 12 points, changing the size of a label to +2 would increase the size of the label by two. For information about how to change your browser's default text size, refer to your browser documentation.
|
- Click Finish to save your changes and display the Manage Component page.
WebDB keeps a copy of previous versions of your components. If you want to go back to an earlier version, simply click the appropriate Version(s) Status link. Once you have finished editing a component and are happy with the final version, you can click Manage and use the Drop option to delete the old versions of the component.
- Click Run to view your changes.
3.3.2 Adding supporting text to a form
Good user interface design requires that any important information about a form should be displayed on the form itself. You should also provide help text to give end users additional guidance if they need it.
- Click your browser's Back button to return to the Manage Component page.
- Click Edit to display the Edit Form: Formatting and Validation page.
- Click the
tab to display the Text Options page. In this page, you can specify text that you want to appear at the top or bottom of the form. You can also add help text and specify a template.
- In the Title field, type
Employees
.
- In the Header Text field, type:
<H3>Use this form to add, edit or delete employee information.</H3>
Note:
You can use HTML tags, for example <H3> or <FONT>, to control the appearance of the text, if desired.
|
- In the Footer Text field, type:
<FONT COLOR="red">Red text indicates that the field is mandatory and you
must enter a value.</FONT>
- In the Help Text field, type:
<H3>To add a new employee:</H3>
Fill in the fields on the form and click <B>Insert</B>.
In this page, you can also specify the overall look and feel of the page on which the form is displayed by choosing a template. WebDB provides some templates to get you started, but you can also create your own with your company logo, color scheme, and text. For more information about templates, see the WebDB online help.
- In the Template list, choose a template, for example PUBLIC.TEMPLATE_3.
- Click Preview Template to see what the template looks like.
- Close the template preview window and click Finish to save your changes and display the Manage Component page.
- Click Run to view your changes.
- To view the help text you provided, click the question mark (?) at the top right of the form.
- Click your browser's Back button to return to the form.
3.3.3 Adding advanced PL/SQL code
You can use PL/SQL to add more functionality to your forms. For example, you might want to display a custom header, or create a temporary table.
- Click your browser's Back button to return to the Manage Component page.
- Click Edit to display the Edit Form: Formatting and Validation page.
- Click the
tab to display the Advanced PL/SQL code page. In this page, you can specify PL/SQL code that runs at different points during the execution of the HTML code that creates the form.
- In the before displaying the page field, type:
htp.print(sysdate);
This will print today's date at the top of the page on which the form is displayed.
- Click Finish to save your changes and display the Manage Component page.
- Click Run to view your changes. Today's date is displayed above the form.
3.4 Making a Component Available to End Users
To enable end users to run a component, you need to provide the following:
- The URL of the page that contains the component, or a way of accessing that URL
- Execute privileges on the component
Members of the Human Resources department need to be granted execute privileges on TUTORIAL_FORM before they can run it.
- Take a look at the URL of the page containing TUTORIAL_FORM.
This is the URL your end users need to run the form.
- End users can type the URL in the Address or Location field of their browser.
- You can provide a link to the URL on another page that end users already know how to access.
- You can add the component to a WebDB site. For information about adding components to WebDB sites, see Chapter 7, "Managing WebDB Site Content".
The URL consists of:
webserver.com:port/dad/your_schema.TUTORIAL_FORM.show
where:
Note:
The combination of the package that creates the component (for example, your_schema.TUTORIAL_FORM ) and the procedure that displays the component (for example, show ) is referred to as the run link. The run link of a component is displayed on the Manage Component page.
|
When end users attempt to display the page containing the component, they will be prompted for their user name and password. To be able to use the component, the end user must have execute privileges on the component.
- Click your browser's Back button to return to the Manage Component page.
- Click Privileges.
The page displayed is divided into two panels. In this exercise you will use the Grant Additional Privileges panel.
- Click
to display a list of the users and roles to which you can grant execute privileges.
- Click Next to page through the list if necessary and click HR_TUTORIAL (Role).
HR_TUTORIAL is displayed in the User / Role field.
- Click Grant Execute Privilege.
HR_TUTORIAL is displayed in the Existing Grants panel.
Members of the HR_TUTORIAL role are now able to run TUTORIAL_FORM.
Note:
If the form was part of a human resources application, you would need to grant the HR_TUTORIAL role execute privileges on all the components in the application.
|
To revoke execute privileges from a user or role, make sure the check box next to the user or role is checked and click Revoke.
3.5 Building a Report
The Human Resources department has also requested a report that they can use to view employee information.
- In the toolbar at the bottom of the page, click
.
The page displayed is divided into three panels. For this exercise you will use the Create a New Report panel.
- Make sure Report from Query Wizard is selected, and click Create to display the Create Reports Wizard.
The Create Reports Wizard produces a SQL query based on the information you provide as you step through the pages of the wizard. If you are familiar with SQL you can also create a report by writing the SQL query yourself.
- In the Schema list, choose your own schema.
- In the Report Name field, type
TUTORIAL_REPORT
.
- Click
to display the Tables and Views page.
- Scroll down the Tables/Views list, and choose <YOUR_SCHEMA>.TUTORIAL_TABLE, where YOUR_SCHEMA is the name of your own schema.
- You can accept the default values on the remaining pages. Click
to create the report and display the Manage Component page.
- Click Run to display your report.
3.6 Editing a Report
You can edit your report in the same way as you edited the form in Section 3.3, "Editing a Form". For example, you can change the report display options, add text, and add PL/SQL code. You can also use the report edit options to produce more sophisticated reports, such as summary reports, break reports, and parameterized reports. The following exercises show you how to create these advanced reports.
3.6.1 Adding summary information to a report
The Human Resources department would like the total salary to be displayed on the report, so they can easily see how much is being spent on salaries.
- Click your browser's Back button to return to the Manage Component page.
- Click Edit to display the Edit Reports: Table/View Columns page.
- Click the
tab to display the Columns Formatting page. In this page, you can specify column alignment, a display format, and whether to add a summary of a column's values.
- In the row for the TUTORIAL_TABLE.SAL column:
- Check Sum.
- In the Format mask field, type
$999,999.99
.
- Click Finish to save your changes and display the Manage Component page.
- Click Run to view your changes. The report now shows the total salary.
3.6.2 Adding a break to a report
The Human Resources department has requested that the report be grouped by the different departments, to make it easier to view employees by department. You can create a break report to do this.
- Click your browser's Back button to return to the Manage Component page.
- Click Edit to display the Edit Reports: Table/View Columns page.
- Click the
tab to display the Display Options page. In this page, you can specify options that control the appearance of the report.
- In the First Break Column list, choose TUTORIAL_TABLE.DEPTNO.
For best results, you should also order the report by the columns you have chosen to break on.
- At the bottom of the page, in the first Order by list, choose TUTORIAL_TABLE.DEPTNO.
- Click Finish to save your changes and display the Manage Component page.
- Click Run to view your changes. The report is now grouped by department. The total salary is shown for each group as well as for the whole report.
3.6.3 Adding parameters to a report
The Human Resources department has submitted another enhancement request for the report. They want to be able to display the report data for a single specified department. You need to create a parameter that they can use to specify the department.
- Click your browser's Back button to return to the Manage Component page.
- Click Edit to display the Edit Reports: Table/View Columns page.
- Click the
tab to display the Parameter Entry Form Display Options page. In this page, you can specify parameters that will be used to specify the data displayed in the report.
- In the first Column Name list, choose TUTORIAL_TABLE.DEPTNO.
- In the Prompt field, type
Department
.
- Click Finish to save your changes and display the Manage Component page.
- Click Parameters to display the parameter entry form for the report.
The parameter entry form prompts the end user for values to use to display the report. The Department parameter you created is the first field on the parameter entry form. Note that end users can also change the break column and order by settings for the report.
- In the Department fields, choose = in the list, and type
10
in the field.
- Click Run Report. Now, your report only lists employees who work in Department 10.
3.7 Building a Chart
The Human Resources department has also requested a chart that shows the total salary for each department.
- Click your browser's Back button twice to return to the Manage Component page.
- In the toolbar at the bottom of the page, click
.
The page displayed is divided into three panels. For this exercise you will use the Create a New Chart panel.
- Make sure Chart from Query Wizard is selected and click Create to display the Create Charts Wizard.
The Create Charts Wizard produces a SQL query based on the information you provide as you step through the pages of the wizard. If you are familiar with SQL you can also create a chart by writing the SQL query yourself.
- In the Schema list, choose your own schema.
- In the Chart Name field, type
TUTORIAL_CHART
.
- Click
to display the Tables or Views page.
- In the Tables/Views list, choose <YOUR_SCHEMA>.TUTORIAL_TABLE, where YOUR_SCHEMA is the name of your own schema.
- Click
to display the Table/View Columns page.
- In the Label list, choose DEPTNO.
The values of the Label column are displayed along the axis of the chart.
- In the Value list, choose SAL.
The values of the Value column are used to calculate the size of the bars in the chart.
Note:
The default for Value is 1. Choosing a value of 1 is useful if you also choose a group function. For example, you can choose the JOB column from TUTORIAL_TABLE as the label, 1 as the value, and COUNT as the group function. This creates a chart that displays the number of employees in each job classification.
|
- In the Group Function list, choose SUM.
The group function groups the Value column values by unique Label column values, and performs an operation on each group of values. In this case, the group function sums the salaries of each department.
- In the Order By list, choose ORDER BY LABEL.
- You can accept the default values on the remaining pages. Click
to create the chart and display the Manage Component page.
- Click Run to display your chart.
3.8 Building a Shared Component
A shared component is a component element such as a link, List of Values, or image that can be used by multiple developers when creating forms, reports, and other WebDB components. You can create the following shared components in WebDB:
Because these component elements are shared, they only need to be created once and will look and behave consistently across components. WebDB Developers can create shared components in a schema on which they have Build In privileges. To use a shared component, developers must have Build In privileges in the schema that owns the shared component.
In the following exercises you'll learn how to create Lists of Values and use them in components. In Chapter 4, "Building Applications by Linking Components" you'll learn how to create and use links.
3.8.1 Building a List of Values (LOV)
End users can use Lists of Values (LOV) to select from a list of possible values rather than type values in fields on forms and component parameter entry forms. There are two types of LOV:
- A Dynamic LOV is based on a SQL query that you code. This type of LOV is dynamically generated from a SQL query of a table column and contains all values in the column.
- A Static LOV contains values that you explicitly specify when you create it.
The DEPTNO column in TUTORIAL_TABLE can have a value of 10, 20, or 30. Let's create an LOV so that when the Human Resources staff need to enter a department number they can choose from a list.
- Click your browser's Back button to return to the Manage Component page.
- In the toolbar at the bottom of the page, click
.
- Click Lists of Values (LOV).
The page displayed is divided into three panels. For this exercise you will use the Create a List of Values panel.
- Click Static, then click Create LOV to display the Create Static List of Values page.
- In the Owning Schema list, choose your own schema.
- In the Name field, type
TUTORIAL_LOV
.
- Complete the Display Value, Return Value, and Display Order fields using the information in the following table.
- Display Value specifies what is displayed in the list.
- Return Value is the actual value that is returned to the component when an item in the LOV is chosen.
- Display Order specifies the order the items are displayed in the LOV. If you do not specify a display order, the items are displayed in ascending alphabetic or numeric order.
- Click Add LOV to create the LOV and display the Manage Lists of Values page.
3.8.2 Testing a List of Values
Once you have created an LOV, you can test it to check how it will appear in a component.
- Scroll down to the Select a Recently Edited List of Values panel.
If TUTORIAL_LOV is not listed:
- Scroll up to the Find an Existing List of Values panel.
- In the LOV Name Contains field, type
TUTORIAL_LOV
.
- Click Find LOV.
- Click Combo to test what TUTORIAL_LOV will look like as a combo box.
- Click your browser's Back button.
- Click Radio to test what the List of Values will look like as a group of radio buttons.
3.8.3 Using a List of Values in a component
In the previous exercise you created an LOV. In this exercise you will use the LOV in the form and report that you created in Section 3.2, "Building a Form Based on a Table" and Section 3.5, "Building a Report".
3.8.3.1 Using a List of Values in a form
Let's add the LOV to the DEPTNO field in TUTORIAL_FORM.
- In the toolbar at the bottom of the page, click
to display the Component Building page.
The Component Building page offers another way of finding components. Here you can search across different types of component.
- In the Schema list, choose your own schema.
- In the Name Contains field, type
TUTORIAL
.
- Click Find.
WebDB searches for all components containing the text "TUTORIAL" and lists them at the bottom of the Component Building page. You can use the check boxes to restrict the search further by specifying which type of components to search for.
- Scroll down the Component Building page and click TUTORIAL_FORM to display the Manage Component page for the form.
- Click Edit to display the Edit Form: Formatting and Validation page.
- In the left side of the page, click DEPTNO.
- In the Display As list, choose ComboBox.
- Click
next to the LOV field to list the available LOVs. You can only use LOVs stored in schemas on which you have Build In privileges.
- Click Next to page through the LOVs if necessary and click <YOUR_SCHEMA>.TUTORIAL_LOV, where YOUR_SCHEMA is the name of your own schema.
- Click Finish to save your changes and return to the Manage Component page.
- Click Run to view your changes. The Department Id field is now a combo box where the end user can select a department by name, rather than having to know which number to type.
3.8.3.2 Using a List of Values in a report parameter entry form
Let's add the LOV to the Department parameter field in the TUTORIAL_REPORT parameter entry form.
- Click your browser's Back button to display the Manage Component page.
- In the toolbar at the bottom of the page, click
to display the Component Building page.
- In the Schema list, choose your own schema.
- In the Name Contains field, type
TUTORIAL
.
- This time, let's just search for reports. Uncheck all the check boxes except for Reports and click Find.
- Scroll down the Component Building page and click TUTORIAL_REPORT to display the Manage Component page for the report.
- Click Edit to display the Edit Reports: Table/View Columns page.
- Click the
tab to display the Parameter Entry Form Display Options page.
- In the row for the Department parameter, click
next to the LOV field to list the available LOVs.
- Click Next to page through the LOVs if necessary and click <YOUR_SCHEMA>.TUTORIAL_LOV, where YOUR_SCHEMA is the name of your own schema.
- In the Display LOV As list, choose Combo box.
- Click Finish to save your changes and return to the Manage Component page.
- Click Parameters to display the parameter entry form for the report.
The LOV looks the same as the LOV in TUTORIAL_FORM: the same values are listed in the same order. This improves the usability and consistency of an application, because the end user does not have to learn a different interface before being able to use the report. If you did not use a shared component in this situation, the LOV might be ordered differently or use different names for the departments, which could confuse the end user.
- In the Department fields, choose = from the first list and choose Sales (30) from the second.
- Click Run Report to show employees in the Sales department (Department 30).
- Click your browser's Back button twice to return to the Manage Component page.
3.9 What's Next?
In this chapter, you learned how to:
- Create and edit a form
- Create and edit a report
- Create a chart
- Provide access to a component
- Create and use a List of Values (LOV)
In Chapter 4, "Building Applications by Linking Components" you will create menus and links to build applications from the individual components you have created.