How to write the query for interactive grid based on the select list in Oracle Apex

Question

I have a drop down select list (P2_ROLE). I want to generate the interactive grid based on the value that I select from the drop down.

I am trying to do that but the query is not returning any result in the grid. However, if I query it in the database, it returns me the result.

It looks like that it is not substituting the value from the dropdown into the IG query. But If I hard code the value, it gives me the results.

dropdown_IG issue

I also tried returning the value selected from the dropdown in the item : P2_SELECT_LIST_VALUE and then using it in the query. But it also doesn't work.

The query that I have used for my Interactive grid is :

  Select USER_NAME
  From WF_USER_ROLES
  Where ROLE_NAME = :P2_SELECT_LIST_VALUE ;  --using the value from new item

or

  Select USER_NAME
  From WF_USER_ROLES
  Where ROLE_NAME = :P2_ROLE;  -- using the value directly from Dropdown

After selecting the value in the dropdown, I am refreshing the IG region using dynamic action on change select list. But that also doesn't help. How can I do that ?

Littlefoot Littlefoot · Accepted Answer · 2018-10-01T08:55:12

Select list returns two values: display and return. Its form is, for example,

select dname  disp_val,
       deptno ret_val
from dept;

Its return value is then used in interactive grid's query.

Also, in order to be able to use select list's value, it has to be stored in session state; if it is not, query won't work. The simplest option is to submit the page, and you can do that by setting that item's Page action on selection property to "Submit page". Doing so, your IG query:

Select USER_NAME
From WF_USER_ROLES
Where ROLE_NAME = :P2_ROLE

should work OK. If it still does not, create an example on apex.oracle.com, provide credentials so that we could have a look at what you did.

How to write the query for interactive grid based on the select list in Oracle Apex

1 Answers