How To Create Dynamic Lov In Oracle Forms 10g

A static LOV contains values that you hard code when you create it.

A dynamic LOV is dynamically generated from a SQL query of a table column. It contains all values in the column.

Note: If you use a bind variable in your query, the dynamic LOV can only be used in form database portlets. When the end user runs the form, the values from the form must provide the values for the bind variable.

To build an LOV, you must have Edit or higher access privileges on the provider that will own the finished LOV.

On the Oracle Portal Home page, click on the shortcut bar.

Click the providers tab in the Oracle Portal Navigator.

The Name column of the Navigator displays the names of all providers in which you have privileges to build database portlets.

Click the name of the provider in which you want to build the LOV.

Click Create, then List of Values . The Lists of Values page displays.

Click Static List of Values.

In the Owner list, choose the provider that will own the finished List of Values.

Note: The list displays only providers in which you have Edit or higher access privileges.

In the Name field, enter a name for the LOV. Choose a name that describes the LOV's function; for example, DEPTNO_LOV.

In the Default Format list, choose a default display format for the LOV, for example a check box or radio group. Developers can change this format when building the database portlet's customization form.

In the Show Null Value list, choose whether to display null values in the LOV.

In the Display Value column, type each value you want to include in the LOV. End users of the LOV will be able to select from this list.

For each Display Value you specify, type a Return Value. This value is passed as an argument to the database portlet. The Return Value can be the same as the Display Value.

In the Display Order column, enter numbers beginning with 1 to set the order that values appear in the LOV. For example, enter 1 for the first display value you want to appear at the top of the list, 2 for the second value, and so forth.

Hint: You can skip numbers. For example, entering 2, 4, 8, 6 in the Display Order column produces the same LOV order as 1, 2, 4, 3. If you don't choose a Display Order, Display Values appear in the LOV in the order they are entered in this wizard step.

Click OK to create the List of Values.

(Optional) To test the LOV, click Run.

Follow steps 1-6 above.

Click Dynamic List of Values .

In the Owner list, choose the provider that will own the finished List of Values.

Note: The list displays only providers in which you have Edit or higher access privileges.

In the Name field, enter a name for the LOV. Choose a name that describes the LOV's function; for example, DEPT_LOV.

In the Default Format list, choose a default display format for the LOV, for example a check box or radio group.

In the Show Null Value list, choose whether to display null values in the LOV.

In the Enter SQL Query field, enter a SQL select statement that displays a value, then a database value (this is what is passed to the stored procedure); for example:

select ename, empno from scott.emp

Click OK.

(Optional) To test the LOV, click Run.

At runtime, the LOV processing assumes that the data values (either static or dynamic) do not contain HTML constructs. The LOV processing does not escape data values. It assumes that any data values that require such treatment will be dealt with in the programming you supply -- that is, in statements such as:

select htf.escape_sc ....

For example:

select htf.escape_sc(ENAME), EMPNO from SCOTT.EMP