Oracle Forms dynamic LOV

Collected Forms: http://sheikyerbouti.developpez.com/forms/dynamic_lov/dynamic_lov.htm

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.