Hi all,
I had to struggle a little bit (too much) to find a solution to this question that was obvious in version 5.xx,6.xx and 7.xx.
One can find an example attached to this post. It is based on a list of erolename and the grid returns the eusername granted with these roles.
1 use the Oracle script to create
the table Type str_tbl
2 use the Oracle script to create
the function parse_list(p_list)
3 Have look at the bo, it should work in any case whenever each item length of list is less than 1000char
4 In the MBPM Solution the button on admin form is "cleaning" the list from all useless char. The grid is dependent, the list has dependent.
I wish this post will be useful to all the community.
Code
create type str_tbl is table of varchar2(1000);
create or replace function parse_list( p_list in varchar2 )
return str_tbl
pipelined
is
begin
for x in (select regexp_substr( p_list, '[^,]+', 1, level ) element
from dual
connect by level <= length( regexp_replace( p_list, '[^,]+')) + 1)
loop
pipe row( x.element );
end loop
return;
end;
Use it as this:
select * from table(parse_list('a,b,cy,4,5,azyeey'));
returns:
a
b
cy
4
5
azyeey
BO SQL Query is:
Select eUserName from eAssignment
Where
eRoleName in (select * from table(parse_list(@RoleList)))
Group by eUserName
-- @RoleList is mapped with the local.variable used by the list control
