1

The following code creates a filter that I successfully append to an existing sql statement.

DECLARE V_FILTER VARCHAR2(255);
begin
SELECT DISTINCT 'reserve reporting_unit_code' ||' IN (' || 
                listagg ( chr(39) ||  uic || chr(39) , ',') within group (order by uic) || ')'  
into v_FILTER               
from ( select distinct uicf.uic           
        FROM OWSADHOC.ADHOC_UIC_FILTER UICF
                 INNER JOIN OWSADHOC.ADHOC_USER_ROLE  UROLE
                    ON UICF.ROLE_NAME = UROLE.ROLE_NAME
                 inner join OWSADHOC.ADHOC_USER_DATA uname
                    on uname.USER_ID = UROLE.USER_ID
        WHERE UROLE.ROLE_NAME in (:P_LoggedInUserRoles)  
         and  uname.JASPER_ACCOUNT = :P_LoggedInUserName
         )
      ;   
      DBMS_OUTPUT.PUT_LINE ('GENERATED FILTER: ' || V_FILTER); 
      end;

v_FILTER is reserve reporting_unit_code IN ('86749')

This works fine when P_LoggedInUserRoles has only one value. However, if I have a comma separated list in the variable, it is not working. I've tried defining expressionList as an array. PL/SQL doesn't compile using that method.

Is the expressionList constrained to the use of literal values if one has more than one value? I can't find that explicit statement in any manual, but I haven't been able to define the expressionList as a variable that is a collection of values.

Paul
  • 1,085
  • 12
  • 20
MARIAN
  • 11
  • 3

1 Answers1

1

No, you cannot, but you're quite close

A comma separated list will make the condition look like this:

WHERE UROLE.ROLE_NAME in ('Jane, Andrew, Jill')

and return an empty resultset. While what you're looking for is

WHERE UROLE.ROLE_NAME in ('Jane', 'Andrew', 'Jill')

It's quite clear that the number of bind variables will have to differ from one call to another as the lists have different length. Sorry, that's not something we can do easily. You'd need something called Dynamic SQL method 4 and that seems a bit of an overkill.

There's an easier way though. We're gonna parse the input string of comma separated values into a resultset. We're gonna do that in a subquery and we can use subqueries in IN.

Here's how we do it

WHERE UROLE.ROLE_NAME in (
  SELECT NAME
  FROM (
    SELECT regexp_substr(:P_LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL
    FROM   DUAL
    CONNECT BY LEVEL <= regexp_count(:P_LoggedInUserRoles, ',') + 1)
  ORDER BY LVL)

I shamelessly stole the regexp from this question: Oracle- Split string comma delimited (string contains spaces and consecutive commas) so you might want to go ahead and upvote the answer there.

Now the downside is of course that we'll be doing this CONNECT BY trick all the time even if we received a single name, but that's a topic for another time.

Paul
  • 1,085
  • 12
  • 20