A Creating Portlets with the Portlet Builder

A.1.1.1 Creating a Schema

To create a schema in OracleAS Portal:

1. Log in to OracleAS Portal.

2. Click the Navigator link at the top of the page.

3. In the Navigator, click the Database Objects tab to bring it forward.

4. Click the Schema link next to Create New… (Figure A-1)

   Figure A-1 The Schema Link on the Database Objects Tab in the Portal Navigator

   
   Description of "Figure A-1 The Schema Link on the Database Objects Tab in the Portal Navigator"
   
   

5. On the Create Schema page (Figure A-2), enter a name to identify the schema in the Schema field.

   The name must be unique within the portal. Blank and special characters are not allowed in the name. Type an underscore character to add a space in a name. For example, you can name a schema PORTLETS_SCHEMA, but not PORTLETS SCHEMA. Additionally, you cannot name a schema PORTLETS*SCHEMA nor PORTLETS%SCHEMA.

   Figure A-2 The Create Schema Page

   
   Description of "Figure A-2 The Create Schema Page"
   
   

6. In the Password field, enter the password you will assign to the schema.

   Asterisks appear for each character you enter. Users will enter this password when logging into the Oracle database as this schema.

7. Reenter the password in the Confirm Password field.

8. Select a default tablespace from the Default Tablespace list.

   This will be used for storing any database objects created by the schema. We recommend that you select a tablespace created specifically for storing locally build portlet applications. If necessary, talk about this with your database administrator.

9. Select a temporary tablespace from the Temporary Tablespace list.

   This will be used by the schema to create temporary storage for operations such as sorting table rows.

10. Select an Oracle resource profile for the new schema from the ORACLE Profile list.

    The default profile is DEFAULT. Feel free to use this. If you plan to create a new profile, you must use Oracle SQL commands to do so. Refer to the Oracle database documentation for more information. You'll find this on the Oracle Technology Network at http://www.oracle.com/technology/index.html.

11. Select the Use this Schema for Portal Users check box to add the schema you are creating to the list of database schemas to which portal users can map for administrative purposes.

    Every OracleAS Portal user must be associated with a database schema. By default, the name of this schema is <portal>_public, where <portal> is the name of the schema in which OracleAS Portal is installed. If you select this check box, a portal user can be mapped instead to the schema you are creating here.

12. Click Create to save your changes and return to the Portal Navigator.

    Alternatively, click the link to your new schema that now appears at the top of the page to begin editing the new schema's properties. See Section A.1.1.2, "Granting and Revoking Privileges on Database Objects" and Section A.1.1.3, "Enrolling the Schema in One or More Roles" for more information.

A.1.1.2 Granting and Revoking Privileges on Database Objects

You may need to increase or limit the access your users will have on some database objects associated with this schema. Once they are able to log in to this schema, users' access privileges are influenced by the roles of which this schema is a member (see Section A.1.1.3, "Enrolling the Schema in One or More Roles"). You can use grants to select specific database objects and specify which of those access privileges should be further limited or increased.

For example, imagine that you have a BASIC_USER role. This role allows users to SELECT from all tables in the database. You may want to restrict selections on some tables. You can use grants to select one or more database objects (such as specific tables, views, and the like) and remove the SELECT privilege. Conversely, if a role does not include all the access privileges you want a schema to have on one or more database objects, you can use grants to increase access privileges.

This section describes how to further control privileges on objects associated with your schema.

To grant privileges to database objects associated with a schema:

1. Log in to OracleAS Portal.

2. Click the Navigator link at the top of the page.

3. Click the Database Objects tab to bring it forward.

4. On the Database Objects tab (Figure A-3), go to the Name column to locate the schema you will work with, and click the Edit link that is next to it.

   Figure A-3 The Edit Link Next to a Schema in the Portal Navigator

   
   Description of "Figure A-3 The Edit Link Next to a Schema in the Portal Navigator"
   
   

5. On the resulting page, click the Grants tab (Figure A-4).

   Figure A-4 The Grants Tab

   
   Description of "Figure A-4 The Grants Tab"
   
   

6. In the Object field, enter the name of a database object on which you want to control privileges.

   Click the Browse icon to select from a list of objects. Prefix the name of the object with the schema that owns it. For example, add SCOTT to EMP (SCOTT.EMP) to indicate the EMP table in the SCOTT schema.

7. Click Add to add the object to the list at the bottom of the page.

   Objects are grouped by object type. That is, tables are listed with tables, views with views, and so on.

8. Select or clear check boxes next to the object to increase or limit the access privileges on the object.

   Figure A-5 The Check Boxes Next to a Table Object

   
   Description of "Figure A-5 The Check Boxes Next to a Table Object"
   
   

9. Click Apply to save your changes, or click OK to save your changes and return to the Portal Navigator.

A.1.1.3 Enrolling the Schema in One or More Roles

You will want to enroll your schema in one or more roles to provide the desired level of overall database access privileges. If you think of a schema as a super-user, created from a population of users who have password access to the schema, you enroll a schema in one or more roles to provide access privileges to the database to all users who in turn have login access to the schema.

The access privileges provided through the schema's membership in one or more roles can be overridden on specific database objects through grants (see "Granting and Revoking Privileges on Database Objects").

To enroll a schema in one or more roles:

1. Log in to OracleAS Portal.

2. Click the Navigator link at the top of the page.

3. Click the Database Objects tab to bring it forward.

4. On the Database Objects tab, go to the Name column to locate the schema you will work with and click the Edit link that is next to it.

5. On the resulting page, click the Roles tab to bring it forward.

   Figure A-6 The Roles Tab

   
   Description of "Figure A-6 The Roles Tab"
   
   

   By default, the schema is enrolled in the CONNECT role, and may be enrolled in others.

6. Click the Browse icon next to the Role field.

7. Select a role to which you will enroll the schema.

8. Click the Add button next to the Role field to add the selected role to the Is Member Of list.

   Continue selecting roles and clicking the Add button until you have enrolled the schema in all desired roles.

9. Click Apply to save your changes, or click OK to save your changes and return to the Portal Navigator.

A.1.4.1 Building Portlets Declaratively

To use a wizard to build a portlet:

1. Log in to OracleAS Portal.

2. Click the Navigator link at the top of the page.

3. Click the Providers tab to bring it forward.

4. Click the Locally Built Providers link.

   Figure A-10 The Locally Built Providers Link in the Portal Navigator

   
   Description of "Figure A-10 The Locally Built Providers Link in the Portal Navigator"
   
   

5. Click the name of the OracleAS Portal database provider you want to host the new portlet.

   The Name column displays the names of all the providers on which you have privileges.

6. Next to Create New…, click the link for the type of portlet that you want to build, for example, Chart or Form (Figure A-11).

   Figure A-11 The Form Link Next to Create New in the Portal Navigator

   
   Description of "Figure A-11 The Form Link Next to Create New in the Portal Navigator"
   
   

   See Table A-1 for a list and description of the types of portlets you can build.

   Depending on the type of portlet you are building, OracleAS Portal displays either the first step of the portlet build wizard or a menu of additional choices. If you click Form, for example, you have the option of creating a form based on a table or view, a master-detail form, or a form based on a procedure (Figure A-12).

   Figure A-12 Choices Available for Creating a Form in the Portlet Builder

   
   Description of "Figure A-12 Choices Available for Creating a Form in the Portlet Builder"
   
   

7. When the first step of the portlet build wizard displays, follow the instructions shown on each step.

   A progress indicator displays how many wizard steps are left to complete. The Finish button becomes available when you have provided the wizard with sufficient information to build the portlet. The fields you don't complete will populate with default values or will remain null.

   If you are unsure of how to complete the fields on a particular step of the wizard, click the online Help icon in OracleAS Portal for more information.

The next sections guide you through the specific steps required to build a particular type of portlet. For more information, see:

• Building Forms Declaratively

• Building Reports Declaratively

• Building Forms and Reports against interMedia Rich Content

• Building Charts Declaratively

• Building Lists of Values Declaratively

A.1.4.2 Building Forms Declaratively

Forms provide a means of inserting, updating, and deleting content from your database through a user-friendly interface. You control form layout through options available in the Forms build wizard. Wizard options enable you to control the look and feel of the overall form as well as the form's individual entry fields and buttons.

You can perform JavaScript validation on user-specified values entered in any text field on the form. Additionally, you can add PL/SQL event handlers that run when a user clicks a button on the form. After successful submission of form content, you can specify a PL/SQL block or procedure that will execute.

The portlet-building wizards in OracleAS Portal provide three construction methods for building forms:

• Forms based on tables or views

• Master-detail forms

• Forms based on a procedure

All of these forms are described in Table A-1. This section describes how to create a form based on a table or view (Figure A-13).

Figure A-13 Sample Form Based on a Table

Description of "Figure A-13 Sample Form Based on a Table"

To create a form based on a table or view:

1. Follow the instructions detailed in "Building Portlets Declaratively".

   Return to this section once you complete step 6.

2. On the Forms page, click Form based on table or view.

   Figure A-14 The Form Based on Table or View Link in the Portlet Builder

   
   Description of "Figure A-14 The Form Based on Table or View Link in the Portlet Builder"
   
   

3. In the Name field, enter an internal name for the form (Figure A-15).

   Figure A-15 Step One of the Form Wizard

   
   Description of "Figure A-15 Step One of the Form Wizard"
   
   

   The internal name is not published to users.

4. In the Display Name field, enter the name by which users will identify this form (Figure A-15).

   The display name is used at runtime. It displays as the title of the browser window as well as in the banner of the form. When the form is published as a portlet, the display name is used as the title of the portlet and in the List of Available Portlets in the Portlet Repository.

5. In the Description field, enter a description of this form (Figure A-15).

   The description displays below the portlet in the Portlet Repository. It can be a summary of the portlet's purpose, a classification of its type, or any other descriptive information. It may be useful within your organization to have a standard approach to what is included in the description.

6. From the list of Portal DB Providers, select the provider that will own this form (Figure A-15).

   We recommend that you use a provider specifically created for owning, or hosting, your declaratively-built portlets. This list displays only those providers in which you have been granted privileges to build portlets.

7. Click Next.

8. Click the Browse icon next to the Table or View field, and choose a table or view on which to base your form (Figure A-16).

   You can use a constant in this field (for example, #APP_SCHEMA#.EMP, #PORTAL_SCHEMA#.DEPT) in lieu of a fixed value. For more information, see "Referencing the OracleAS Portal Schema".

   Figure A-16 Selecting a Table or View

   
   Description of "Figure A-16 Selecting a Table or View"
   
   

   The tables and views that you can choose in this step are those that are accessible from the Portal DB Provider you selected in the previous step. You can also enter the table name directly into the field.

   
   

   Note: If you are using the Example Application (based on the PORTAL_DEMO schema) to build a form, the default table on which to base the form is SCOTT.EMP. As the SCOTT table does not have PUBLIC EXEC privileges, the administrator must manually grant public access to the SCOTT schema to create a form based on the default table. If this is not done, and you choose the default table, the following error is generated: Error: This table does not exist or you do not have the require privileges (WWV-13020).

   
   

9. Click Next.

10. Select a form layout.

    Choose between Tabular or Custom. Tabular layouts are created automatically. Custom layouts are based on HTML code that you supply in a later step.

11. Click Next.

12. Set Formatting and Validation Options (Figure A-17):

    Figure A-17 Form Formatting and Validation Options

    
    Description of "Figure A-17 Form Formatting and Validation Options"
    
    

    1. Click Form in the left column to set up the formatting and validation options for the entire form (Figure A-17).

       The formatting and validation options that appear in the right column relate to the object you have selected in the left column.

       Table A-2 lists and describes form-level formatting and validation options.

       Table A-2 Form-Level Formatting and Validation Options

       Option	Description
       Form Background Color	Choose the background color of the form.
       Form Background Image	Choose an image that will appear in the background of the form.
       Form Border	Choose a style for the border around the form background.
       Log Activity Check Box	Select to record activity in the OracleAS Portal log. Log information includes performance statistics as well as the names of users who request the form.
       Show Timing Check Box	Select to display the time elapsed, starting from when the server receives the request to generate HTML for the form. Timing displays at the bottom of the form.
       Order by	Choose the column by which you will order the data returned by the SELECT statement. Select the percent sign (%) if you do not wish to specify a column for ordering.
       Then by	Choose the column(s) for subsequent ordering of the data returned by the SELECT statement. Select the percent sign (%) if you do not wish to specify a column for ordering.
       Ascending	Choose Ascending (from a to z/0 to 9) or Descending (from z to a/9 to 0), depending on how you want the column values ordered.
       On Successful Submission of a Form Execute this PL/SQL	Enter optional PL/SQL code that will execute after a user clicks a button on the form. The button must cause an operation, such as INSERT, to be performed on the table or view on which the form is based. For example, you might enter code that causes a message to display to the user when a table row is successfully updated.

       
       

    2. Select a TOP_SECTION item in the left column (that is, INSERT_TOP, UPDATE_TOP, and the like) to set formatting and validation options in the right column for INSERT, UPDATE, and other like items that will display at the top of the form (Figure A-18).

       Figure A-18 Button Formatting and Validation Options

       
       Description of "Figure A-18 Button Formatting and Validation Options"
       
       

       By default, insert, update, delete, query, and reset buttons appear at the top and bottom of each form. To prevent these buttons from displaying, click the Delete icon that appears next to the item in the left column.

       Table A-3 lists and describes the formatting and validation options for buttons.

       Table A-3 Formatting and Validation Options for Buttons

       Option	Description
       Item Type	Select the method for displaying the selected item type on your form. Note that the default for items, such as INSERT_TOP, is button. For INSERT, UPDATE, DELETE, QUERY, and RESET, you will likely most often want to use the default. If you choose another item type, you will need to add code to invoke the item's functionality (for example, an insert) through a JavaScript or PL/SQL Event Handler. Item types include: Blank: Inserts a line break in between fields. Button: Inserts a button. INSERT, UPDATE, DELETE, QUERY, and RESET items require the selection of an event handler further down on this page. CheckBox: For INSERT, UPDATE, and so on, you must write a JavaScript handler for this to be meaningful. For columns, the underlying column must be at least varchar2(5) to accommodate entries of TRUE (selected) and FALSE (cleared). ComboBox: Inserts a pop-list of values with an extra blank space that allows for manual entry of a value. You must already have defined a list of values to make this selection. You can enter a constant in the Default Value field, and define the default value as a constant in the Default Value Type field. For more information about the constants you can use, see "Referencing the OracleAS Portal Schema". File Upload (Binary): Used to insert a BLOB column. File Upload (interMedia): Select this type for interMedia rich content, such as images, audio clips, and video clips. The table must have columns of the type ORDIMAGE, ORDAUDIO, or ORDVIDEO. For more information, see "Building Forms and Reports against interMedia Rich Content". Hidden: Creates a hidden form field. Horizontal Rule: Draws a horizontal line, similar to <hr>. Image: Used to show an image. Label Only: This shows only a prompt. Password: This will have the behavior of a password field, where input will be hidden. TextArea: Provides a large text area for the entry of multiple lines of text. TextBox: Provides a text box.
       Display Options	Label: Enter text that will display next to the button.
       Layout Options	This section displays only if you selected Tabular in the Form Layout step. If you selected Custom, you can specify your own sophisticated layout in the next step using HTML code. Begin on New Line: Select this option to display the item on a new line on the form. Leaving this cleared will display the item on the same line as the previous item or column field.
       JavaScript Event Handlers	Choose an event and enter the JavaScript for the action you want to occur when that event happens. Refer to your JavaScript documentation for descriptions of the events on this list.
       PL/SQL Button Event Handler	Choose a button event and enter the PL/SQL code for the action you want to occur when the event happens. For advanced personalization, choose Custom from the drop-down list, and enter the PL/SQL code for the custom event.

       
       

    3. Select a column name in the left column to set formatting and validation options in the right column for table or view columns that will display in the form (Figure A-19).

       Figure A-19 Column Formatting and Validation Options

       
       Description of "Figure A-19 Column Formatting and Validation Options"
       
       

       To prevent the display of a column, click the Delete icon next to it.

       Table A-4 lists and describes the formatting and validation options available for table or view columns.

       Table A-4 Formatting and Validation Options for Table or View Columns

       Option	Description
       Item Type	Defines how the column you've selected on the left will display on your form. Options are listed and described in Table A-3.
       Display Options	Label: Enter text to label this column on the form. Text will be display-only and will appear next to the field that represents this table or view column on the form. For example, you can add a label next to the field for the EMPNO column called Employee Identification Number. Link: Specify a link to another OracleAS Portal portlet or URL. If you specify this option, the Label appears on the form as a hypertext link. If you want to link the form to another URL, type the URL location in the Link text box. If you want to link to an OracleAS Portal portlet, you can type the name of the package containing the portlet, for example: SCHEMA.portlet.SHOW SCHEMA is the name of the schema that owns the portlet; portlet is the portlet name; and SHOW is the procedure used to display the portlet. You can also specify SHOW_PARMS to display the personalization form for the portlet. Font Face, Color, Size: Specify the font characteristics for displaying text associated with the selected column on the form. Input Width: Enter the character width of the field associated with the selected column. Input Max Length: Enter the maximum length of the data that can be entered into the form field associated with the selected column.
       Validation Options	Mandatory: Select to require that the user specify a value in the field associated with the selected column before submitting the form. Updatable: Select to enable the user to update the column. Leaving this blank prevents updates. This feature is useful when you want to allow users to update some table columns in the form, but only view others. For example, by clearing Updatable for employee names and ID numbers, and selecting Updatable for the employee's department, you can create a form that enables users to update an employee's department (for example, when the employee is transferred), but disables users from updating the employee name and ID number. When users display views, updatable columns display in black, non-updatable columns display in blue, and mandatory columns display in red. Insertable: Select to store the value of the column in the database when the user creates a new record. Leaving this blank prevents the value from being stored in the database (that is, it removes the column from the INSERT statement). Default Value: Enter a default value for the field associated with the selected column. Users can accept this value or specify their own. Specify a constant, function, expression, or SQL query. Default Value Type: Specify a type for the default value entered in the previous field (Default Value). Format Mask: Enter an Oracle display format for columns containing numeric and date data types. For example, you could enter DD/MM/YYYY to display dates according to this pattern; or you could enter 999,999,999.99 to place commas and decimals according to this pattern. Note: Refer to the Oracle database documentation (http://www.oracle.com/technology/index.html) for information about date and numeric formatting options. Field Level Validation: Choose a JavaScript validation routine that verifies whether the user enters a valid value in the field. For example, you could choose a JavaScript routine called IsNumber that verifies that a number has been typed in a SALARY field. Field validation providers are implemented in JavaScript and run when the OnChange condition occurs; for example, when the user presses the Return key after entering a value in the field. Form Level Validation: Choose a JavaScript validation provider that verifies whether the user enters a valid value in the field. Form validation providers run when the user submits the information, for example, after the user clicks the Insert button on the form.
       Layout Options	This section displays only if you selected Tabular in the Form Layout step. If you selected Custom, you can specify your own sophisticated layout in the next step using HTML code. Begin on New Line: Select to create a line break before the field associated with the selected column. Leaving this cleared will display the column field on the same line as the previous column field. Row Span: Enter the number of HTML cells that can be used to display the field vertically on the browser page. Col Span: Enter the number of HTML cells that can be used to display the field horizontally on the browser page.
       JavaScript Event Handlers	Choose an event and enter the JavaScript for the action you want to occur when the event takes place. Refer to your JavaScript documentation for descriptions of the events in the list.

       
       

    4. Select a BOTTOM_SECTION item in the left column (that is, PREVIOUS, NEXT, INSERT_BOTTOM, UPDATE_BOTTOM, and the like) to set formatting and validation options in the right column for PREVIOUS, NEXT, INSERT, UPDATE, and other like items that will display at the bottom of the form.

       See step b of this task for more information.

13. Click Next.

14. If you selected a Custom layout:

    Enter the HTML code that will control the layout of your form. The text boxes on this page contain sample HTML code that creates a table. Using this model, each column you selected in the Tables or Views step of this wizard is formatted to display in a table row on the form.

    Column labels are specified by the suffix .LABEL in the sample code. Column values are specified by the suffix .ITEM in the sample code.

    You can update the sample code with any HTML of your own, provided you do not change the column values (.ITEM suffix) or labels (.LABEL suffix). You can, however, delete column values from this code.

    If you selected a Tabular layout:

    Enter descriptive text that you want to appear on the top or bottom of the form (Figure A-20). You can add a form title and help text for the form. You can also choose a template that controls the look and feel of the page on which the form appears.

    Figure A-20 Entering Header, Footer, and Other Text for a Form

    
    Description of "Figure A-20 Entering Header, Footer, and Other Text for a Form"
    
    

    Table A-5 lists and describes the options available on this page.

    Table A-5 Form Text Options on Tabular Format Forms

    Option	Description
    Template	Choose a template to set the look and feel of form elements such as background colors and images and the image that appears in the upper left corner of the page. Templates are used only when your form is displayed in full-page view, and not when it is displayed as a portlet. When a form is displayed as a portlet, the host page's style controls the look and feel.
    Preview Template	Click to view the appearance of the template currently selected in the Template drop-down list.
    Display Name	Edit the display name of the form. You can specify HTML in this field.
    Header Text	Enter any introductory text that you want to display at the top of the form, just below the title, but above any buttons. You can specify HTML in this field.
    Footer Text	Enter any text that you want to display at the bottom of the form. You can specify HTML in this field.
    Help Text	Enter any text that you want to display in a help page for the form. OracleAS Portal automatically adds a help button to the form. Users can click this button to link to a page displaying the help text that you enter here. You can specify HTML in this field.

    
    

15. Click Next.

16. Optionally, enter the PL/SQL that will run at different points during the execution of the HTML code that creates this form (Figure A-21).

    In the PL/SQL you enter into these fields, you can use constants for the OracleAS Portal schema, the application schema, and the application name. For more information, see "Referencing the OracleAS Portal Schema".

    Figure A-21 Optional Fields for Entering Additional PL/SQL Code

    
    Description of "Figure A-21 Optional Fields for Entering Additional PL/SQL Code"
    
    

    Table A-6 lists and describes the options on this page.

    Table A-6 Additional PL/SQL Code Options

    Option	Description
    … before displaying the page.	Enter a PL/SQL procedure that will execute before the HTML for the form is generated (that is, before the <FORM> tag is generated). In spite of the name of this field, the PL/SQL procedure actually executes after the page itself is displayed.
    … before displaying the form.	Enter a PL/SQL procedure that will execute after the <FORM> tag but before displaying any form elements.
    … after displaying the form.	Enter a PL/SQL procedure that will execute after all form elements are displayed but before the ending </FORM> tag.
    … after displaying the page.	Enter a PL/SQL procedure that will execute after the ending </FORM> tag.
    … before processing the form.	Enter a PL/SQL procedure that will execute before the form is processed.
    … after processing the form.	Enter a PL/SQL procedure that will execute after the form is processed.

    
    
    
    

    Note: If you want to display output from a PL/SQL procedure, you must enter the PL/SQL in one of these four fields: before displaying the page before displaying the form after displaying the form after displaying the page You cannot display output from a PL/SQL procedure in the before/after processing the form fields; the output will not display. Additionally, if you want to display output from your PL/SQL procedure, you must specify the schema name with the procedure. This is required even if the form and the procedure are in the same schema.

    
    

17. Click Finish to save your changes.

    Once you click Finish, Portal jumps to a summary page from which you can manage your form (Figure A-22). These tasks are discussed in later sections in this appendix.

    Figure A-22 Form Summary Page

    
    Description of "Figure A-22 Form Summary Page"
    
    

18. Click Close to return to the Portal Navigator.

A.1.4.3 Building Reports Declaratively

Figure A-23 A Sample Report

Description of "Figure A-23 A Sample Report"

Reports display data from tables and views (Figure A-23). You can base a report on multiple tables or views using a JOIN condition. You can highlight data that satisfies conditions you specify. For example, you can display in bold red text the records of all employees who have been employed less than two years. You can create hypertext links from values displayed in the report to other OracleAS Portal database portlets or URLs.

Additionally, the wizards enable you to specify other options to achieve the following results:

• Limit data displayed in the report

• Sum column values

• Use logical operators to select data within the columns

• Format data in the report

• Format the report's personalization form

• Specify PL/SQL code that executes at various points in the report.

The OracleAS Portal Portlet Builder wizards provide three construction methods for building reports:

• Query By Example (QBE) Reports

• Reports From Query Wizard

• Reports From SQL Query

These reports are described in Table A-1. This section describes how to build a report using the Query Wizard.

To build a report using the Query Wizard:

1. Follow the instructions detailed in "Building Portlets Declaratively".

   Return to this section once you complete step 6.

2. Click the Report link next to Create New…(Figure A-24)

   Figure A-24 The Report Link in the Example Applications Portal DB Provider

   
   Description of "Figure A-24 The Report Link in the Example Applications Portal DB Provider"
   
   

3. On the Reports page, click Reports From Query Wizard (Figure A-25).

   Figure A-25 The Reports From Query Wizard Link in the Portlet Builder

   
   Description of "Figure A-25 The Reports From Query Wizard Link in the Portlet Builder"
   
   

4. In the Name field, enter an internal name for the report (Figure A-26).

   Figure A-26 The First Step in the Report From Query Wizard

   
   Description of "Figure A-26 The First Step in the Report From Query Wizard"
   
   

   The internal name is not published to users.

5. In the Display Name field, enter a display name for the report (Figure A-26).

   The display name is used at runtime. It displays as the title of the browser window as well as in the banner of the report. When the report is published as a portlet, the display name is used as the title of the portlet and in the List of Available Portlets in the Portlet Repository.

6. From the list of Portal DB Providers, select the provider that will own this report (Figure A-26).

   We recommend that you use a provider specifically created for owning, or hosting, your declaratively-built portlets. This list displays only those providers in which you have been granted privileges to build portlets.

7. Click Next.

8. From the Tables and Views list, choose one or more tables or views on which to base this report (Figure A-27).

   
   

   Note: In the Tables and Views field, you can use constants for the OracleAS Portal schema and the application schema (for example, enter #APP_SCHEMA#.EMP). For more information, see "Referencing the OracleAS Portal Schema".

   
   

   Figure A-27 Selecting a Data Source for the Report

   
   Description of "Figure A-27 Selecting a Data Source for the Report"
   
   

9. Click Add after each selection (Figure A-28).

   Figure A-28 Clicking Add to Add a Data Source

   
   Description of "Figure A-28 Clicking Add to Add a Data Source"
   
   

10. Click Next.

11. If you are basing your report on multiple tables or views, identify the columns that will be joined to one another.

    This page displays only when you have selected multiple columns or views in the previous step. Default join conditions are automatically generated. You can accept or modify these defaults.

12. Click Next.

13. Select the columns you want to display in your report (Figure A-29).

    Figure A-29 Selecting the Columns that Will Display in the Report

    
    Description of "Figure A-29 Selecting the Columns that Will Display in the Report"
    
    

    Click the arrows between the two columns to move individual selections or the entire list from one column to the other. Use the rearrange icons to the right of the Selected Columns list to set the order of appearance of your selected columns. Select no more than 255 columns.

14. Click Next.

15. Optionally, specify conditions that limit the data displayed in the report (Figure A-30).

    Figure A-30 Setting Column Conditions

    
    Description of "Figure A-30 Setting Column Conditions"
    
    

    Table A-7 lists and describes the options on this page.

    Table A-7 Options on the Column Conditions Page

    Option	Description
    Column Name	Choose a column if you want to specify a condition for the data in the report. The column need not be one that was selected for display. For example, you can choose a salary column that is not selected for display to limit the displayed data to only those employees that meet your specified salary condition, such as salary must be greater than $200. To set this condition, choose (for example) SAL under Column Name, then choose a Condition of >, and enter 200 as your Value.
    Condition	Choose conditions that will define Column Name's relationship to Value. Choose from: equal to (=) greater than (>) greater than or equal to (>=) less than (<) less than or equal to (<=) like not equal to (!=) null not null in not in
    Value	Enter a value that limits which rows of the identified column are displayed in the report. To specify multiple values after an IN or NOT IN condition, type a colon (:) between each value, for example, 10:20:30.
    More …	Click to display more fields for adding more conditions on this page.

    
    

16. Click Next.

17. Select from three report layout types: Tabular, Form, or Custom.

    • Tabular layouts are based on options you choose in the Report Building wizard. Results are displayed in a table format, where each record represents a row in the underlying table or view, and each data column displays as a column in the table.

    • Form layouts are based on options you choose in the Report Building wizard. Results are displayed in rows, where each data column is displayed in a separate row.

    • Custom layouts are based on HTML code that you supply in a later wizard step. Because you specify your own HTML code, you will have a greater level of control over the appearance of your report than you would using a structured (tabular or form) layout.

    This description will proceed as if you have selected a tabular layout. Some of the wizard steps you would take with other types of layouts are not included in the construction of a tabular report. For example, with a custom layout, you would be given an opportunity to specify your custom HTML code. That step does not display in the wizard when you choose a tabular layout.

18. Click Next.

19. Specify column formatting (Figure A-31).

    Figure A-31 The Column Formatting Page

    
    Description of "Figure A-31 The Column Formatting Page"
    
    

    Table A-8 lists and describes the options presented on this page.

    Table A-8 Column Formatting Options for a Tabular Report

    Option	Description
    Column	Displays the names of the table or view columns you selected in a previous wizard step.
    Column Heading	Enter the name you will use to identify this column in your report. For example, if your table has an EMPNO column, you can enter Employee ID Number, in lieu of the column's actual name.
    Sum	Select to add values together and display the result in the report.
    Data Type	Displays the data type of the data in the associated column.
    Align	Choose whether to align data to the left, center, or right margin of a report column. By default, numeric data aligns to the right, and alphabetical data aligns to the left.
    Display as	Choose from: Text—Displays the value in the report without interpreting any associated HTML tags. For example, the value <i>Name</i> displays as <i>Name</i>. HTML—Displays the value in the report using associated HTML tags. For example, the value <i>Name</i> displays as Name. Hidden—Hides the value from view in the report. For example, choose Hidden for columns whose values are included for link parameters that you do not want displayed in the report.
    Format Mask	Enter an Oracle display format for columns that contain date and numeric data types. See Table A-9 and Table A-10 for examples of date and number format masks. You cannot use format masks in reports generated in Excel format.
    Link	Create a link from the column to another OracleAS Portal portlet. The column value will display as a hypertext link. You can specify a link only if one has already been created for this column and has been stored in the database.
    Edit Link	Click to display the Set Link Parameters window. In this window, you can set the values of the link parameters to pass to the target portlet. Enter a static value, such as 10 or KING, or select from the list of columns you identified in the table or view columns step earlier in this wizard. If you need the value of a column for a link parameter, but do not want that column displayed in the report, you can set the Display as value to Hidden.
    Width Type	Specify one of the following: Char—Displays the output in the specified number of characters per line. For example, if you enter 20 in Width, the report displays 20 characters of the column data in each line. If the number of characters exceeds the specified number, the remaining characters are wrapped to the next line. Note, however, that if the output is ASCII format, Char is based on the column width. Pixel—Displays the output in the specified number of pixels per row. For example, if you enter 10 in Width, the column data displays 10 pixels of data per line. Percent—Displays the output in a column that takes up the specified percentage of the table. For example, if you enter 25 in Width, the column data displays in a column that takes up 25 percent of the overall table width.
    Width	Enter the numeric value for the width type.

    
    

    Table A-9 Examples of Date Format Masks

    Sample Date Format	Date Displayed
    MM/DD/RR	03/04/85
    Mon. DD, RRRR	Mar. 4, 1985
    Day Month DD fmHH:MI AM	Monday March 4 11:35 PM
    Dy Mon ddth fmHH24:MI:SS	Mon Mar 4th 23:35:22
    Day "the" ddthsp "of" Month	Monday the fourth of March

    
    

    Table A-10 Examples of Number Format Masks

    Sample Number Format	Number	Number Displayed
    -0000	7934 -7934	"7934" "-7934"
    -00000	7934	"07934"
    -NNNN	7639 535	"7639" "535"
    +NNNN	100 -99	"100" "-99"
    -%NN	10	"%10"
    $<NNNNN.00>	120014	" $1200.14"
    +KKNNNNN.00	1950	"+ 1950.00"
    $ <NNNNN.00>	1200 400	"$ 1200.00 "$ 400.00"

    
    

    The quotation marks in Table A-10 will not appear in your output. They are used here to make it clear where there are leading spaces. See the Oracle Application Server documentation for more information on format masks. You'll find it on the Oracle Technology Network, http://www.oracle.com/technology/index.html.

20. Click Next.

21. Optionally, specify conditions and define special formatting for displayed report data that will be used whenever those conditions are met.

    For example, specify that the background color of a particular row should become yellow whenever the JOB column equals PRESIDENT (Figure A-32).

    Figure A-32 The Formatting Conditions Page

    
    Description of "Figure A-32 The Formatting Conditions Page"
    
    

    Table A-11 lists and describes the options on this page.

    Table A-11 Options on the Formatting Conditions Page of the Report Wizard

    Option	Description
    Column	Choose a column on which the condition will be applied. The column must be one of those selected for display in an earlier wizard step.
    Condition	Choose conditions that will define Column's relationship to Value. Choose from: equal to (=) greater than (>) greater than or equal to (>=) less than (<) less than or equal to (<=) like not equal to (!=) null not null in not in
    Value	Enter a column value to trigger the condition. There is no need to apply formatting masks to numeric values. For example, in a table with an EMPNO column, if you want to apply special formatting to EMPNO values greater than 3000, select > for the Condition, and enter 3000 in this field. To specify multiple values after an IN or NOT IN condition, type a colon (:) between each value, for example, 10:20:30.
    Row/Col	Choose whether to apply the conditional formatting to an entire row or to just the affected column data. If you specify a condition, you must choose <Row> or a column name here.
    Color	Choose a text color for displaying data that meets the specified condition.
    Background Color	Choose a background color for the column or row that contains data that meets the specified condition. In some cases, the background color does not change as expected. This is a known issue with reports.
    Font Face	Choose a font in which to display data that meets the specified condition.
    A	Select to display results that meet the specified condition in bold.
    A	Select to display results that meet the specified condition in italic.
    A	Select to underscore results that meet the specified condition.
    Blink	Select to cause results that meet the specified condition to blink on and off.
    Seq	Enter a sequential number to establish an order of precedence for execution of the formatting. This is useful, for example, when a row contains data that meets more than one condition, and the formatting rules differ for each. The condition with the highest position in the order (that is, 1), takes precedence over all the others.

    
    

22. Click Next.

23. Define display options for the various possible views of the report:

    • Table A-12 describes display options common to all views (Figure A-33).

    • Table A-13 describes display options for full-page views (Figure A-34).

    • Options for portlet views are the same as those for full-page views, with a few noted exceptions. See Table A-13 and Figure A-34.

    • Table A-14 describes display options for column breaks (Figure A-35).

    • Table A-15 describes display options for row order (Figure A-36).

    • Table A-16 describes display options for mobile devices (Figure A-37).

    Figure A-33 Display Options Common to All Views

    
    Description of "Figure A-33 Display Options Common to All Views"
    
    

    Table A-12 Report Display Options Common to All Views

    Option	Description
    Show Total Row Count	Select to display the total number of rows in the report.
    Show Null Values as	Enter the text string you want to display for all null values in the report, for example, (null).
    Embed interMedia rich content in the report	Select to generate report output that shows any interMedia data values, such as audio, video, or images. For more information, see "Building Forms and Reports against interMedia Rich Content".
    Expire After (minutes)	Enter the number of minutes after the initial display to keep the report in the cache. When the time expires, the report data is refreshed from the database.
    Default Format	Choose a display format for the report: HTML—Formats the report using HTML tables, and displays output on a new page in the Web browser. Portlets that contain large amounts of data may take longer to display in this format. Excel—Formats the report for display in Microsoft Excel. When running the finished report, your local Excel settings may make it necessary for you to first save the report locally before it can be opened and viewed. ASCII—Formats the report using the HTML PRE tag to display heading and values in the report as ASCII text. This option is useful for displaying large amounts of data.

    
    

    Figure A-34 Report Display Options for Full Web-Page and Portlet Views

    
    Description of "Figure A-34 Report Display Options for Full Web-Page and Portlet Views"
    
    

    Table A-13 Report Display Options for Full Web-Page and Portlet Views

    Option	Description
    Font Face	Specify a font for report text.
    Font Size	Specify a font size for report text. Use relative values, such as +1, +2, and so on. Relative font size is the last font size specified in the HTML code for the page plus the relative value. For example, the last font size specified in HTML code is 14 points; a new heading is coded at +2 font size; the heading will display in 16-point type.
    Border	Specify the thickness of border to display around the report. Choose No Border, Thin Border, or Thick Border.
    Font Color	Specify a font color for report text.
    Page Style	Choose the page style you wish to use for the report when it appears as a portlet.
    Heading Background Color	Specify a background color for report column headings.
    Table Row Color(s)	Specify a background color for report rows. To choose more than one color use CTRL-Click (Windows). Rows will display in alternating colors.
    Maximum Rows Per Page	Enter the maximum number of rows you want to display in the report.
    Draw Lines Between Rows	Select to display lines between report rows.
    Log Activity	(Full page only.) Check to enter performance information and the names of users who request the report in the OracleAS Portal activity log.
    Show Timing	(Full page only.) Check to display report timing at the bottom of the report. Timing runs from when the server receives the request to generate the report to when the HTML for the report is generated.
    Show Query Conditions	(Full page only.) Check to display user-specified parameters that are passed to the query that created the report and the time when the report was created.

    
    

    Figure A-35 Report Display Options for Column Breaks

    
    Description of "Figure A-35 Report Display Options for Column Breaks"
    
    

    Table A-14 Report Display Options for Column Breaks

    Option	Description
    Break Style	Choose a break style.
    First Break Column	Choose the first column on which to break. For example, if you choose DEPTNO, the report will show all the rows associated with the first department number, followed by all the rows associated with the next department number, and so on. If you specify break columns, then you must also specify Order by values for the columns in the same order (see Table A-15).
    Second Break Column	Choose the second column on which to break. If you do not require a second level break, choose %.
    Third Break Column	Choose the third column on which to break. If you do not require a third level break, choose %.

    
    

    Figure A-36 Report Display Options for Row Order

    
    Description of "Figure A-36 Report Display Options for Row Order"
    
    

    Table A-15 Report Display Options for Row Order

    Option	Description
    Order by	Choose the table or view column whose values will be used to sort rows in the report. Choosing this option is equivalent to specifying a SQL ORDER BY clause. Choose Ascending to sort A to Z or 0 to 9. Choose Descending to sort Z to A or 9 to 0.
    then by	Choose additional columns whose values will be used to sort report rows. For example, if you choose Order by Department ID, then by Employee Name, Oracle Portal sorts report rows numerically using department IDs. Rows containing the same Department ID are then sorted alphabetically using employee names.

    
    

    Figure A-37 Report Display Options for Mobile Devices

    
    Description of "Figure A-37 Report Display Options for Mobile Devices"
    
    

    Table A-16 Report Display Options for Mobile Devices

    Option	Description
    Columns to Display	Choose the columns you want to display on a mobile device. Use this feature to reduce the size of the report to better fit onto the smaller screens available on mobile devices. For example, you would probably want to limit the number of columns displayed to 2 or 3 for most mobile devices.
    Maximum Rows Per Page	Enter the maximum number of rows you want to display on a single screen. For example, you probably ought to set this option to a relatively small value, such as 5, for most mobile devices.

    
    

24. Click Next.

25. In the Personalization Form Display Options section, choose the parameters that you want to display on the report's personalization form (Figure A-38).

    Figure A-38 Detail of the Personalization Form Display Options Page

    
    Description of "Figure A-38 Detail of the Personalization Form Display Options Page"
    
    

    For each parameter you choose, an entry field appears on the personalization form that enables users to choose their own conditions for displaying data in the report. Check Value Required if you want to require the user to enter a value in the parameter entry field. If you wish, you can use bind variables to allow users to specify their own amounts. See "Using Bind Variables" for more information.

    Table A-17 lists and describes the display options for the report personalization form.

    Table A-17 Display Options for the Personalization Form Display Options Page

    Option	Description
    Value Required	Select to require that the user specify a value for the column's entry field on the report personalization form. If you do not check this box, the user will not be required to specify a value.
    Column Name	Choose a table or view column. An entry field will be added to the report personalization form. Users can specify values that limit the data displayed in the report. Entry fields appear only for the columns you select here.
    Prompt	Enter the prompt text you want to display next to the entry field. The prompt text tells users what to enter in the field. For example: Enter a Department Number:
    LOV	Select a list of values (LOV) for the column's entry field. Users of the personalization form can choose values from this list to limit the data displayed in the report. Click the Browse icon next to this field to locate an LOV. Note that the LOV must already be built and stored in the Portal Repository. For more information, see "Building Lists of Values Declaratively". In the LOV field, you can use a constant for the application name (for example, enter #APP_NAME#.dept_lov). For more information, see "Referencing the OracleAS Portal Schema".
    Display LOV As	Choose a format for displaying your list of values. Choose from: <Blank>: No option is selected. Check box: Each selection displays with a check box next to it. Users select or clear the box to indicate their preferred value. Combo box: Combines a pop-up list and a manual entry field. Users can either select a provided value or enter their own. Pop up: Provides a pop-up list from which users select a value. Radio group: Lists each selection with a radio button next to it. Users select one of the buttons to indicate their preferred value. Multiple Select: Allows users to choose more than one value.
    Make Public	Select to allow values to be made public, that is, to allow non-authenticated users to see them. Be sure to make public any element that will be a variable in the list of portlet parameters associated with a page parameter.
    More Parameters	Click to display more fields on this page to include more table or view columns on the report personalization form.

    
    

26. In the Formatting options section, choose formatting options to include on the report personalization form (Figure A-39).

    Including one or more of these selections enables users to have a greater level of control over the final appearance of the report.

    Figure A-39 The Formatting Options Section of the Personalization Form Display Options Page

    
    Description of "Figure A-39 The Formatting Options Section of the Personalization Form Display Options Page"
    
    

27. In the Public Formatting Options section, choose formatting options to include on the parameter tab for the page where the report is displayed (Figure A-40).

    Figure A-40 The Public Formatting Options Section of the Personalization Form Display Options Page

    
    Description of "Figure A-40 The Public Formatting Options Section of the Personalization Form Display Options Page"
    
    

28. In the Button Options section, select the buttons that will display on the report personalization form (Figure A-41).

    Table A-18 lists and describes button display options for the report personalization form.

    Figure A-41 The Button Options Section of the Personalization Form Display Options Page

    
    Description of "Figure A-41 The Button Options Section of the Personalization Form Display Options Page"
    
    

    Table A-18 Button Display Options for the Personalization Form Display Options Page

    Option	Description
    Button	Select one or more buttons to display on the report personalization form: Run—Displays the URL portlet with the options the user has specified in the personalization form. Save—Saves users' current selections to enable them to rerun a report using the same parameters without having to reenter them into the report personalization form. When you include this button, the report personalization form also displays a Reset to Defaults button that resets all values in the report personalization form to their default values. Batch—Runs the URL portlet in batch mode and saves the results in the database. Reset—Undoes any changes made to the report personalization form since the last Save, Reset, or Reset to Defaults event.
    Name	Enter the label that will display on the selected button. Keep the name short to avoid displaying large buttons. You cannot change the label on the Reset to Defaults button.
    Location	Specify the location of the button on the report personalization form. Choose from Top, Top and Bottom, or Bottom.
    Alignment	Specify the alignment of the button on the report personalization form. Choose from Left, Center, or Right.

    
    

29. Click Next.

30. Optionally, select a template to set the look and feel of the report and the report personalization form when they are displayed on a full Web page.

    The template you choose here applies to both the report and the report personalization form when they are displayed on a full Web page. The template is not used when the report and report personalization form are displayed as portlets; in this case, the style of the page is used.

    The template controls the look and feel of the page on which the report appears. In contrast, the display options you have specified on other pages in this wizard control the look and feel of the report itself.

    Click the Preview button to see how the template elements will display (Figure A-42).

    Figure A-42 Report and Personalization Form Text Page

    
    Description of "Figure A-42 Report and Personalization Form Text Page"
    
    

31. Optionally, enter text for display on the top or bottom of the report or the report personalization form (Figure A-42).

    Table A-19 lists and describes the options for including text. Enter text for the report in the left column. Enter text for the report personalization form in the right column.

    Table A-19 Options for Including Text on a Report or a Report Personalization Form

    Option	Description
    Display Name	Edit the display name for the report and the report personalization form. You can specify HTML in this field.
    Description	(Report only.) Provide a description of the purpose of this report. This is an attribute that may or may not display, depending on which attributes have been selected for display in the region where the report or report portlet is placed.
    Header Text	Enter any introductory text for display at the top of the report or report personalization form. Header Text displays below the display name or title. You can specify HTML in this field.
    Footer Text	Enter any text for display at the bottom of the report or report personalization form.You can specify HTML in this field.
    Help Text	Enter any text for inclusion on a help page for the report or report personalization form. OracleAS Portal automatically includes a help button with the report and the report personalization form when they are displayed on a full page. When they are displayed as a portlet, whether the help button displays is controlled by the way the host portlet region has been configured. Users click the help button to display the text you enter here. You can specify HTML in this field.
    About Text	Enter any text for inclusion in an About box. OracleAS Portal automatically includes an About button with the report and the report personalization form when they are displayed on a full page. When they are displayed as a portlet, whether the about button displays is controlled by the way the host portlet region has been configured. Users click the about button to display the text you enter here.

    
    

32. Click Next.

33. Optionally, enter PL/SQL code that runs during the execution of the report or report personalization form HTML (Figure A-43).

    Table A-20 lists and describes the options available for running PL/SQL code at different points during the execution of the HTML code that creates the report or the report personalization form.

    
    

    Note: In the PL/SQL you enter into these fields, you can use constants for the OracleAS Portal schema, the application schema, and the application name. For more information, see "Referencing the OracleAS Portal Schema".

    
    

    Figure A-43 Additional PL/SQL Code Page

    
    Description of "Figure A-43 Additional PL/SQL Code Page"
    
    

    Table A-20 Options for Executing PL/SQL Code During Report Publication

    Option	Description
    … before displaying the page	Enter a PL/SQL procedure that will execute before the page containing the report or the report personalization form displays.
    … after displaying the header	Enter a PL/SQL procedure that will execute after the report or the report personalization form header displays.
    … before displaying the footer	Enter a PL/SQL procedure that will execute before the report or the report personalization form footer displays.
    … after displaying the page	Enter a PL/SQL procedure that will execute after the page containing the report or the report personalization form displays.

    
    

34. Click Finish.

    This saves the report and takes you to a summary page where you can manage and test run the report (Figure A-44). Subsequent sections in this appendix provide more detail about these tasks.

    Figure A-44 The Report Summary Page

    
    Description of "Figure A-44 The Report Summary Page"
    
    

A.1.4.4 Building Forms and Reports against interMedia Rich Content

You can integrate interMedia rich content, such as such as images, audio, and video, into the reports and forms you build with OracleAS Portal. These objects are stored in the Oracle Database 10g.

The database column types for the interMedia rich content you can display include:

• ORDIMAGE: This object type supports the storage, management, and manipulation of image data, such as GIF, JPEG, and BMP.

• ORDAUDIO: This object type supports the storage and management of audio data, such as MP3, AU, WAV, and MPEG

• ORDVIDEO: This object type supports the storage and management of video data such as REAL, QuickTime 3/4, AVI, and MPEG.

In addition to allowing the use of these interMedia rich content type columns in an OracleAS Portal report or form, object attributes can be displayed in join conditions, formatting, column conditions, and so on. For example, display a video clip's size or duration in a report, or set a report condition that specifies that only objects modified after a certain date will display.

Keep in mind that, although interMedia supports a variety of content types and formats, the browser being used to view this material must natively support the relevant MIME type or have an installed plug-in that displays rich content that is not typically supported on the Web. For example, most browsers can natively display GIF and JPEG images, but TIFF images are not displayed without an installed plug-in.

interMedia and Reports

Two OracleAS Portal Reports Wizards offer the opportunity to include interMedia rich content:

• Reports from Query Wizard

• Query By Example Report (QBE)

  With QBE reports, object-type attributes will not display. Consequently, trying to provide a value for an interMedia-based column in a report personalization form results in an error. This means that you cannot use a report personalization form for a QBE report that runs against interMedia object-type columns.

You can display interMedia rich content in a report in one of two ways:

• Embedded (inline)

  The image, audio, or video runs in place within the report.

• As a linked icon

  When a user clicks the link, the content is displayed in a new browser window, or it may be handled by the associated source application. For example, it may display in a RealPlayer window.

These display options apply to all columns that contain interMedia content in the OracleAS Portal report. They can be selected on the Display Options tab in the OracleAS Portal Report Wizard. By default, interMedia rich content is represented by icons in a report. Users click an icon to view the actual content. If you plan to rely on the default, you do not need to do anything further. If you plan to embed interMedia rich content, select the Embed interMedia rich content in the report check box on the Display Options page in the Report Wizard. Refer to Table A-12.

For more information about the Report Wizard, see "Building Reports Declaratively".

interMedia and Forms

Two OracleAS Portal Form Wizards offer the opportunity to include interMedia rich content in your form:

• Form based on table or view

• Master-detail form

With either of these methods, you must make sure that the underlying database object you are changing through your form is based on columns of type ORDIMAGE, ORDAUDIO, or ORDVIDEO.

To include interMedia rich content in your form, on the Formatting and Validation Options page of the OracleAS Portal Form Wizard, select the column name in the left frame, and specify an Item Type of File Upload (interMedia) in the right frame.

One advantage of uploading images, audio, and video clips into interMedia-based columns over uploading into BLOB columns is that the data is automatically parsed to extract several attributes, such as MIME type, length, and any user-defined metadata that might be included in the original media file. For example, a QuickTime file might contain close-captioning, or a RealVideo file might have copyright information. With interMedia-based columns, these attributes are automatically extracted and stored in the interMedia object for indexing and querying.

A.1.4.5 Building Charts Declaratively

Figure A-45 A Chart Built with the Portlet Builder Chart Wizard

Description of "Figure A-45 A Chart Built with the Portlet Builder Chart Wizard"

Charts display data from database tables or views as bar charts. They are based on at least two table or view columns:

• Values in the Label column provide a display name for the bars on the chart.

• Values in the Values column calculate the size of the bars on the chart relative to one another. Value columns must always contain numeric data.

You can also specify a Link column. Values in this column create hypertext links from the chart's labels to other OracleAS Portal database portlets or URLs. For example, with a link in place, users can click the label in a chart and jump to a form that enables them to update the data on which the chart is based.

Charts are also an effective way to display aggregate data. You can use Group By and Summary Options in the wizard to sum the column values returned by your query, for example, the minimum, maximum, or average value, or the number of values in a column.

The portlet-building wizards in OracleAS Portal provide two construction methods for charts:

• Charts From Query Wizard

• Charts From SQL Query

The Query Wizard provides a greater level of assistance in constructing a chart. The SQL Query method provides you with a greater level of control over the query that will be used to fetch data for the chart.

This section describes how to build a chart using the Query Wizard.

To create a chart using the Query Wizard:

1. Follow the instructions detailed in "Building Portlets Declaratively".

   Return to this section once you complete step 6.

2. Click the Chart link next to Create New… (Figure A-46).

   Figure A-46 The Chart Link Next to Create New …

   
   Description of "Figure A-46 The Chart Link Next to Create New …"
   
   

3. On the Charts page, click Charts From Query Wizard (Figure A-47).

   Figure A-47 The Charts From Query Wizard Link

   
   Description of "Figure A-47 The Charts From Query Wizard Link"
   
   

4. On the resulting page enter an internal name for the chart in the Name field (Figure A-48).

   The internal name is not published to users.

   Figure A-48 The First Step of the Chart Wizard

   
   Description of "Figure A-48 The First Step of the Chart Wizard"
   
   

5. In the Display Name field, enter the name by which users will identify this chart (Figure A-48).

   The display name is used at runtime. It displays as the title of the browser window as well as in the banner of the chart. When the chart is published as a portlet, the display name is used as the title of the portlet and in the List of Available Portlets in the Portlet Repository.

6. In the Description field, enter a description of this chart (Figure A-48).

   The description displays below the portlet in the Portlet Repository. It can be a summary of the portlet's purpose, a classification of its type, or any other descriptive information. It may be useful within your organization to have a standard approach to what is included in the description.

7. From the list of Portal DB Providers, select the provider that will own this chart (Figure A-48).

   This list displays only those providers in which you have been granted privileges to build portlets. We recommend that you use a provider specifically created for owning, or hosting, your declaratively-built portlets.

8. Click Next.

9. Click the Browse icon next to the Tables/Views field, and choose a table or view on which to base your chart (Figure A-49).

   
   

   Note: In the Tables/Views field, you can use constants for the OracleAS Portal schema and the application schema (for example, enter #APP_SCHEMA#.EMP). For more information, see "Referencing the OracleAS Portal Schema".

   
   

   Figure A-49 Clicking the Browse Icon to Select a Table or View

   
   Description of "Figure A-49 Clicking the Browse Icon to Select a Table or View"
   
   

   The tables and views that you can choose in this step are those that are accessible from the Portal DB Provider you selected in the previous step. Additionally, the list is limited to those tables and views on which you have the following privileges: all table views in the provider schema, granted select to public, granted select to the provider schema.

   If clicking the Browse icon produces a "No response from Server" message, and you know the table you want to select, try entering the table name directly into the field.

   
   

   Note: You can choose only one table or view using this wizard. If you want to base the chart on more than one table or view, you must use the Charts from SQL Query wizard to write your own SQL.

   
   

10. Click Next.

11. Choose the column(s) that contain the values you will display as Labels, Links, and Values in the chart (Figure A-50).

    Figure A-50 Columns to Be Used for Labels, Links, and Values in the Chart

    
    Description of "Figure A-50 Columns to Be Used for Labels, Links, and Values in the Chart"
    
    

    Table A-21 lists and describes the options on this page.

    Table A-21 Data Selection Options for Charts

    Option	Description
    Label	Choose the column that contains the values you will use as labels for different bars on the chart. For example, if you were going to create a chart that compared salaries of each job title in the department, you might choose the JOB column as the Label, SAL as the Value, and AVG as the Group Function.
    Link	Links must be preconstructed and stored in the database before you can use them here. Use this field to choose a preconstructed link that jumps from a chart bar's label to another OracleAS Portal portlet or URL. Click the Edit icon next to the Link field to set link parameters. If you do not set link parameters, the default values of the link are used.
    Value	Choose the column that contains the values to be used to calculate the relative size of the bars in the chart. Value columns always contain numeric data. The default for Value is 1. Specifying a value of 1 is useful if you also choose a group function. For example, you can choose the JOB column from SCOTT.EMP 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.
    Group Function	A group function calculates a single summary value (%, sum, count, average, maximum, minimum, standard deviation, or variance) from groups of numeric values in the Value column. OracleAS Portal uses values in the Label column to determine these groupings. For example, you can choose the JOB column from SCOTT.EMP as the Label, SAL as the Value, and AVG as the Group Function. This creates a chart that displays the average salary for each job classification.

    
    

12. Click Next.

13. Optionally, specify conditions that limit the data displayed in the chart.

    For example, to display data from all employees in Department 10, choose DEPTNO from Column Name, choose = or like from Condition, and enter 10 in the Value field (Figure A-51).

    Figure A-51 Defining Column Conditions for a Chart

    
    Description of "Figure A-51 Defining Column Conditions for a Chart"
    
    

    Table A-22 lists and describes the options that appear on the Column Conditions page.

    Table A-22 Options on the Column Conditions Page

    Option	Description
    Column Name	Choose columns whose values will be used to limit the data displayed in the chart. For example, if you want to display values greater than 3000 from the EMPNO column of the SCOTT.EMP table, choose EMPNO as the Column Name, choose > from Condition, and enter 3000 in the Value field.
    Condition	Choose conditions that will define Column Name's relationship to Value. Choose from: equal to (=) greater than (>) greater than or equal to (>=) less than (<) less than or equal to (<=) like not equal to (!=) null not null in not in
    Value	Enter a value that will limit the data displayed in the chart. To specify multiple values after an IN or NOT IN condition, type a colon (:) between each value, for example, 10:20:30.
    More Conditions	Click to add more fields for specifying additional conditions.

    
    

14. Click Next.

15. Optionally, specify conditions and define special formatting for chart data that will be used whenever those conditions are met.

    For example, you can specify that a chart bar should be red if its related column value exceeds a particular amount (Figure A-52).

    Figure A-52 Defining Formatting Conditions

    
    Description of "Figure A-52 Defining Formatting Conditions"
    
    

    Table A-23 lists and describes the formatting condition options on this page.

    Table A-23 Options on the Formatting Conditions Page of the Chart Wizard

    Option	Description
    Column	Choose a column on which the condition will be applied.
    Condition	Choose conditions that will define Column's relationship to Value. Choose from: equal to (=) greater than (>) greater than or equal to (>=) less than (<) less than or equal to (<=) like not equal to (!=) null not null in not in between
    Value	Enter a column value to trigger the condition. There is no need to apply formatting masks to numeric values. For example, in a table with an EMPNO column, if you want to apply special formatting to EMPNO values greater than 3000, select > for the Condition, and enter 3000 in this field. Enter date values in nls_date_format. To specify multiple values after an IN or NOT IN condition, type a colon (:) between each value, for example, 10:20:30. To specify a BETWEEN condition, separate the two values by either a comma, a space, or the word and.
    Col/Bar	Choose whether you want the conditional formatting to be applied to a displayed column name or to the <BAR> associated with value that meets the condition. For example, imagine that your chart uses the SAL and ENAME columns. ENAME is used to label each bar in the chart, and SAL is used to provide the value that drives the size of the bar. If you want a chart bar to turn red and blink if a salary is under 40,000, select <BAR>. If you want the ENAME to turn red and blink when the condition is met, select ENAME. If you want the salary, which is included in the chart along with the label and the bar, to turn red and blink when the condition is met, select SAL.
    Color	Choose a color for displaying data that meets the specified condition. The color will be applied to text or to the bar, depending on the choice you made in the Col/Bar field.
    Type Face	Choose a font in which to display data that meets the specified condition.
    A	Select to display results that meet the specified condition in bold.
    A	Select to display results that meet the specified condition in italic.
    A	Select to underscore results that meet the specified condition.
    Blink	Select to cause results that meet the specified condition to blink on and off.
    Seq	Enter a sequential number to establish an order of precedence for execution of the formatting. This is useful, for example, when a row contains data that meets more than one condition, and the formatting rules differ for each. The condition with the highest position in the order (that is, 1), takes precedence over all the others.

    
    

16. Click Next.

17. Define display options for the various possible views of the chart:

    • Table A-24 describes display options common to all views of a chart (Figure A-53).

    • Table A-25 describes display options for full-page views of a chart (Figure A-54).

    • Table A-26 describes display options for charts displayed as portlets (Figure A-55).

    • Table A-27 describes display options for charts displayed on mobile devices (Figure A-56).

    Figure A-53 Display Options Common to All Chart Views

    
    Description of "Figure A-53 Display Options Common to All Chart Views"
    
    

    Table A-24 Display Options Common to All Chart Views

    Option	Description
    Order By	Choose a method for ordering the chart's data: ORDER BY VALUE—The bars in the chart are shown in the same numeric order as values in the table column that you specified in the Value entry field. ORDER BY VALUE DESC—The bars in the chart are shown in reverse order of values in the table column that you specified in the Value entry field. ORDER BY LABEL—The bars in the chart are shown in the same order as values in the table column that you specified in the Label entry field. ORDER BY LABEL DESC—The bars in the chart are shown in the reverse order of values in the table column that you specified in the Label entry field.
    Include Null Values	Select to display null values in the chart.
    Treat Null Values as	Enter the text string you want to be displayed for any null values in the chart, for example: (this field is null).
    Expire After (minutes)	Enter the number of minutes after the initial display to keep the chart in the cache. When the time expires, the chart data is refreshed from the database.

    
    

    Figure A-54 Display Options for Full Web Page Chart Views

    
    Description of "Figure A-54 Display Options for Full Web Page Chart Views"
    
    

    Table A-25 Display Options Applying to Full Web-Page Chart Views

    Option	Description
    Type Face	Choose the font for displaying chart text.
    Font Color	Choose the font color for displaying chart text. User-defined colors are not available for selection.
    Font Size	Choose the font size for displaying chart text.
    Chart Type	Choose whether to display the cart bars in a horizontal or vertical orientation.
    Axis	Choose a method for displaying chart bars relative to the value of the chart's axis. For example, if you choose Zero, the value of the axis is set at 0. If you choose Average Value, the axis is set at the average of all values in the table or view column on which the Value section of the chart is based.
    Bar Image	Choose an image that will be used to fill in the bars on the chart. Choose MULTI to display bars in different colors.
    Chart Scale	Choose a percent (%) value to set the scale of chart bars relative to the Web page that will host the chart. Higher percentages display larger bars.
    Bar Width	Choose a width in pixels for chart bars. This option applies to bars in both horizontally-and vertically-oriented charts.
    Bar Height	Enter a height in pixels for chart bars. This option applies to bars in both horizontally-and vertically-oriented charts.
    Value Format Mask	Enter a format for numeric values or dates that appear on the chart.
    Maximum Rows Per Page	Enter the maximum number of bars you want to display per page.
    Summary Options	Choose one or more options to display summary information about the chart. Each option you choose is included in the summary information box at the bottom of the chart. Windows users can choose more than one option by pressing the Ctrl key while clicking each option.
    Log Activity	Select to enter information in the OracleAS Portal activity log. Information includes performance statistics and the names of users who request the chart.
    Show Timing	Select to display the time when the server received the request to generate HTML for the chart. This information is displayed at the bottom of the chart.
    Show Query Conditions	Select to display all user-specified parameters passed to the SQL query that created the chart and the time the chart was created. This information is displayed at the bottom of the chart.

    
    

    Figure A-55 Display Options for Charts Viewed As Portlets

    
    Description of "Figure A-55 Display Options for Charts Viewed As Portlets"
    
    

    Table A-26 Display Options for Charts Viewed as Portlets

    Option	Description
    Page Style	Choose the page style element to use for displaying chart text.
    Type Face	Choose a font to override the font of the chosen page style element.
    Font Color	Choose a font color to override the font color of the chosen page style element.
    Font Size	Choose a font size to override the font size of the chosen page style element.
    Bar Width	Choose a width in pixels for bars on the chart. This option applies to bars in both horizontally- and vertically-oriented charts.
    Bar Height	Choose a height in pixels for bars on the chart. This option applies to bars in both horizontally- and vertically-oriented charts.
    Maximum Rows Per Page	Enter the maximum number of bars you want to display in the chart.

    
    

    Figure A-56 Display Options for Charts Viewed on Mobile Devices

    
    Description of "Figure A-56 Display Options for Charts Viewed on Mobile Devices"
    
    

    Table A-27 Display Options for Charts Viewed on Mobile Devices

    Option	Description
    Maximum Rows Per Page	Enter the maximum number of bars you want to display in the chart. For example, you probably ought to set this option to a relatively small value, such as 5, for most mobile devices.

    
    

18. Click Next.

19. Define the content for display on the chart personalization form.

    Use the options on this page to give your users control over what data will appear in their view of the chart.

    For example, if you choose the DEPTNO column from the SCOTT.EMP table as a Column Name in this step, OracleAS Portal adds an entry field for DEPTNO to the chart's personalization form. Users can type a department number in the field to limit the display to data relevant to that department.

    Optionally, you can add a list of values to the entry fields. Instead of requiring users to type a numeric value for DEPTNO, you could add a list of values that enables them to choose, for example, 10, 20, or 30.

    In portlet implementations, the chart personalization form displays when users click the Personalize link at the top of the portlet. In full-page implementations, you can provide users with a link to run the chart; the link can target the chart personalization form, which in turn can have a Run button. Users click this button to run the chart once they've set their personalization options.

    If you wish, you can use bind variables to allow users to specify their own values in the personalization form. See "Using Bind Variables" for more information.

    • Table A-28 describes chart personalization form display options (Figure A-57).

    • Table A-29 describes chart personalization form formatting options (Figure A-58).

    • Table A-30 describes chart personalization form public formatting options (Figure A-59).

    • Table A-31 describes chart personalization form button options (Figure A-60).

    Figure A-57 Chart Personalization Form Display Options

    
    Description of "Figure A-57 Chart Personalization Form Display Options"
    
    

    Table A-28 Chart Personalization Form Display Options

    Option	Description
    Value Required	Select to require the user to specify a value for the column's entry field on the chart's personalization form.
    Column Name	Choose a table or view column for inclusion on the chart personalization form. An entry field will display on the form that enables users to specify values that limit the data that will be displayed in the chart. If you do not choose a table or view column, no entry appears on the personalization form.
    Prompt	Enter prompt text to display next to the entry field. The prompt text tells users what to enter in the field, for example: Choose the department number you want to display.
    LOV	Select a list of values (LOV) for the column's entry field. Users of the personalization form can choose values from this list to limit the data displayed in the report. Click the Browse icon next to this field to locate an LOV. Note that the LOV must already be built and stored in the Portal Repository. For more information, see "Building Lists of Values Declaratively". In the LOV field, you can use a constant for the application name (for example, enter #APP_NAME#.dept_lov). For more information, see "Referencing the OracleAS Portal Schema".
    Display LOV As	Select a format for displaying your list of values. Choose from: <Blank>: No option is selected. Check box: Each selection displays with a check box next to it. Users select or clear the box to indicate their preferred value. Combo box: Combines a pop-up list and a manual entry field. Users can either select a provided value or enter their own. Pop up: Provides a pop-up list from which users select a value. Radio group: Lists each selection with a radio button next to it. Users select one of the buttons to indicate their preferred value. Multiple Select: Allow users to choose more than one value.
    Make Public	Check this field if you want this element to display on the chart personalization form. Be sure to make public any element that will be a variable in the list of portlet parameters associated with a page parameter.
    More Parameters	Click to add more entry fields on the personalization form.

    
    

    Figure A-58 Chart Personalization Form Formatting Options

    
    Description of "Figure A-58 Chart Personalization Form Formatting Options"
    
    

    Table A-29 Chart Personalization Form Formatting Options

    Option	Description
    Axis	Select to enable users of the chart personalization form to choose a method for displaying chart bars relative to the value of the chart's axis. For example, if users choose Zero, the value of the axis is set at 0. If users choose Average Value, the axis is set at the average of all values in the table or view column on which the Value section of the chart is based.
    Include Nulls	Select to enable users of the chart personalization form to specify whether to display null values in the chart.
    Maximum Rows/Page	Select to enable users of the chart personalization form to specify the maximum number of bars to display in the chart.
    Summary	Select to enable users of the chart personalization form to choose one or more options that display summary information about the chart. Each option that users choose is included in the summary information box at the bottom of the chart.
    Type	Select to enable users of the chart personalization form to choose a font for displaying chart text.
    Display Name	Select to allow users of the chart personalization form to edit the display name for the chart.
    Order By	Select to enable users of the chart personalization form to set the display order for chart data.

    
    

    Figure A-59 Chart Personalization Form Public Formatting Options

    
    Description of "Figure A-59 Chart Personalization Form Public Formatting Options"
    
    

    Table A-30 Chart Personalization Form Public Formatting Options

    Option	Description
    Type	Select to enable unauthenticated users of the chart personalization form to choose a font for displaying chart text.
    Display Name	Select to enable unauthenticated users of the chart personalization form to edit the display name of the chart.
    Maximum Rows	Select to enable unauthenticated users of the chart personalization form to specify the maximum number of bars to display in the chart.
    Order By	Select to enable unauthenticated users of the chart personalization form to set the display order for chart data.

    
    

    Figure A-60 Chart Personalization Form Button Options

    
    Description of "Figure A-60 Chart Personalization Form Button Options"
    
    

    Table A-31 Chart Personalization Form Button Options

    Option	Description
    Button	(Full page only) Select one or more buttons to display on the chart personalization form: Run—Displays the URL portlet with the options the user has specified in the chart personalization form. Save—Saves users' current selections to enable them to rerun a chart using the same parameters without having to reenter them into the chart personalization form. When you include this button, the chart personalization form also displays a Reset to Defaults button that resets all values in the chart personalization form to their default values. Batch—Runs the URL portlet in batch mode and saves the results in the database. Reset—Undoes any changes made to the chart personalization form since the last Save, Reset, or Reset to Defaults event.
    Name	Enter the label that will display on the selected button. Keep the name short to avoid displaying large buttons. You cannot change the label on the Reset to Defaults button.
    Location	Specify the location of the button on the chart personalization form. Choose from Top, Top and Bottom, or Bottom.
    Alignment	Specify the alignment of the button on the chart personalization form. Choose from Left, Center, or Right.

    
    

20. Click Next.

21. Optionally, select a template to define the look and feel of the page that will host the chart, and enter text for display at the top and/or bottom of the chart and the chart personalization form.

    The template is applied only when the chart is displayed on a full Web page. If the chart is displayed as a portlet, the template you select here will be ignored and the style of the host page will be used.

    Table A-32 lists and describes the options available on this page (Figure A-61).

    Figure A-61 Options Available on the Chart and Personalization Form Text Page

    
    Description of "Figure A-61 Options Available on the Chart and Personalization Form Text Page"
    
    

    Table A-32 Options Available on the Chart and Personalization Form Text Page

    Option	Description
    Template	Choose a template to set the look and feel of the page that will host the chart. The template is applied only when the chart is displayed on a full Web page. If the chart is displayed as a portlet, the template you select here will be ignored and the style of the host page will be used. Display options, which you specified on previous wizard pages, control the look and feel of the chart itself.
    Preview Template	Click to view the appearance of the template that is currently selected in the Template list.
    Description	(Chart only.) Provide a description of the purpose of this chart. This is an attribute that may or may not display, depending on which attributes have been selected for display in the region where the chart or chart portlet is placed.
    Display Name	Edit the chart's display name or the title for the chart personalization form. You can specify HTML in this field.
    Header Text	Enter any introductory text that you want to display at the top of the chart or the chart personalization form. This text displays below the display name or title. You can specify HTML in this field.
    Footer Text	Enter any text that you want to display at the bottom of the chart or the chart personalization form. You can specify HTML in this field.
    Help Text	Enter any text that you want to display in a help page for the chart or chart personalization form. OracleAS Portal automatically includes a help button with the chart and the chart personalization form when they are displayed on a full page. When they are displayed as a portlet, whether the help button displays is controlled by the way the host portlet region has been configured. Users click the help button to display the text you enter here. You can specify HTML in this field.

    
    

22. Click Next.

23. Optionally, enter PL/SQL code to run at different points during the runtime creation of the chart or the chart personalization form.

    Table A-33 lists and describes the options available on this page (Figure A-62).

    Figure A-62 Additional PL/SQL Code Options for Charts

    
    Description of "Figure A-62 Additional PL/SQL Code Options for Charts"
    
    

    Table A-33 Options Available on the Additional PL/SQL Code Page

    Option	Description
    … before displaying the page.	Enter a PL/SQL procedure that will execute before the page containing the chart or the chart personalization form displays.
    … after displaying the header.	Enter a PL/SQL procedure that will execute after the chart or the chart personalization form header displays. If you want to display output in the chart from a PL/SQL procedure, you must enter the PL/SQL in the … after displaying the header field.
    … before displaying the footer.	Enter a PL/SQL procedure that will execute before the chart or personalization form footer displays.
    … after displaying the page.	Enter a PL/SQL procedure that will run after the page containing the chart or personalization form displays.

    
    

24. Click Finish.

    Clicking Finish saves your changes and takes you to a summary page where you can test your results (Figure A-63). See "Performing Test Runs on a Portlet" for more information on testing your results.

    Figure A-63 Chart Summary Page

    
    Description of "Figure A-63 Chart Summary Page"
    
    

A.1.4.6 Building Lists of Values Declaratively

Figure A-64 Different Display Types for a Dynamic List of Values

Description of "Figure A-64 Different Display Types for a Dynamic List of Values"

When you build a list of values using the OracleAS Portal List of Values Wizard, you have the option of creating two types of lists:

• Dynamic list of values

• Static list of values

A dynamic list of values takes its content from a SELECT statement you build through the wizard. The content of the list may change, depending on the data that is available for selection from the source database. A static list of values takes its content from values you enter in the wizard. These values do not change unless you edit the list and specifically alter them.

This section describes how to create a dynamic list of values.

To create a dynamic list of values:

1. Follow the instructions detailed in "Building Portlets Declaratively".

   Return to this section once you complete step 6.

2. Click the List of Values link next to Create New… (Figure A-65).

   Figure A-65 The List of Values Link Next to Create New …

   
   Description of "Figure A-65 The List of Values Link Next to Create New …"
   
   

3. On the List of Values page, click Dynamic List of Values (Figure A-66).

   Figure A-66 The Dynamic List of Values Link

   
   Description of "Figure A-66 The Dynamic List of Values Link"
   
   

4. On the resulting page, select the Portal DB Provider that will own this list from the Owner drop-down list (Figure A-67).

   Figure A-67 Defining List of Values Options

   
   Description of "Figure A-67 Defining List of Values Options"
   
   

5. In the Name field, enter a name for this list of values (Figure A-67).

   This name appears on the selection list when you insert a list of values.

6. From the Default Format drop-down list, choose a default format for the list of values (Figure A-67).

   Choose from:

   • Check box: Each selection displays with a check box next to it. Users select or clear the box to indicate their preferred value.

   • Combo box: Combines a pop-up list and a manual entry field. Users can either select a provided value or enter their own.

   • Pop up: Provides a pop-up list from which users select a value.

   • Radio group: Lists each selection with a radio button next to it. Users select one of the buttons to indicate their preferred value.

   • Multiple Select: Allow users to choose more than one value.

   Note that portlet developers who add the list of values to their portlet can override this default and display the list in a different format.

7. Specify whether to show null values by selecting either Yes, No, or % from the Show Null Value drop-down list (Figure A-67).

   • When you select Yes, null values display as Null in the list of values.

   • When you select No, null values display as blank spaces in the list of values.

   • When you select %, blank spaces are also displayed. The difference between % and No is that, with %, when you place the list of values on a form, the null value (the wildcard %) is available for the set up of dynamic transactions in the form's code.

8. Enter a SQL SELECT statement in the SQL Query text box (Figure A-67).

   Select two values from two table or view columns (though you can select each value from the same column):

   • The first column specifies the values displayed in the list of values.

   • The second column specifies the actual values that are passed to the portlet.

   For example, the query select ename, empno from scott.emp creates a list of values that displays employee names from the ENAME column. It passes the employee's associated ID number (EMPNO) to the portlet when a user chooses a name from the list of values.

   Do not end your query with a semicolon.

   
   

   Note: You can enter bind variables by prefixing them with colons (:), but only if the list of values is to be used in a form portlet. Lists of values with bind variables will not work in other kinds of OracleAS Portal portlets. In the SQL query, you can use constants for the OracleAS Portal schema and the application schema. For more information, see "Referencing the OracleAS Portal Schema".

   
   

9. Click OK to save your changes.

   Clicking OK takes you to a summary page where you can manage the list of values (Figure A-68). For example, from this page, you can run the list to see the different ways it can display.

   Figure A-68 List of Values Summary Page

   
   Description of "Figure A-68 List of Values Summary Page"
   
   

Once you complete a list of values, it becomes available. It displays automatically on selection lists wherever you can add a list of values. For example, both the form and report personalization forms you construct with Portlet Builder wizards allow for the addition of lists of values. As you go through these wizards, you'll note that the list you created here now appears as a selection option.

A.3.6.1 Viewing the Package Spec and Body for a Portlet

To view the package spec and body for a portlet:

1. Navigate to the component management page for the portlet with which you want to work.

   For information on how to navigate to this page, see "Navigating to the Component Management Page".

2. Click the Develop tab to bring it forward.

3. Click the Package Spec link next to PL/SQL Source to view the portlet's spec; click the Package Body link next to PL/SQL Source to view the body.

A.3.6.2 Viewing the Call Interface for a Portlet

To view the call interface for a portlet:

1. Navigate to the component management page for the portlet with which you want to work.

   For information on how to navigate to this page, see "Navigating to the Component Management Page".

2. Click the Develop tab to bring it forward.

3. Click the Show link next to Call Interface.

A.5.1.1 Inheriting Portlet Access Privileges from a Provider

By default, access to your portlet is inherited from the provider that owns it. For example, all users who have the Execute privilege on the owning provider are automatically able to execute your portlet. Users with the Edit provider privilege can edit all the portlets owned by the provider, and so forth. You can set provider access through the Grants tab (for more information, see "Granting and Revoking Privileges on Database Objects"). On the Grants tab, you can grant different privileges to different groups of users. Once privileges on the provider are established, you can specify that any portlets created under the provider must inherit the same set of privileges. This section describes how to specify that portlets should inherit privileges granted to their host providers.

To inherit portlet access privileges from a provider:

1. Click the Navigator link at the top of the Portal Builder page.

2. Click the Providers tab to bring it forward.

3. Click the Locally Built Providers link.

4. Click the name of the Database Provider that contains the portlet on which you want to grant access privileges.

5. Click the Manage link next to the relevant portlet.

   The Manage page displays.

6. Click the Access tab.

7. Select the Inherit Privileges from Provider check box.

   Portlet access will be defined by the portlet's host provider.

A.5.1.2 Granting Access Privileges to Individual Users

You can override the default access privileges (that is, those granted through the provider) by setting them at the portlet level. This section describes how to set access privileges at the portlet level, allowing you to more specifically target privileges to individual users.

To grant access privileges to individual users:

1. Click the Navigator link at the top of the Portal Builder page.

2. Click the Providers tab to bring it forward.

3. Click the Locally Built Providers link.

4. Click the name of the Database Provider that contains the portlet on which you want to grant access privileges.

5. Click the Manage link next to the relevant portlet.

   The Manage page displays.

6. Click the Access tab.

7. Clear the Inherit Privileges from Provider check box, then click Apply.

   The Access page displays two new sections: Grant Access and Change Access.

8. In the Grantee field, enter the name of the user or group to whom you want to grant privileges.

   If you are not sure of the name of the user or group click the Browse Users or Browse Groups icon and select from the list provided.

9. From the privilege list, choose an access privilege to grant to the user or group.

   See Table A-34 for information on the actions allowed with different access privileges.

10. Click Add.

    The user or group you entered in the Grantee field displays along with the access privilege you granted in the Change Access section at the bottom of the page.

    
    

    Note: OracleAS Portal uses the Oracle Internet Directory for identity management, serving as the repository for users and groups. In Oracle Internet Directory, groups are uniquely identified by their distinguished name (DN). Each group has a unique DN, though many groups can share a common name, in the same way that two people can share a common name, yet have completely different lineage (that is, John Smith and John Doe). When working within the portal, groups created from within that portal are displayed simply with their common names. However, when the portal references a group from some other location in the Oracle Internet Directory—such as a group from some other portal associated with the same Identity Management Infrastructure—the DN of the group is displayed to distinguish it from the portal's locally defined groups.

    
    

11. (Optional) To modify a user or group's access privilege, choose a new privilege next to the user or group in the Change Access section.

12. (Optional) To remove all privileges, click the Delete icon next to the user or group in the Change Access section.

13. Click Apply.

14. Click Close to exit the Manage page and return to the Portal Navigator.

A.6.5.1 Setting init.ora Parameters for Batch Jobs

To enable end users to execute jobs in batch mode, the database administrator should review the parameters in the init.ora file for the Oracle database where OracleAS Portal is installed. If these parameters are not set correctly, even though batch jobs may be sent to the batch queue, they may not run.

Table A-35 provides some suggested settings:

A.6.5.2 Adding a Batch Button to an Existing Component

To add a Batch button to an existing component:

1. Navigate to the component management page for the portlet you will work with.

   For information on how to navigate to this page, see "Navigating to the Component Management Page".

2. If necessary, click the Develop tab to bring it forward.

3. Click the Edit link at the bottom of the tab.

4. Click the Personalization Form Display Options tab to bring it forward.

5. Scroll to the Button Options section at the bottom of the tab.

6. Select the check box next to Batch.

7. Optionally, set the Batch button's display options:

   1. Enter a display name for the Batch button.

      The default is Batch.

   2. Specify the location for the button.

      Choose from Top, Top and Bottom, and Bottom. Top is the default

   3. Specify button alignment.

      Choose from Center, Left, or Right. Center is the default.

8. Click OK to save your changes and return to the Manage page

9. Click Close to return to the Portal Navigator.

A.8.2.1 Writing a JavaScript Event Handler for an Item on a Form

To write a JavaScript event handler for an item on a form:

1. Edit the form that includes the item for which you want to write an event handler:

   1. Go to the Manage page for the relevant form component.

      For information on navigating to this page, see "Navigating to the Component Management Page".

   2. If necessary, click the Develop tab to bring it forward.

   3. Click the Edit link toward the bottom of the tab.

2. Click the Formatting and Validation Options icon to bring that tab forward.

3. In the left frame, click the name of the item for which you want to write a JavaScript event handler.

4. In the right frame, scroll to the JavaScript Event Handlers section.

5. From the JavaScript Event Handlers list, choose the required event, and enter the JavaScript to be executed when the event occurs.

this.value = this.value.toUpperCase();

return confirm('Are you sure you want to submit the form?');

if (isNaN(this.value)) {
alert('Please enter a valid number.');
}

A.8.2.2 Writing a PL/SQL Event Handler for a Button on a Form

To write a PL/SQL event handler for a button on a form:

1. Edit the form that includes the button for which you want to write an event handler:

   1. Go to the Manage page for the relevant form component.

      For information on navigating to this page, see "Navigating to the Component Management Page".

   2. If necessary, click the Develop tab to bring it forward.

   3. Click the Edit link toward the bottom of the tab.

2. Click the Formatting and Validation Options icon to bring that tab forward.

3. In the left frame, click the name of the button for which you want to write a PL/SQL event handler.

4. In the right frame, scroll to the PL/SQL Button Event Handler section.

5. Define either:

   • A pre-defined event: Choose an event for the button from the PL/SQL Button Event Handler list.

   • A custom event: Choose Custom from the PL/SQL Button Event Handler list, and type the associated PL/SQL code.

A.9.2.1 Guidelines for Writing Field- or Form-Level Validation JavaScript

Follow these guidelines when writing a field- or form-level validation JavaScript:

• All validation routines should be written as functions and return either TRUE or FALSE values.

• The routine should display an alert message to users if the element (entry field) being validated contains an invalid value.

• The routine should bring focus (position the cursor) to the entry field where the user entered the incorrect value flagged by the JavaScript.

Example A-4 demonstrates the use of JavaScript to perform field-level validation.

1->  function isNumber(theElement)
     {
2->     if (isNaN(Math.abs(theElement.value)))
        {
3->        alert("Value must be a number.");
4->        theElement.focus();
           return false;
        }
        return true;
     }

The JavaScript presented in Example A-4 performs field-level validation tasks in the following sequence:

1. Identifies the name of the function and the entry field being validated.

2. Checks whether the absolute value of the entry field is a number. A relevant JavaScript function that signifies that a value is not a number is: isNaN

3. If the value in the entry field is not a number, the user is alerted with the message, "Value must be a number."

4. The routine brings focus to the entry field.

A.9.2.2 Creating JavaScript under the Shared Components Provider

To create JavaScript under the Shared Components Provider:

1. In the Navigator, click the Providers tab to bring it forward.

2. At the root level of the Providers tab, click the Locally Built Providers link.

3. Click the link for the Shared Components provider.

4. Click the Javascript link next to Create New…

5. In the JavaScript Name field, enter a descriptive name for the JavaScript.

   For example, enter NotNull for a JavaScript that ensures there are no null values in an entry field.

6. In the Language field, enter the language in which the JavaScript will be written.

   For example, enter JavaScript1.1 or JavaScript1.2.

7. Click Next.

8. Enter or copy your JavaScript into the field provided.

9. Click Finish.

To edit the JavaScript you just created, drill to the relevant JavaScript (in the Portal Navigator: Providers tab: Locally Built Providers link: Shared Components: JavaScripts: your JavaScript), and click the Edit link next to the relevant JavaScript.

A.9.2.3 Adding JavaScript to a Form

Once you have created JavaScript under the Shared Components provider, it is automatically added to the selection list that is available in the Build Form wizard. For example, when you create a form, you can select a JavaScript that you created once you reach the Formatting and Validation Options page. The list of available JavaScripts is located on this page under the Validation Options section (for more information, see "Building Forms Declaratively").

A.9.6.1 Building a Structured User Interface Template

You can use structured UI templates only with portlets. You create them with a wizard. In the wizard, you specify images, text, and layout elements that you want to apply to every portlet that uses the template.

You cannot use your own HTML code to extend the template beyond the pre-identified attributes. To achieve greater flexibility, you may want to build an unstructured user interface template (see "Building an Unstructured User Interface Template").

To build a structured user interface template:

1. In the Navigator, click the Providers tab to bring it forward.

2. At the root level of the Providers tab, click the Locally Built Providers link.

3. Click the link for the Shared Components provider.

4. Click the User Interface Template link next to Create New…

5. Click the Structured UI Template link.

6. In the Template Name field, enter a name for the template.

   Make the name as descriptive as possible. This is the name users will see when applying a UI template to a portlet during the build process. For example, if the template will be applied to all portlets created for a scheduling provider, you could name it Schedule_Template.

7. Select other options to refine the look and feel of the template.

   For example, you can choose an image to appear in the upper left corner of the template and a background image to display behind the portlet, as shown in Figure A-77.

   Figure A-77 Structured UI Template with Cloud Image

   
   Description of "Figure A-77 Structured UI Template with Cloud Image"
   
   

   If you have a question about an option, click the help icon. Leave an option blank if you do not want to include it in your template.

8. (Optional) Click Preview to open a new browser window that displays the UI template.

   You can reselect options then click Preview again to see how the changes affect the look of the template.

9. When you are satisfied with your template, click Create.

   The page updates with a link, which you can click to edit the template. If you do not want to edit the template at this time, click Close.

A.9.6.2 Building an Unstructured User Interface Template

Unstructured user interface templates are based on HTML code that you supply. Because you are writing your own HTML code, you can create a more elaborate and sophisticated unstructured UI template than you can a structured UI template.

To create an unstructured user interface template, you first write HTML code to create a Web page. You can also copy this code into OracleAS Portal from another source, such as a Web page editor. Once entered into OracleAS Portal, you edit the HTML code to add substitution tags. When the HTML code executes, the substitution tags embed portlets, titles, and other elements into the Web page. For example, you can add a #BODY# tag that adds a portlet such as a chart or report to the original Web page background. For a list of all the substitution tags you can include in your HTML code, see Table A-40.

In an unstructured template, you can use <ORACLE></ORACLE> tags to include SQL statements or PL/SQL blocks. You can also include HEAD elements such as custom JavaScript, cascading style sheet references, and META tags. You can use special substitution tags for integrating page metadata to embed PL/SQL scripting.

To build an unstructured user interface template:

1. In the Navigator, click the Providers tab to bring it forward.

2. At the root level of the Providers tab, click the Locally Built Providers link.

3. Click the link for the Shared Components provider.

4. Click the User Interface Template link next to Create New…

5. Click the Unstructured UI Template link.

6. In the Template Name field, enter a name for the template.

   Because this is the name users will see when applying a UI template to a page or portlet during the build process, you should make the name as descriptive as possible. For example, if the template will be applied to all portlets created for a calendar, you could name it Calendar_Template.

7. In the Template Definition field, enter or paste the HTML code you want to use as the basis for your unstructured user interface template.

   The HTML code you supply should create a Web page.

8. Embed substitution tags in the HTML code in the location where you want the items associated with the tags to appear in the finished template.

   Table A-40 lists and describes the tags you can use. For example, you may want to embed a #BODY# substitution tag in the code. #BODY# adds the main body of the page, such as page content or a portlet, to the Web page when the HTML code executes. If the HTML source code divides a page into two frames, you can embed the #BODY# tag in different places in the code, causing the portlet to display in the left frame or the right frame.

   Table A-40 Unstructured UI Template Substitution Tags

   Tag	Value set by
   #BODY#	The portlet itself or the page's portlets.
   #IMAGE_PREFIX#	The OracleAS Portal images directory as specified in the plsql.conf configuration file.
   #USER#	The user name of the user who is currently logged on.
   #USER.FULLNAME#	The full name of the user who is currently logged on. The full name includes the user's first, middle, and last names.
   #VERSION#	The version of this installation of OracleAS Portal.
   #HELPSCRIPT#	The JavaScript function used to open a window to display the online help. If you include #HELPLINK# in your template, you should also include this tag.
   #HELPLINK#	Used to display the help text for the component and its personalization form that you specified in the component creation wizard.
   #DIRECTION#	The direction of character layout (left-to-right or right-to-left). This value is set based on the language.
   #ALIGN_LEFT#	Align text to the left.
   #ALIGN_RIGHT#	Align text to the right
   #PAGE.STYLE#	The HTML LINK element that references the page's cascading style sheet. This tag is allowed only in the document HEAD.
   #PAGE.STYLE.URL#	The URL of the page's cascading style sheet.
   #PAGE.BASE#	The HTML base element for the base URL of the document. This tag is allowed only in the document HEAD.
   #PAGE.BASE.URL#	The base URL.
   #PAGE.BGIMAGE#	The HTML source for the whole page background image.
   #PAGE.BGCOLOR#	The HTML source for the whole page background color.
   #PAGE.SUBPAGELINK#	The URL for a sub-page link.
   #PORTAL.HOME#	The HTML image hyperlink to the portal home page.
   #PORTAL.HOME.URL#	The URL of the portal home page.
   #PORTAL.HOME.IMAGE#	The image used for the portal home page link.
   #PORTAL.HOME.LABEL#	The text used for the portal home page link.
   #PORTAL.NAVIGATOR#	The HTML image hyperlink to the Navigator.
   #PORTAL.NAVIGATOR.URL#	The URL of the Navigator.
   #PORTAL.NAVIGATOR.IMAGE#	The image used for the Navigator link.
   #PORTAL.NAVIGATOR.LABEL#	The text used for the Navigator link.
   #PORTAL.HELP#	The HTML image hyperlink to the online help.
   #PORTAL.HELP.URL#	The URL of the online help.
   #PORTAL.HELP.IMAGE#	The image used for the online help link.
   #PORTAL.HELP.LABEL#	The text used for the online help link.
   #PORTAL.LOGOUT#	The HTML text hyperlink to the logout URL.
   #PORTAL.LOGOUT.URL#	The logout URL.
   #PORTAL.LOGOUT.LABEL#	The text used for the logout link.
   #PORTAL.ACCOUNTINFO#	The HTML text hyperlink to the account information dialog.
   #PORTAL.ACCOUNTINFO.URL#	The URL of the account information page.
   #PORTAL.ACCOUNTINFO.LABEL#	The text used for the account information link.
   #PORTAL.COMMUNITY#	The name of the OracleAS Portal Community Web site.
   #PORTAL.COMMUNITY.URL#	The URL of the OracleAS Portal Community Web site.
   #PORTAL.COMMUNITY.IMAGE#	The image of the OracleAS Portal Community Web site.
   #PORTAL.COMMUNITY.LABEL#	The label of the OracleAS Portal Community Web site.
   #PAGE.CUSTOMIZEPAGE#	The HTML text hyperlink to the personalize page.
   #PAGE.CUSTOMIZEPAGE.URL#	The URL of the personalize page.
   #PAGE.CUSTOMIZEPAGE.LABEL#	The text used for the personalize page link.
   #PAGE.EDITPAGE#	The HTML text to allow page editing.
   #PAGE.EDITPAGE.URL#	The HTML text to allow page URL editing.
   #PAGE.EDITPAGE.LABEL#	The HTML text to allow page label editing.
   #PAGE.REFRESH#	The HTML text hyperlink used to refresh the page.
   #PAGE.REFRESH.URL#	The refresh URL.
   #PAGE.REFRESH.LABEL#	The text used for the refresh link.

   
   

9. (Optional) Click Preview to open a new browser window that displays the UI template.

   You can update the code then click Preview again to see how the changes affect the look of the template.

10. When you are satisfied with your template, click Create.

    The page updates with a link, which you can click to edit the template. If you do not want to edit the template at this time, click Close.