Unanswered: how to set spool off of two files one by one.
I am executing following sql script:
1 SET SERVEROUTPUT ON SIZE 100000
2 SET VERIFY OFF
3 SET FEEDBACK OFF
4 spool abc1.sql
5 spool abc2.log
7 test VARCHAR2(40);
10 test := 'yes';
12 IF test <> '&test2' THEN
13 dbms_output.put_line ('Testing block');
14 END IF;
18 spool off
20 spool off
I want to stop the spooling of file abc.sql at line 18 and spooling of abc2.log
at line 20.
In this file @test2 is a substitution variable defined before executing this script.
You can only spool to one file at a time. Yes, Oracle does support dual feeds, but one is "standard output" (i.e., your monitor) and the other is a file you name with the SPOOL command. Unless you redirect standard output (not easy to do with a compiled product like SQL*Plus, you are stuck.
Mind the presence of a SPOOL command causes an implicit SPOOL OFF, ending your first SPOOL command.