Oracle: spool data to different files depending on where condition (loop)
SQL&gt; create table t1 (c1) as select mod(rownum, 4) from dual connect by level &lt;= 12;


Just to have some sample data.

$ cat run.sql
set heading off feedback off echo off termout off pagesize 0
spool select.sql
select distinct 'spool c1_' || c1 || '.txt ' || chr(10)|| 'select c1 from t1 where c1 = ' || c1 || ';' || chr(10) || 'spool off' from t1 order by 1;
spool off
@select.sql
!rm select.sql
exit

$ sqlplus -s bp/bp @run.sql


And the result:

$ grep "" *txt
c1_0.txt:         0
c1_0.txt:         0
c1_0.txt:         0
c1_1.txt:         1
c1_1.txt:         1
c1_1.txt:         1
c1_2.txt:         2
c1_2.txt:         2
c1_2.txt:         2
c1_3.txt:         3
c1_3.txt:         3
c1_3.txt:         3