OracleAS Portal List Of Values

An OracleAS Portal List of Values is a unique component in the sense that, along with OracleAS Portal Links, it is not designed to be placed on a page; rather, the purpose of an LOV is to be used as an attribute to provide data to other OracleAS Portal components . An LOV is invaluable when used in data entry “type applications for two reasons: 1) it eliminates incorrect data from being entered, and 2) it eliminates the need to memorize various codes needed to drive applications.

An LOV, after it is created, exists in OracleAS Portal but is not available to be placed on an OracleAS Portal page. Any new OracleAS component can use the LOV, and any existing OracleAS component can be modified to include the new LOV. LOVs can display values in different formats:

• Combo box This displays the data in a text box with a drop-down arrow on the text box s right side. When a user clicks the arrow, the box drops down to display all of the values of the LOV. Only a single value can be selected. Combo boxes are good for application screens that do not have a lot of free space on them.

• Pop-up This displays the data in a text box with a small notepad icon to its right. Clicking the icon opens another window, where the appropriate value can be selected. As in combo boxes, only a single value can be selected. We ve seen pop-ups for selecting tables or views when we created our example OracleAS Portal Forms and Reports earlier in this chapter.

• Check box This displays all of the data for the LOV with a small check box to the left of each entry. Check boxes are good when you want to display all values in the LOV and give the end user the ability to select multiple values.

• Radio group This displays all of the data for the LOV with a small circular button to the left of each entry. Only a single value can be selected. Radio groups are good for applications that need to display all LOV data to the user at one time and then allow one selection to be made.

• Multiple select This displays the first couple of data elements for the LOV in a text box. The user can scroll through the values and select multiple values by holding down the SHIFT key and single-clicking the appropriate values.

Figure 9-31 displays a simple LOV in defferent formats.

Figure 9-31: The different formats in which an LOV can be displayed

Figure 9-32 shows the two types of LOVs that you can build (Dynamic and Static).

Figure 9-32: The first page of the LOV wizard

A static LOV is one where the developer hard-codes the values to be referenced in the database table, the display values, and the order in which they are displayed. Static LOVs are good for data elements on your OracleAS Portal components that have values that never change, such as a GENDER or STATE field. Figure 9-33 shows an example of the creation of a static LOV. Note that the fields do not have to be entered in order, as the Display Order field can be used to reorder the entries. If more than five entries are needed, clicking the More button on the bottom left of the screen will redraw the wizard page with more rows.

Figure 9-33: Example creation of a static LOV

Dynamic LOVs query data out of the database and are populated on-the-fly (dynamically). Creation of a dynamic LOV (Figure 9-34) consists of specifying a query that is similar to the one we created for the OracleAS Portal Chart earlier in this chapter in the sense that it must be structured in a specific way. The syntax at the bottom of the page provides a template for how the SQL query needs to be constructed . Unlike in the SQL query needed to drive an OracleAS Portal Chart, the columns do not need to be aliased:

 select [display_column], [return_column] from table 

Figure 9-34: Example creation of a dynamic LOV

The query can be any valid SQL statement, so it can contain multiple tables, join conditions, where clauses, database links (as in Figure 9-34), references to stored procedures, etc. If bind variables are used, the end user will be prompted for a value before the LOV is rendered.

Caution  

LOVs are intended to make the user s interaction with the application easier. Requiring the end user to enter a value before an LOV is rendered makes the interaction more complex and, in general, is not desirable.

Figure 9-34 also shows a drop-down box named Default Format. On virtually every page where an LOV can be specified in OracleAS Portal, there is also an option to specify what type of LOV is to be displayed. If the type of LOV is left blank, the default format value specified here will be used. After the LOV is created, the values generated can be checked by clicking the Run link on the Manage page for the LOV.

Now that we have a valid LOV, the next example will show how to assign it as an attribute to an existing OracleAS Portal component. In the Navigator, click the Edit link next to the chart we created earlier in this chapter. You will see a screen similar to Figure 9-35.

Figure 9-35: Editing the OracleAS Portal chart from earlier in this chapter

Tip  

When editing an existing OracleAS Portal component, the tabs along the top right of the screen correspond to pages in the wizard used to create the OracleAS Portal component. Not every page has a corresponding tab, as some options (like the type of chart, form, or report and the provider it is associated with) cannot be modified.

Caution  

It is also important to remember that clicking the Edit link on the Navigator page will edit the production copy of the OracleAS component. If you want to preserve the production version and edit a new copy, you would have to click the Manage link on the Navigator page and then click Edit As New on the Manage page for the component.

In order to demonstrate LOVs, we will need to add a bind variable to this chart. In the SQL query window of Figure 9-35, the line

 and o.order_id = :p_order_id 

was added. This creates a bind variable for the chart, which can then have an LOV associated with it. Click the fourth tab to display the Customization Display Form Options page (Figure 9-36). You will notice that our new bind variable, p_order_id, is automatically displayed in Bind Variable list at the top of the page. Clicking the small notepad icon to the right of the LOV text box will bring up a list of available LOVs. Select the LOV created in Figure 9-34 and select one of the LOV formats from the Display LOV As drop-down box.

Figure 9-36: Adding the LOV to the chart

Click OK to generate a new OracleAS Portal Chart, and then click Run on the Manage page. A page appears indicating that no data is returned ”why? When we modified the query to include the line

 and o.order_id = :p_order_id 

we added a clause that will exclude all rows if p_order_id is null, which it is, since we have not provided any value for it. Return to the Manage page for the chart and click the Customize link. This link will display a page similar to Figure 9-37. This page allows end users to enter specific information that will determine the dataset used to populate the OracleAS Portal component in question. Under Query Options, the bind variable specified in the query (P_ORDER_ID) along with the LOV we created are displayed, allowing the user to specify what data should be included on the chart. Selecting Brown and clicking Run Chart will display Figure 9-38.

Figure 9-37: The customize screen for the chart

Figure 9-38: The OracleAS Portal Chart displaying only Brown s data

Tip  

One of the really nice features of the Customize screen (Figure 9-37) is the Save button located at the top of the screen. If a user changes parameters for an OracleAS Portal component, that user can save their preferences by clicking the Save button. The next time the component is displayed, the OracleAS Portal engine will determine if that user has saved any preferences for that component and apply those preferences when displaying the component. This allows end users to customize their OracleAS Portal experience with a minimal amount of programming. In the next chapter, we will look at how OracleAS Portal administrators can grant Customize privileges on OracleAS Portal portlets.