CODEWITHSUNDEEP
oracle-databaseplsql
Frenchi In LA
Frenchi In LA

Reputation: 3169

Oracle Loop delimited list to check if record exist

I'm new in PL/SQL, and Got a code from an old program that check if a record exist in a table something like :

     oRetValue := ' ';
  SELECT f1
  INTO oRetValue
  FROM t1
  WHERE w1='w1' 
  AND code  = iCode;

it was ok before, but now the application has more than 500 rows to verify. I'd like to pass a string with all the code separated by comma, and the procedure will loop and return all icode not found. Any help would be greatly appreciated.

Upvotes: 0

Views: 559

Answers (1)

Simas.B
Simas.B

Reputation: 754

Try this:

select f1
  into oretvalue
  from t1
 where w1 = 'w1'
   and code in
       (select p_code
          from (select level as id,
                       regexp_substr(icode, '[^,]+', 1, level) as p_code
                  from dual
                connect by regexp_substr(icode, '[^,]+', 1, level) is not null));

Upvotes: 1

Related Questions