SQLPlus Spool Issue

Question

Simple problem. For audit purposes, a new set of steps was initiated when we sign into an Oracle DB using SQLPlus. At the first SQL> command prompt, we immediately activate spool to a file with a descriptive filename. The 2nd thing done is issue a !date at the SQLPlus prompt. This brings in the host's date at the OS level. Then start issuing SQL commands. When we finish, we do a spool off. That way we have nice full auditable files.

Not quite. The problem is we get this in the spool file so we have no date info: SQL> !date

SQL>

No date information. It shows on the screen, but not in the spool file. Yes, I know we can use sysdate at the command prompt, but management wants !date. And no, we have vanilla settings in our glogin.sql file. Nothing fancy. To keep management happy, any ideas why SPOOL is not picking up this date info? No date information. And yes, we could use sysdate in SQL, but we have

Answer 1

From the documentation

Stores query results in a file, or optionally sends the file to a printer.

Shell commands executed with ! are not queries, so their output is not sent to the file.

To do what you want, use shell redirection to send its output to the file before spooling. Then use the APPEND option to append the query output.

SQL> !date > filename.txt
SQL> SPOOL filename.txt APPEND
SQL> SELECT ...
SQL> SPOOL OFF