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:
if [ `cat myfile.txt|wc -l` -lt 1 ]
then echo Check Status OK > myfile.txt
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
select stuff . . . .
Using the Oracle spool append command
if you spool out a file that does not exist, SQL*Plus creates a new file. SQL*Plus replaces the existing file if the file name already exists. In Oracle 10g, the SPOOL command has a new APPEND option. Now, you can append new SQL*Plus output in an existing file.