Creating Lists of Values

Creating a Named LOV at the Application Level

You define named (or shared) LOVs at the application level by running the Create LOV Wizard and adding them to the List of Values repository. All LOVs can be defined as static or dynamic. Static lists are based on predefined pairs of display values and return values. Dynamic lists are based on a SQL query you write that selects values from a table.

To create a named LOV:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

   The Application home page appears.

3. Click the Shared Components icon.

4. Under User Interface, select Lists of Values.

   The Lists of Values page appears.

5. To create a LOV, click Create.

6. Follow the on-screen instructions.

   New named LOVs are added to the List of Values repository. Note to add the LOV to a page you must edit the appropriate item or column and select the named LOV.

About the List of Values Page

Once you create an LOV, it appears on the List of Values page.

Description of the illustration lov_page.gif

Use the Navigation bar at the top of the page to search for a LOV by name or change the page display. For example, you can change the default display by making a selection from View list. Available options include:

• Icons (the default) displays each LOV as a large icon. To edit an LOV, click the appropriate icon.

• Details displays each LOV as a line in a report. To edit an LOV, click the name.

About Static LOVs

Static LOVs are based on a static list of display values and return values you specify when you run the Create LOV Wizard. To create a static LOV, run the Create LOV Wizard and select the LOV type Static. Oracle Application Express stores the display values, return values, and sort sequence you specify in the List of Values repository. Once you add a static LOV to the repository, you can create an item and display it as a check box, radio group, select list, or popup list based on this definition.

Editing an Existing LOV

To edit an existing LOV, select the LOV on the Lists of Values page.

To edit an LOV:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. Click Shared Components.

4. Under User Interface, select Lists of Values.

5. Select an LOV.

   The Edit List of Values page appears.

6. To learn more about a specific item on a page, click the item label.

   When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

7. Click Apply Changes.

Referencing Session State Within an LOV

You can reference session state by using bind variables. Keep in mind that referencing session state makes an LOV a bit less reusable, but is still a recommended development practice. In the following example, this LOV only works if the item called my_deptno contains a valid department number.

SELECT ename, empno FROM emp WHERE deptno = :P1_DEPTNO

Referencing a Null Value in an Item Based on an LOV

LOVs have a null display value option and a null return value option. The null display value is the value the end user sees in the list indicating the no selection from the proper (non-null) values of the list will be made. When a user selects a null display value, the LOV's null return value is sent to the application when the page is submitted. If the developer has left the null return value unspecified (or empty), the actual value transmitted is not an empty string or an Oracle null, but the literal %null%. The application must be prepared to deal with this literal and treat is as the null selection.

Be aware of this behavior when writing code to evaluate submitted values. For example, suppose a page evaluates the submitted item P1_X and you need to use the PL/SQL expression replace(:P1_X,'%'||'null%',null) to prepare the item for permanent storage in session state or for passing to DML or other APIs.

To avoid problems, be aware of the appropriate way to code %null% in expressions that occur in page computations, processes, and validations. You must break up the string so that the application does not replace %null% with a null value in the page metadata when it is saved. Consider the following example:

'%'||'null%'

Accessing LOV Reports

Application Builder includes a number of reports designed to help you better manage LOVs.

To access LOV reports:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. When Application Builder appears, click Shared Components.

4. Under User Interface, select Lists of Values.

5. Select one of the following tabs at the top of the page:

   • Search

   • Subscription

   • Utilization

   • History

6. Follow the on-screen instructions.