For each line, execute a function / procedure

Question

I would like to iterate over the list of lines and execute a function / procedure with each line as an argument.

What is the best alternative to the following generic code (since this is not legal):

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

I guess there might be a template for this.

Answer 1

Just for a complete, clean 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> 

In this case, the sys.dbms_debug_vc2coll extended data type is sys.dbms_debug_vc2coll , which has a fairly broad definition ...

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

... so, as Gary says, you can make yourself known. Especially if your lines are short and you have a lot of them.

Answer 2

I usually 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 3

Using:

 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 factoring subquery (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 4

The answer here depends on where the strings come from. In a non-database language, you will probably somehow get the rows in the array and then go through the array, as you have already illustrated. The question is, is the list of rows hard-coded or do you select them from the database table?

The OMG Ponies solution will work, but it may include an unnecessary choice. You might be better off using a PLSQL table or varrays - as I said, it depends on how you get the lines in your program that you need to process. The following is an example of 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

 DECLARE -- 1. declare a list type TYPE STR_LIST_TYPE IS TABLE OF VARCHAR2(15); -- 2. declare the variable of the list V_STR_VALUES STR_LIST_TYPE; -- 3. optional variable to store single values V_STR_VALUE VARCHAR2(15); BEGIN -- 4. initialize the list of values to be iterated in a for-loop V_STR_VALUES := STR_LIST_TYPE('String 1','String 2'); -- 5. iterating over the values FOR INDX IN V_STR_VALUES.FIRST..V_STR_VALUES.LAST LOOP -- 6. accessing the value itself V_STR_VALUE := V_STR_VALUES(INDX); END LOOP; END; 

Answer 6

 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;