Using Spool in Oracle 11g
I want to use spool functionality in Oracle 11g.
I want the entire output to be spooled into a file and a subset of the output to be spooled into a separate file.
In the below example I want temp_1.txt to contain data from A,B,C,D and E
In temp_2.txt I want data only for D.
sqlplus user/pass@inst
spool on temp_1.txt
select * from A;
select * from B;
select * from C;
spool on temp_2.txt
select * from D;
spool off temp_2.txt
select * from E;
exit;
Note:- Since this is very old legacy code I can't write a separate sqlplus session for D or re order the selects.
Answer
How about doing it all in the sqlplus script. The host commands will need to change if you ever run on a different system (ie. Microsoft Windows). But, they would be required to change in a shell script as well.
spool all_queries.txt
select * from A;
select * from B;
select * from C;
spool off
spool only_d_query.txt
select * from D;
spool off
host cat only_d_query.txt >>all_queries.txt
spool all_queries.txt append
select * from E;
spool off