Using the Oracle spool command

The "spool" command is used within SQL*Plus to direct the output of any query to a server-side flat file.

SQL> spool /tmp/myfile.lst

Becuse the spool command interfaces with the OS layer, the spool command is commonly used within Oracle shell scripts. 

You have several options for writing conditional messages to a SQL*Plus spool file.  I would just check the spool file size after you execute it:

$ORACLE_HOME/bin/sqlplus system/manager<<!
spool myfile.txt
select owner,object_type,object_name
from dba_objects
where status='INVALID';
spool off
exit
!

if [ `cat myfile.txt|wc -l` -lt 1 ]
then echo Check Status OK > myfile.txt
fi

 

Writing dates into spool comands

For example, say that you want to run a SQL*Plus script where it spools to a file name that contains SYSDATE.  How do you add SYSDATE to a spool file name?

name.   You can select SYSDATE into a variable name using the DUAL pseudo-table, and execute the name as part of the spool command:

sqlplus . . .
select 'invoice_'||to_char(sysdate, 'yymmdd') spoolname from dual
spool '&spoolname'
select stuff . . . .
spool off

Using the Oracle spool append command