Oracle PL/SQL loop through pre-defined values - The easiest way

How many times did you need to loop iterate through a set of predefined values using PL/SQL language?

And how many times did you face examples addressing complex paths using "type of varchar2" kind of tables or PL/SQL arrays?

I bet .. countless times ... right?

Thing is, even when those examples deliver what we need, they are just "old-school" coding style.

Here’s a simple trick on how to make a PL/SQL loop iterate through a set of predefined values.

Basically, this trick:

• Uses regular expressión to extract each word from a string
• Each word in the string is delimited by a blank space.
• And use "connect by" statement to build a hierarchical query.

Here we go with the code:

procedure iterateWord
is
inputString varchar2(200) default 'wordA wordB wordC'; 
pos number default 0 ; 

cursor v_word is
  select regexp_substr(inputString, '[^ ]+', 1, level) as str from dual
  connect by regexp_substr(inputString, '[^ ]+', 1, level) is not null; 
begin

     for i in v_word
     loop  
         pos := pos + 1;
         dbms_output.put_line(pos||': '||i.str
     end loop;

end iterateWord;

You can use any other given delimiter, for instance, let' say you want to use commas then the regular expression will be like the following:

cursor v_word is
  select regexp_substr(inputString, '[^,]+', 1, level) as str from dual
  connect by regexp_substr(inputString, '[^,]+', 1, level) is not null; 

I found this approach pretty much concise and effective.

Keep it handy as I do!