SQLPlus – spool name with embedded timestamp

To be able to create a spool file, with filename embedded with timestamp:

col sysdt new_value sysdt_var
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
DEFINE LOGFILE=run_filename_&&sysdt_var..log
spool &&LOGFILE

Updated 10/03/2014 – the SQL had some special chars, so when I copied it to my script, it failed. Corrected that above. I have also changed it store the spool file name in a DEFINED variable and use it throughout the script.

Also, where it has .log, it actually needs .. (two dots).

Update 12/07/12

This was confusing, as I had the same name for column name and the SQL variable created by new_value. I’ve renamed the two accordingly. This works.

Also, I posted about spooling to HTML some time back. You can combine the two, of course:

col spoolname new_value spoolname_var
SELECT 'tablist_'||to_char(sysdate, 'yymmdd_hh24miss') || '.html' spoolname
FROM dual;

HEAD"<TITLE> Session Stats</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -

spool &&spoolname_var

SELECT username, logon_time, status FROM v$session;
spool OFF;

creates a nice HTML file with a list of all users currently logged on to the database. This is saved in a HTML file with timestamp in it’s name!


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s