For each string, execute a function/procedure

Question

I'd like to loop through a list of strings and execute a function/procedure with each string as the argument.

What's the best alternative to the following generic code (since it's not legal):

set serveroutput on;  
begin
    FOR r IN ('The', 'Quick', 'brown', 'fox')
    LOOP
        dbms_output.put_line( r );
   END LOOP;
end;

I assume there might be pattern for this.

Answer 1

Use:

SELECT package.your_function(x.col)
  FROM (SELECT 'The' AS col
          FROM DUAL
        UNION ALL
        SELECT 'Quick'
          FROM DUAL
        UNION ALL
        SELECT 'brown'
          FROM DUAL
        UNION ALL
        SELECT 'fox'
          FROM DUAL) x

Oracle 9i+, Using Subquery Factoring (AKA CTE)

---

WITH list AS (
  SELECT 'The' AS col
    FROM DUAL
  UNION ALL
  SELECT 'Quick'
    FROM DUAL
  UNION ALL
  SELECT 'brown'
    FROM DUAL
  UNION ALL
  SELECT 'fox'
   FROM DUAL)
SELECT package.your_function(x.col)
  FROM list x

Answer 2

set serveroutput on;  
begin
   dbms_output.put_line('The');
   dbms_output.put_line('Quick');
   dbms_output.put_line('brown');
   dbms_output.put_line('fox');
end;

Answer 3

I generally use my own collection type, but you can use the built-in sys.dbms_debug_vc2coll

select column_value from table(sys.dbms_debug_vc2coll('The', 'Quick', 'brown', 'fox'));

[I incorrectly had column_name not column_value. Thanks for the correction]

Answer 4

The answer here depends on where the strings come from. In a non 'database language' you would probably get the strings into an array somehow, and then loop over the array, as you have illustrated above. The question is, is that list of strings hardcoded, or are you selecting them from a database table?

OMG Ponies solution will work, but it involves a possibly needless select. You may be better using PLSQL table or varrays - as I said, it depends on how you get the strings into your program that you need to process. Here is an example using plsql tables:

declare
  type myarray is table of varchar2(255) index by binary_integer;
  v_array myarray;
begin
  v_array(v_array.count + 1) := 'The';  
  v_array(v_array.count + 1) := 'quick';
  v_array(v_array.count + 1) := 'brown';
  v_array(v_array.count + 1) := 'fox';
  for i in 1..v_array.count loop
    dbms_output.put_line(v_array(i));
  end loop; 
end;
/

Answer 5

Just for completeness, a pure PL/SQL solution.

SQL> set serveroutput on
SQL>
SQL> declare
  2      my_array sys.dbms_debug_vc2coll
  3          := sys.dbms_debug_vc2coll('The', 'Quick', 'brown', 'fox');
  4  begin
  5     for r in my_array.first..my_array.last
  6      loop
  7          dbms_output.put_line( my_array(r) );
  8     end loop;
  9  end;
 10  /
The
Quick
brown
fox

PL/SQL procedure successfully completed.

SQL>

This uses the preclared sys.dbms_debug_vc2coll datatype, which has quite a generous definition ...

SQL> desc sys.dbms_debug_vc2coll
 sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)

SQL>

... so, like Gary says, you may wish to declare your own. Especially if your strings are short and you have lots of them.