Oracle Loop delimited list to check if record exist

Question

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.

Splitting a string into separate values is going to be the hard part, as it's not native function in PL/SQL the way it is in JavaScript etc. Couldn't you use a collection? — William Robertson, Aug 24 '16 at 16:48
show your code to select with 'where comma separated string' clause — Анатолий Предеин, Aug 24 '16 at 16:57
Yes I can use collection. the call is coming from C# method, so I can make all kind of parameters. — Frenchi In LA, Aug 24 '16 at 20:20
Throw them in a global temporary table and query `select f1 from temp_table minus select f1 from t1 ... ` — Martin Schapendonk, Aug 24 '16 at 20:56
I would not recommend passing a comma separated string as an input parameter to the procedure. As Martin has suggested it would be better if you could insert the iCode values in a global temporary table. Also, in what format are you expecting the procedure output. Again here I would not recommend a comma separated string value. You procedure could insert the values which were not found in another global temporary table which you can use in your code. — phonetic_man, Aug 25 '16 at 03:58
How do you want to 'return' these values? Should this be a function that returns a collection, or do you want to process each item further within the procedure? — William Robertson, Aug 25 '16 at 16:11
I opted for passing collection, then using for while. I'm looking for the way to pass an array from C# using OracleCommand. — Frenchi In LA, Aug 25 '16 at 16:30

score 1 · Accepted Answer · answered Aug 29 '16 at 14:12

1

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));

answered Aug 29 '16 at 14:12

Simas.B

724
1
13
31

REGEX's of the format `'[^,]+'` (unfortunately commonly seen for parsing delimited lists) fail when there is a NULL element and should be avoided. Please see http://stackoverflow.com/questions/25648653/regex-to-select-nth-value-from-a-list-allowing-for-nulls for an alternative that handles NULLs. – Gary_W Aug 29 '16 at 14:17
Oops meant to share this link which gives a better explanation of the regex: http://stackoverflow.com/a/31464699/2543416 – Gary_W Aug 29 '16 at 14:20

Oracle Loop delimited list to check if record exist

1 Answers1