APEX Interactive Grid - ERR-1002 Unable to find item ID for item-CodePudding

I created a Interactive Grid where one of the columns is Popup LOV with Cascading List of Values.

Type of Popup LOV is PL/SQL Function returning SQL Query.

Syntax of my function:

declare
v_sql varchar2(4000);

begin
    
     if :F_ID = 61 then
     v_sql := 'select t_day d0, t_day d1
                from ...;

     elsif :F_ID = 62 then ...;

When I select values from the column I got an error:

ERR-1002 Unable to find item ID for item "F_ID" in application "119".

F_ID is one of the columns from my IG.

I am using Oracle Apex 20.1

CodePudding user response：

The behaviour you're seeing is a limitation from interactive grids:

The reason is that when a LOV based on a PL/SQL function returning SQL Query, that PL/SQL function is evaluated (and the SQL query is generated) during Interactive Grid Initialization, before the SQL query has been executed and the first row has been fetched. Thus the : F_ID column value is indeed available to the resulting SQL query, but not to the PL/SQL function which generates that query.

I even think that this is rather a limitation than a bug - “fixing” it would require that Interactive Grid must execute the PL/SQL code for each row of the query result, which would have massive performance impact.

(credits to Carsten)

CodePudding user response：

Did you pass IG column name in "Cascading LOV Parent Column(s)" property in LOV properties. See the screenshot bellow. This setup work for me in APEX 19.2

LOV as column in IG setup

CodePudding user response：

I just test in OCI with APEX 20.2 and it seems work as well. You can see how it is configured and compare by yourself what is different with your code. Hope will found why is not working in your side.

Here it is URL for demo APP. https://jyyxnpyzrpkotn3-db202002.adb.eu-frankfurt-1.oraclecloudapps.com/ords/f?p=4550:1:117227947509597:::::

use: Workspace: BI_APP_1_ADMIN User : DEMO_USER pass: FCfpUyaC5lw5pY1IHZPn