5
Building Application Components

Estimated completion time: 50 minutes

This chapter shows you how to build Oracle Portal application components (forms, reports, charts, links, etc.).

To familiarize you with Oracle Portal's component building capabilities, this chapter leads you though the steps of creating components in a simple human resources application for a fictitious company named Healthy Living Wholesalers. You should complete the exercises in this chapter if you are responsible for creating application components within your organization.

This chapter includes the following sections:

Section
Section 5.1, "Before You Begin"
Section 5.2, "Creating an Application Component"
Section 5.3, "Controlling Access to an Application Component"
Section 5.4, "Lesson Summary"

5.1 Before You Begin

To complete the exercises in this chapter you must have Manage privileges on the Tutorial30 Application. Instructions for granting users these privileges are provided in Section A.6, "Granting a User Manage Privileges on the Tutorial Application".

You must start and log on to Oracle Portal. For instructions on how to do this, see "Starting and Logging on to Oracle Portal".

5.2 Creating an Application Component

Components are Web pages with content based on data stored in the database. Some components, such as reports, display data in a graphical format. Others, such as forms, provide interfaces that users can use to change data. You can create the following components in Oracle Portal:

Component	Description
Forms	Provide an interface to one or more database tables, views, or procedures.
Reports	Display the results of a SQL query in tabular format.
Charts	Display the results of a SQL query as a bar chart. Charts are based on at least two table or view columns: one that identifies the bars on the chart and one that calculates the size of the bars on the chart.
Calendars	Display the results of a SQL query in calendar format. At least one of the table columns in the query must contain values of the Date datatype.
Dynamic Pages	Display dynamic database data based on SQL or PL/SQL which is executed every time the page is requested.
Hierarchies	Display data from a self-referencing table or view (at least two columns in the table must share a recursive relationship). Hierarchies can contain up to three levels and display data such as employees in an organization chart, or the hierarchical relationship between menus in a Web site.
Menus	Display Web pages containing options that users can click to navigate to other components, URLs, or other menus.
Frame Drivers	Consist of Web pages divided into two frames. One frame (the driving frame) contains a SQL query that drives the contents of a second frame (the target frame).
Links	Provide hypertext links that enable users to navigate between components.
Lists of Values	Provide selectable parameters for fields in components. Lists of Values can be displayed in formats such as combo boxes or radio buttons.

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.

You are a member of the Healthy Living IT department, and have been asked to create several components for the Human Resources department that enable HR staff to maintain information about the company's employees.

5.2.1 Creating a form

The first component you have been asked to include in your application is a form for updating existing employees' personal information, such as their address and telephone number.

1. In the shortcut bar, click .

2. If you are not already on the Applications tab of the Navigator, click Applications.

3. If you are not already at the root level, click Applications in the Path.

4. Click TUTORIAL30_APPLICATION to drill down into the tutorial Human Resources application.

   Note: If you cannot see this application, notify your portal administrator.

5. Click Form.

   
   

   Note: If you cannot see the Form link, you probably do not have Manage privileges on TUTORIAL30_APPLICATION. Ask your portal administrator to grant you these privileges. Instructions for doing this are provided in Section A.6, "Granting a User Manage Privileges on the Tutorial Application".

6. You can build the following types of forms:
   • Form based on table or view: Displays fields and buttons for querying, inserting, updating, and deleting the data contained in a table or view.

   • Master-detail form: Displays a master row and multiple detail rows within a single HTML page. With this form, users can query, insert, update, and delete data in two tables or views.

   • Form based on procedure: Displays fields and buttons for entering arguments and passing them to a procedure.

   HR staff will use our form for updating data that is contained in a single table (the HLW_STAFF table), so click Form based on table or view.

7. In the Name field, enter <YourName>hrform.

   Note: Throughout this tutorial you add your user name to the beginning of names and display names to make sure that the object you are creating is unique, and is easy to find later.

8. In the Display Name field, enter <YourName> Update Employee Details.

9. In the Application list, make sure TUTORIAL30_APPLICATION is chosen.

   
   

10. Click Next.

11. In the Table or View field, enter <demo_schema>.hlw_staff, where demo_schema is the name of the schema where the Oracle Portal demos are installed.

    Note: If you are not sure of the name of the demo schema contact your portal administrator.

    
    
12. Click Next.

13. Make sure the Tabular radio button is selected.

    A tabular layout allows you to define the layout of the form using options in the wizard. A custom layout lets you write your own HTML code to control the layout of the form yourself.

    
    
14. At this point, you have entered all the required information. The wizard can apply defaults to the remaining properties. So click Finish and let's take a look at your new form.

    The Manage Component page displays, where you can perform any task on your component that you might need, such as editing, deleting, copying (from the Manage tab), and defining access (from the Access tab).

15. Click Run.

    
    

    The form doesn't look too bad, but it could use some improvement. For example, our form is for querying and updating data only, but there is still an Insert button displayed at the top and bottom of the form; there are far more fields included than we really need; and the field labels aren't particularly user-friendly. So let's edit the form and make some changes.

16. Close the browser window that contains the form.

17. In the Manage Component page, click Edit.

18. First, let's remove the buttons that we don't want on the form.

    In the left frame, click , then click OK to remove the following buttons from the form:

    • INSERT_TOP

    • DELETE_TOP

    • INSERT_BOTTOM

    • DELETE_BOTTOM

19. Not all the columns in the HLW_STAFF table are appropriate for our form. Some of them contain data that will not change (such as EMPLOYEE_GENDER and HIRE_DATE), and others will be updated using different components (for example, you would probably have a separate form for updating MANAGER_ID, DEPARTMENT_NO, REGION_ID, etc., for when an employee transfers to a different department). Let's remove the columns that we don't need:
    • JOB

    • EMPLOYEE_GENDER

    • EMAIL_ID

    • USER_ID

    • HIRE_DATE

    • ANNUAL _SALARY

    • COMMISSION

    • MANAGER_ID

    • DEPARTMENT_NO

    • REGION_ID

    • BUDGET

    • VACATION_DAYS

    • CCR_ID

    Note: This removes the fields based on the columns from the form; it does not delete the columns from the underlying table.

20. We are left with the following:

    
    

21. The wizard has defaulted the field labels based on the column names. In most cases we want to change these labels to something more meaningful, or format them to include spaces.

    In the left frame, click EMPLOYEE_ID.

22. In the Label field, enter Employee Id.

    
    

23. Change the rest of the field labels as follows:

    Old field label	New field label
    Name	Full Name
    Employeeaddress	Address
    Employeecity	City
    Employeezip	Zip Code
    Employeestate	State
    Employeephone	Phone No
    Maritalstatus	Marital Status

24. At the moment the State and Zip Code fields are in the wrong order: State should come before Zip Code. Let's fix that now.

    In the left frame, next to EMPLOYEE_STATE, click to move the State field above Zip Code.

25. Also, to conserve space, let's place the State and Zip Code fields on the same line.

    In the left frame, click EMPLOYEE_ZIP.

26. Clear the Begin On New Line check box.

    
    

    You can also change other field properties. For example, you might want to change the Input Width of the Address field to 20 to allow users more space to enter the address.

27. We can also brighten up the form by adding some color to it.

    In the left frame, click FORM. This lets you change the properties of the overall form, rather than the individual fields.

28. In the Box Background Color list, choose Blue, Light.

29. In the Box Border list, choose Thin Border.

    
    

30. We should probably add some explanatory text above the form, to explain to users how to use it.

    Click the (Form Text) tab.

31. In the Header Text field, enter the following:

    Enter the id of the employee that you want to update, then click Query. 
    Change the employee's details as necessary, then click Update.
    
    

    
    

    You can also use this tab to change the template used by your form. Templates are shared components that determine the appearance of the page on which a component is displayed, and help you to provide a consistent look across all your components. Oracle Portal is shipped with several templates to start you off, but you can create your own to match your particular organization's standards.

    Note: For information about creating templates, visit the Oracle Portal Online Help. In the Search field, enter template, and click Go.

32. For the moment, we have made all the changes we need to make to the form, so click OK.

33. In the Manage Component page, click Run to view the changes you made.

    
    

34. Close the browser window that contains the form.

    Of course, there are many more things you can do with Oracle Portal forms, for example, you can add JavaScript form- and field-level validation, or specify PL/SQL code to execute at different points during the execution of the form.

    Note: For more information about JavaScript in Oracle Portal, visit the Oracle Portal Online Help. In the Search field, enter javascript, and click Go.

35. Click Close to return to the Navigator.

5.2.2 Creating a report

The HR department has also requested that they be able to find out which employees are members of which departments. The best way to provide this information is by creating a report that lists employees by department. Let's create that report now.

1. Click Report.

2. You can build the following types of reports:
   • Query By Example (QBE) Reports: Allows you to create a report for querying, inserting, updating, and deleting data from a table or view.

   • Reports From Query Wizard: Provides a wizard that guides you through all the steps for creating a report, including creating the SQL query that selects the data displayed in the report.

   • Reports From SQL Query: Provides a wizard that guides you through the steps for creating a report, but allows you to write your own SQL query to select the data displayed in the report.

   Our report is for viewing data only, and requires a simple query, so click Reports From Query Wizard.

3. In the Name field, enter <YourName>hrreport.

4. In the Display Name field, enter <YourName> View Employees by Department.

5. In the Application list, make sure TUTORIAL30_APPLICATION is chosen.

   
   

6. Click Next.

7. Most of the data required for the report is contained in the HLW_STAFF table.

   In the Tables or Views field, enter <demo_schema>.hlw_staff.

8. Click Add.

9. However, in addition to the number of each department, we also want to include their names. This data is provided in the HLW_DEPARTMENTS table, so we also need to add this table to the report query.

   In the Tables or Views field, enter <demo_schema>.hlw_departments.

10. Click Add.

    
    

11. You could click Finish here and let the wizard provide default values for the remaining report properties, but we want to specify some more details first, so click Next.

12. Each department in the HLW_DEPARTMENTS table is uniquely identified by its own number (DEPARTMENT_NO). This is known as a primary key. Each employee in the HLW_STAFF table is associated with a department number, which maps to the numbers used in the HLW_DEPARTMENTS table. This is known as a foreign key. Because this relationship exists between these two tables, we can ensure that the correct department name is retrieved for each employee.

    For the report to work correctly, this relationship needs to be specified in the report query as a join condition.

    In the first left Column list, choose HLW_STAFF.DEPARTMENT_NO.

13. In the first right Column list, choose HLW_DEPARTMENTS.DEPARTMENT_NO.

    
    

14. Click Next.

15. Now we need to select which data to include in the report. All the HR department are interested in is the employee number, name, and job, and the department number and name.

    In the Available Columns list select the following columns, then click :

    • HLW_STAFF.EMPLOYEE_ID

    • HLW_STAFF.NAME

    • HLW_STAFF.JOB

    • HLW_STAFF.DEPARTMENT_NO

    • HLW_DEPARTMENTS.NAME

      
      

16. You can accept the defaults for the remaining properties, so click Finish to display the Manage Component page.

17. Click Run.

    
    

    As with the form, this first attempt at creating the report doesn't look too bad, but it could also use some improvement. For example, the department number and name do not need to be repeated for each row, and the order of the columns and rows should probably be rearranged slightly to make it easier for users to find the information they want. So let's edit the report and make some changes.

18. Close the browser window that contains the report.

19. In the Manage Component page, click Edit.

20. First, let's change the order of the columns in the report to display the department information first.

    Click the (Table or View Columns) tab.

21. Use the and buttons to reorder the columns as follows:

    
    

22. To remove duplication of repeating values, you need to specify break options for the report.

    Click the (Display Options) tab.

23. In the First Break Column list, choose HLW_STAFF.DEPARTMENT_NO.

24. In the Second Break Column list, choose HLW_DEPARTMENTS.NAME.

    
    

25. If you choose to break your report on certain columns, you also need to order the report by those columns.

    In the Order by list, choose HLW_STAFF.DEPARTMENT_NO.

26. In the first then by list, choose HLW_DEPARTMENTS.NAME.

27. To make it easier to find an employee within a department, let's also order the report by employee ID.

    In the second then by list, choose HLW_STAFF.EMPLOYEE_ID.

    
    
28. You can also use this tab to add some colors and borders to your report, as we did with the form, but for now we have made the changes we wanted to make, so click OK.

29. In the Manage Component page, click Run to view the changes you made.

    
    

30. Close the browser window that contains the report.

    Oracle Portal offers many more report features, for example, you can apply formatting conditions to change the appearance of information in your report based on the values of data, or add a customization form, which allows users to restrict the query results to meet their own specific requirements.

    Note: For more information about building reports, visit the Oracle Portal Online Help. In the Search field, enter report, and click Go.

31. Click Close to return to the Navigator.

5.2.3 Creating a chart

The final request the HR department has made, is for a component to show how much each department spends on salaries. This kind of information is perfect for displaying in a chart.

1. Click Chart.

2. You can build the following types of charts:
   • Charts From Query Wizard: Provides a wizard that guides you through all the steps for creating a chart, including creating the SQL query that selects the data displayed in the report.

   • Charts From SQL Query: Provides a wizard that guides you through the steps for creating a chart, but allows you to write your own SQL query to select the data displayed in the report.

   Our chart requires a simple query, so click Charts From Query Wizard.

3. In the Name field, enter <YourName>hrchart.

4. In the Display Name field, enter <YourName> View Salary per Department.

5. In the Application list, make sure TUTORIAL30_APPLICATION is chosen.

   
   

6. Click Next.

7. In the Tables/Views field, enter <demo_schema>.hlw_staff.

   
   

8. Click Next.

9. In the Label list, choose DEPARTMENT_NO.

   The values of the column specified in the Label field are displayed along the axis of the chart.

10. In the Value list, choose ANNUAL_SALARY.

    The values of the column specified in the Value field are used to calculate the size of the bars in the chart.

11. In the Group Function list, choose SUM.

    A 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 provides the total salary for each department.

    
    
12. Click Finish accept the defaults for the remaining properties and display the Manage Component page.

13. Click Run.

    
    

14. Close the browser window that contains the chart.

    If you wanted, you could now go back and edit the chart to make changes such as ordering the bars in the chart by the Label column rather than Value column, or add a customization form.

    Note: For more information about building charts, visit the Oracle Portal Online Help. In the Search field, enter chart, and click Go.

15. Click Close to return to the Navigator.

5.3 Controlling Access to an Application Component

Now that you have created your components, you need to allow other users to access and interact with them.

The salary information provided by the View Salary per Department chart you created is of a particularly sensitive nature. Only members of the HR department should be able to access that information, so let's make sure only members of the HLW_HR group can run the chart.

1. In the row for <YOURNAME>HRCHART, click the Grant Access action.

2. Clear the Inherit Privileges from Application check box.

   
   

3. Click Apply.

4. Let's give the HLW_HR group privileges to run the chart.

   In the Grantee field, enter HLW_HR.

5. In the list next to the Grantee field, make sure Execute is chosen.

   
   

6. Click Add.

   
   

7. Click Close to return to the Navigator.

5.4 Lesson Summary

Congratulations! You have created an application, and given users access to it. You now know how to:

• Create a form based on a table

• Create a report using the query wizard

• Create a chart using the query wizard

• Provide access to a component

For more advanced examples of what you can do with Oracle Portal application components, see the Oracle Portal Building Advanced Portals manual.

For any other information about building application components, see the Oracle Portal Online Help.