Creating Cascading LOVs in Oracle Forms

In Oracle Forms, List of Values (LOVs) make data entry easier by allowing users to select from predefined options instead of typing manually. Sometimes, LOVs need to work in a dependent manner, where the values in one LOV are filtered based on the selection made in another LOV. These are called Cascading LOVs.

This article explains how to create cascading LOVs in Oracle Forms, the logic behind them, and provides practical examples for implementation.

What are Cascading LOVs?

Cascading LOVs are sets of LOVs where one LOV’s data depends on the selection from another.

For example:

• First LOV → Select a Department.
• Second LOV → Display only the Employees belonging to the selected Department.

This approach ensures:

• Data consistency.
• Better usability for end-users.
• Improved performance, since only relevant values are shown.

Key Concepts Behind Cascading LOVs

To implement cascading LOVs in Oracle Forms, you will use:

1. Record Groups – to hold dynamic query results in memory.
2. LOVs – to display values linked to record groups.
3. Triggers – usually the WHEN-NEW-ITEM-INSTANCE or WHEN-BUTTON-PRESSED trigger to refresh LOVs dynamically.
4. Built-ins – such as:
   • CREATE_GROUP_FROM_QUERY
   • POPULATE_GROUP
   • SET_LOV_PROPERTY
   • SHOW_LOV

Steps to Create Cascading LOVs

Step 1: Create the First LOV (Parent LOV)

• Create an LOV for Departments using the query:

SELECT DEPTNO, DNAME FROM DEPT ORDER BY DNAME;

• Attach this LOV to the item EMP.DEPTNO.

Step 2: Create the Second LOV (Child LOV)

• Create another LOV for Employees, but initially attach it to a dummy record group.
• This LOV will later be linked dynamically based on the department selected in the first LOV.

Step 3: Populate the Child LOV Dynamically

In the WHEN-NEW-ITEM-INSTANCE trigger of the Employee LOV item, write:

DECLARE
   rg_id   RecordGroup;
   rg_name VARCHAR2(30) := 'EMP_RG';
   lov_id  LOV;
   ret     NUMBER;
BEGIN
   rg_id := FIND_GROUP(rg_name);

   -- Delete existing group if found
   IF NOT ID_NULL(rg_id) THEN
      DELETE_GROUP(rg_id);
   END IF;

   -- Create new record group with filtered employees
   rg_id := CREATE_GROUP_FROM_QUERY(rg_name,
             'SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = ' || :EMP.DEPTNO);

   -- Populate the group
   ret := POPULATE_GROUP(rg_id);

   -- Attach the record group to LOV
   lov_id := FIND_LOV('EMP_LOV');
   SET_LOV_PROPERTY(lov_id, GROUP_NAME, rg_name);
END;

This ensures that when the Employee LOV is opened, it only shows employees of the selected department.

Step 4: Display the Child LOV

You can display the LOV programmatically if needed:

DECLARE
   ret BOOLEAN;
BEGIN
   ret := SHOW_LOV('EMP_LOV');
END;

Example Walkthrough

1. User selects Department 10 (ACCOUNTING) in the Department LOV.
2. The Employee LOV is refreshed dynamically with employees belonging only to Department 10.
3. If the user selects Department 20 next, the Employee LOV automatically adjusts to show employees from Department 20.

This creates a smooth cascading relationship.

Best Practices for Cascading LOVs

• Validate Parent Selection: Ensure the parent LOV (Department) is selected before calling the child LOV (Employee).
• Use Proper Triggers: WHEN-NEW-ITEM-INSTANCE or WHEN-BUTTON-PRESSED are commonly used for refreshing LOVs.
• Limit Rows for Performance: Use WHERE clauses to restrict unnecessary data.
• Delete Old Record Groups: Always clean up existing record groups to avoid memory leaks.
• Reuse LOVs: Instead of creating multiple LOV objects, reuse the same LOV by dynamically changing its record group.

Conclusion

Creating cascading LOVs in Oracle Forms improves usability and ensures that users select valid, related values. By combining record groups, LOVs, and triggers, you can design interactive forms where child LOVs update automatically based on the parent LOV selection. This not only enhances user experience but also enforces data integrity at the application level.