Creating Dynamic LOV in Oracle D2k Forms

by

Dynamic Lov is a good idea for the form where too many Lov requirement is there with different record groups. In this blog you will learn create dynamic Lov step by step.

See also:
https://www.foxinfotech.org/populate-list-with-recordgroup-in-oracle-forms
https://www.foxinfotech.org/adding-value-combolist-runtime-oforms
https://www.foxinfotech.org/when-validate-item-trigger-oracle-forms
https://www.foxinfotech.org/2012/11/creating-list-item-in-oracle-d2k_9366

Step-1 Create a Single Lov (you can give any name)
1.   Click on form builder navigator Lovs node and then click on Add button. Select create lov manually option from the window.
Step-2 Create Record Group (you can give any name)
2.   Click on form builder navigator Record Groups and then click on Add button and set the properties and query as displayed in below image.

Step-3 Create Parameters
3. Click on Parameter node and then click on Add button and create 4 Char type Parameters as displayed below:

Step-4 Set Lov Properties
4.   Set the lov properties as show below:

Step-5 Set Lov Column Mapping Properties
5.   Set the Lov's Column Mapping Properties with the parameters as show below:


Step-6 Create a procedure to populate lov 

PROCEDURE call_dynlov (IGroupName in Varchar2)
 IS
    blov boolean;
begin
    :parameter.lovcol1 := null;
    :parameter.lovcol2 := null;
    :parameter.lovcol3 := Null;
    :parameter.lovcol4 := Null;
    set_lov_property('dynlov', group_name, IgroupName);
    set_lov_property('dynlov', position, (get_application_property(display_width)-600)/2,
                                         (get_application_property(display_height)-250)/2-50);
    blov := show_lov('dynlov');
    if not blov then
             message('Value not choosen.');
    end if;
end;

Finally Call to this Lov with the following code:

DECLARE
 rg_name VARCHAR2(40) := 'YrGroup';
rg_id RecordGroup;
errcode NUMBER;
BEGIN
rg_id := Find_Group( rg_name );
--- Create group with 4 column because we already set the lov property for 4 cols, these 3 and 4 cols will --- be  not mapped to any fields
if  not id_null(rg_id) then
   delete_group(rg_id);
end if;
rg_id :=
 Create_Group_From_Query( rg_name, 'select main_menu_name col1, sub_menu_name col2, ''3'' col3, ''4'' col4 from
fox_main_menu a, fox_sub_menu b
where a.main_menu_code = b.main_menu_code order by 1,2');
--- call the procedure
 call_dynlov(rg_name);
            if :parameter.lovcol1 is not null then
                 --- if lov selected and parameter 1 value is not null then map to field
                 :fox_user_prog.mmn := :parameter.lovcol1;
                 :Fox_user_prog.smn := :parameter.lovcol2;
            end if;

 END;

See also: Oracle Forms Recipes
Like us to get notifications for free source code in future, thanks.


Dynamic LOV in Oracle Forms

Reviewed by Joseph on

Feb 10

Rating:
5

6 thoughts on “Creating Dynamic LOV in Oracle D2k Forms”

  2. Hi Munir,

    There is an alias col4 is missing in record group query, i added, below is the query:

    Create_Group_From_Query( rg_name, 'select employee_id col1, first_name col2, last_name col3, job_id col4 from employees');

    Try with this one, then let me know.

    Reply

  3. Sir,
    I'm fresh in oracle forms 10g.
    I'm try to create dynamic lov following your attractive idea.
    Module Nmae: dynamic_lov
    Block Name: employees based on hr.employees table
    Lov: dynlov
    Record group: dyngroup
    Record group query: SELECT '#' COL1, '#' COL2, '#' COL3, '#' COL4 FROM dual
    Parameters:
    lovcol1, lovcol2, lovcol3, lovcol4

    Procedure:
    PROCEDURE call_dynlov (IGroupName in Varchar2)
    IS
    blov boolean;
    begin
    :parameter.lovcol1 := null;
    :parameter.lovcol2 := null;
    :parameter.lovcol3 := Null;
    :parameter.lovcol4 := Null;
    set_lov_property('dynlov', group_name, IgroupName);
    set_lov_property('dynlov', position, (get_application_property(display_width)-600)/2,
    (get_application_property(display_height)-250)/2-50);
    blov := show_lov('dynlov');
    if not blov then
    message('Value not choosen.');
    end if;
    end;

    When-mouse-double-click trigger for showing lov:
    DECLARE
    rg_name VARCHAR2(40) := 'YrGroup';
    rg_id RecordGroup;
    errcode NUMBER;
    BEGIN
    rg_id := Find_Group( rg_name );
    --- Create group with 4 column because we already set the lov property for 4 cols, these 3 and 4 cols will --- be not mapped to any fields
    if not id_null(rg_id) then
    delete_group(rg_id);
    end if;
    rg_id :=
    Create_Group_From_Query( rg_name, 'select employee_id col1, first_name col2, last_name col3, job_id from employees');
    --- call the procedure
    call_dynlov(rg_name);
    if :parameter.lovcol1 is not null then
    --- if lov selected and parameter 1 value is not null then map to field
    :employees.employee_id := :parameter.lovcol1;
    :employees.last_name := :parameter.lovcol2;
    end if;

    END;

    But error occurred:
    ------------------
    FRM-41826: Cannot replace group; columns don't match LOV

    Please, help me.

    Thank you
    Munir

    Reply

  4. Ok thats good. Then I think there should be Null instead of '#' in record group query columns, then it will accept every data type.

    SELECT null COL1, null COL2, null COL3, null COL4 FROM dual;

    Reply

  6. Sir,

    employee_id column is number.
    i wrote Record group query: SELECT '#' COL1, '#' COL2, '#' COL3, '#' COL4 FROM dual
    re-write the Record group query: SELECT 1 COL1, '#' COL2, '#' COL3, '#' COL4 FROM dual

    and

    Create_Group_From_Query( rg_name, 'select employee_id col1, first_name col2, last_name col3, job_id col4 from employees');

    Now, done the practice using your good and smart one.

    Thank you, Sir.

    Reply

Leave a Comment