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’ .


  • January 9, 2015 | 23 views