Oracle: spool data to different files depending on where condition (loop)

Question

I am trying to get my way around something like this

declare
 filename varchar2(100);
begin
 for condition in (select fieldN from mytable) loop
 filename := 'data_for_value'||condition.fieldN||'txt';
 spool filename
 select some_data from some_table where fieldN = condition.fieldN;
 spool off
 end loop;
end;

In order to export different sections of data from a table onto different files but I just cannot get it right, I have read this and tried to do something similar in consequence:

set serveroutput on
set pagesize 0
set numwidth 2
set trimspool on
DECLARE
 filename varchar2(100);
BEGIN
 for field2 in (select field2 from blau2) loop
 filename := 'COST_TRAN_BASE '||field2.field2||'.txt';
 spool "run_query.sql" REPLACE
 select distinct 'spool "' || filename || '.txt"' || chr(10) ||
 'select field1, field3 from blau2 where field2 = ' || field2.field2|| ';' || chr(10) ||
 'spool off' cmd
 from blau2;
 spool off
 @"run_query.sql"
 end loop;
END;

But to no avail. I get the feeling that I am not allowed to use the spool command where I am trying to (I recently started working in the Oracle environment and the line between SQL*Plus and PL/SQL is still a bit blurry (you can kill me now for my ignorance :') ))

Am I just struggling against the impossible? D:

Edit:

with the initial help of Balazs Papp and my follow up investigations I came up with this system: Using this file:

basic_script.txt
-----------------
SET PAGESIZE 0
SET TRIMSPOOL ON
spool RUN_QUERY.sql REPLACE
select distinct 'set heading off' || chr(10) ||
 'spool "BLAU2 ' || field2 || '.txt"' || chr(10) ||
 'select field1||chr(9)|| field3 from blau2 where field2 = ' || 
field2 || ';' || chr(10) ||
 'spool off'
 from blau2;
spool off
-----------

And this other file:

basic_script_condition.sql:
-----------
set feedback off
set trimspool on
set recsep off
set serveroutput on
col some_column new_value script_name
select decode((select count(*) from (select distinct FIELD2 from BLAU2)),1,'exit.sql','RUN_QUERY.sql') some_column from dual;
@&script_name
-----------

Calling them from command line in a simple .bat program:

basic_launcher.bat:
-------------
echo exit | sqlplus lmartin/lmartin @"BASIC_SCRIPT.txt"
echo exit | sqlplus lmartin/lmartin @"BASIC_SCRIPT_CONDITION.sql"
-------------

A script for exporting the different sections of data in the BLAU2 table is generated and executed only if there is data to export (if there is no data to export, basic_script would generate a .sql file with no rows selected as its content, possibly repeated N times, and the execution would fail, hence the basic_script_condition.sql controlling this behavior).

Answer 1

SQL> create table t1 (c1) as select mod(rownum, 4) from dual connect by level <= 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

Answer 2

spool is a SQL*Plus internal command. In your example you are trying to use spool inside a PL/SQL block. The spool command is unknown to the PL/SQL language.

The best option to achieve what you want (write to different text files inside PL/SQL) is to use the UTL_FILE (Oracle 9i and beyond) Oracle internal package.

http://oracle-base.com/articles/9i/utl_file-random-access-of-files-9i

• Feillen: Thanks for the explanation, I had my suspicions about that hehe. I knew of UTL_FILE but since it requieres special privileges I was reluctant to use it