oracle - PL/SQL: Passing an 'array' of strings as argument to sql -

using plsql, want run test.sql multiple times, each time pass in different argument test.sql , spool result different file.

the file name may not have relation argument being passed.

i'm hoping can define 2 'arrays'; 1 filename spool to, , other argument value.

declare my_types sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('typea', 'typeb', 'typec'); my_filenames sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('filenamefora', 'filenameforb', 'filenameforc'); begin   r in my_types.first..my_types.last   loop     --dbms_output.put_line(my_types(r));     --dbms_output.put_line(my_filenames(r));         spool my_filenames(r)     @test.sql my_types(r);         spool off   end loop; end; / 

for spool, says encountered symbol "my_filenames" when expected := . < @ % ;. also, looks test.sql taking argument put in literally, 'my_types(r)' instead of expected 'typea'. if there different , easier way of doing in plsql let me hear it. thanks.

to work (clunky, ugly), have have use plsql generate sql scripts calls th esql script(s) trying test. e.g.

set serveroutput on spool run_it.sql declare    my_types sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('typea', 'typeb', 'typec');    my_filenames sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('filenamefora', 'filenameforb', 'filenameforc'); begin   r in my_types.first..my_types.last   loop     dbms_output.put_line('spool ' || my_filenames(r) );     dbms_output.put_line('@test.sql ' || my_types(r) );     dbms_out.put_line ('spool off' );   end loop; end; / spool off @run_it.sql 

not tested/compiled. hope idea.

with above run_it.sql should like:

spool filenamefora @test.sql typea spool off spool filenameforb @test.sql typeb spool off . .