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).
Best Answer
Just to have some sample data.
And the result: