Parsing Comma Separated string in WHERE Clause of SQL Statement in Oracle Forms

In some cases, we get a comma separated string as output (say from another select statement or block) that we would need to pass to the IN clause of a Select statement.

Requirement :

SQL Statement need to process a String value (having multiple Text values seperated by Commas) in the WHERE Clause at runtime. 

This String value is generated dynamically at run-time based on certain criteria and processing logic.

Illustration:

Oracle Forms example

1.  Setting Profile_value based on certain condition.

 BEGIN

   if <<Condition1 = True>>  

   then

            P_PROFILE_VALUE := 'Profile-1, Profile-2';

   

    Elsif <<Condition2 = True>>  

    then

            P_PROFILE_VALUE := 'Profile-3, Profile-4';   

    Else

            P_PROFILE_VALUE := 'Profile-5';   

    End If;

Now, assuming the  Condition1 = True then we will have the string as below :

      P_PROFILE_VALUE := 'Profile-1, Profile-2';

2. If the above value is passed to a IN Condition of a WHERE clause  , it would be treated as a SINGLE string of value 'Profile-1, Profile-2'  and the query will Not check for values Profile-1 and Profile-2 independently (ie) it would fetch records that have the value 'Profile-1, Profile-2' .

Select VALUE  from xx_test_table where NAME  IN(P_PROFILE_VALUE );

No rows selected

3. The desired result, can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause. 

Oracle provides regexp_substr  function, which comes handy for this scenario. 

SELECT  REGEXP_SUBSTR('Profile-1,Profile-2',

                      '[^,]+',

                       1,

                       LEVEL

                       )

 FROM DUAL

CONNECT BY LEVEL <= LENGTH ('Profile-1,Profile-2')

                   - LENGTH (REPLACE ('Profile-1,Profile-2',

                                     ',') )  + 1

Output:

Profile-1

Profile-2

4. Substitute the above logic in query as shown below to get the desired results :

               Select VALUE  from xx_test_table where NAME  IN

                        (SELECT  REGEXP_SUBSTR(P_PROFILE_VALUE,

                                             '[^,]+',

                                             1,

                                             LEVEL

                                            )

                                 FROM DUAL

                           CONNECT BY LEVEL <=

                                           LENGTH (P_PROFILE_VALUE)

                                         - LENGTH (REPLACE (P_PROFILE_VALUE,

                                                            ',')

                                                  )

                                         + 1);

     End;

The IN Clause above query will now check for both/either 'Profile-1' and 'Profile-2' .