I’m working on an Interactive Grid in Oracle APEX where one column uses a PL/SQL function returning a SQL query as its List of Values (LOV).
The LOV function requires multiple parameters, including one that corresponds to the same column within the grid. However, at runtime, it seems the column’s own value is not passed — as if the Interactive Grid doesn’t provide the active row context to the LOV execution. Because of this, the function cannot return the correct filtered data.
Here’s an example of the function:
FUNCTION Emp_Lov (
p_lang_id IN NUMBER,
p_dept_id IN NUMBER DEFAULT NULL
) RETURN VARCHAR2 IS
v_return VARCHAR2(2000);
BEGIN
v_return := 'SELECT emp_code ||'' : ''|| DECODE('||p_lang_id||
', 2, emp_name_ar, emp_name_en) AS name, emp_id';
v_return := v_return || ' FROM hr_employees';
v_return := v_return || ' WHERE emp_active_flag = 1';
IF p_dept_id IS NOT NULL THEN
v_return := v_return || ' AND emp_dept_id = ' || p_dept_id;
END IF;
v_return := v_return || ' ORDER BY 1';
RETURN v_return;
END Emp_Lov;
In the Interactive Grid column LOV settings, I call this function and pass the parameters (for example, p_lang_id and p_dept_id), but the value related to the current row’s column (p_dept_id in this case) is not received or evaluated when the LOV runs.
Question:
Is this the expected behavior for Oracle APEX Interactive Grids?
And what is the proper or recommended way to make a PL/SQL-based LOV access the current record’s column values when the LOV is evaluated?