Oracle Forms

A Forms dynamic LOV

Home page

The purpose is to manage dynamic LOVs, able to build any LOV of 1 to 9 columns.

The record group is dynamically populated by the SELECT order provided, then the LOV is tuned to fit and display the corresponding columns.

It uses a pre-defined LOV that can handle 9 columns.

The data selected from the LOV are copied into 9 hidden items stored in a control block (:LOV).

The copy process is done in an Item-level KEY-LISTVAL trigger:

Declare

LB$Ok Boolean ;

LC$Lov Varchar2(30) := Get_Item_Property(:system.trigger_item, LOV_NAME) ;

Begin

If LC$Lov Is not null Then

LB$Ok := Show_Lov(LC$Lov);

If LB$Ok Then

-- get returned values --

:BL.TXT1 := :LOV.RETURN1 ;

End if ;

End if ;

End;

The LOV populating process is done in the POPULATE_LOV() procedure, stored in the program unit of the sample dialog provided,

But you can, of course, copy it in a PL/SQL library.

(In fact you need to keep together: PKG_COLLECT package, Build_Select_line function and Populate_LOV procedure)

How to use the dynamic LOV

You can manage 5 different dynamic LOVs in the provided sample dialog.

To populate the LOV, and eventually attach it to an item, use the Populate_LOV() procedure

PROCEDURE Populate_LOV

(

PC$SQL_Order IN VARCHAR2,

PC$NumLOV IN VARCHAR2,

PC$Title IN VARCHAR2,

PC$TargetItem IN VARCHAR2 default null,

PB$Display IN BOOLEAN Default false

)

PC$SQL_Order is the corresponding SQL query

PC$NumLOV is the number of LOV (1-5)

PC$Title is the title for the LOV

PC$TargetItem is the item you want to attach the LOV (bloc_name.item_name)

PB$Display is provided in debug mode to ensure the LOV will display the correct values

e.g.:

Populate_LOV

(

'Select empno, ename, sal from emp',

'1',

'Choose an employee',

'BL.TXT1',

False

);

If you want to provide aliases, they must be given within double-quotes:

Select empno "Number", ename "Name", sal "Salary" from emp

Special case:

If you need to incorporate a function that contains a comma, To_Char(), for instance, replace the comma by a ;

e.g.

select empno, ename, sal, TO_CHAR(hiredate;'DD/MM/YYYY') "HIREDATE" from emp

the ; will be replace by its normal comma at execution time.

The sample dialog

· Download the dynamic_lov.fmb sample dialog for you to test (Forms 9.0.2)

To implement it into you own Forms, you can simply drag the DYN_LOV object group into your module.