Figuring out how to spool to a file has been easy enough. I am hoping there is an option to write to the text file after each command is written. I am not sure how to communicate the status of a long script to other people on my team. The solution we were going for was to write a log file to a network drive, as the script executes they would be able to follow along.
However, this seems to only write output to the file after the spool off; command at the end of the file.
Is there any way to achieve what we're trying to do, either with spooling a log file or another method?
Here is the code I have so far.
set timing on;
set echo on;
column date_column new_value today_var
select to_char(current_timestamp, 'yyyymmdd_HH24_MI') as date_column
from dual
/
select current_timestamp from dual;
SPOOL 'Z:\log\KPI\secondary_reporting_&today_var..log'
... lots of stuff...
spool off;
Two methods come to mind, depending on what your 'stuff' is.
1) If your code has lots of SQL statements and PL/SQL blocks then you can repeatedly spool for a little while. Use the spool <filename> append statement for this.
SQL> help spool
SPOOL
-----
Stores query results in a file, or optionally sends the file to a printer.
In iSQL*Plus, use the Preferences screen to direct output to a file.
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Not available in iSQL*Plus
2) If you have long running PL/SQL procedures use the UTL_FILE package. See https://docs.oracle.com/html/B14258_02/u_file.htm for more information. This does require some setup and administrative privileges in the database to set up a directory where writing is allowed.