SQL*Plus > Is current date available in a system variable or some sort?

Question

I'm regularly spooling results or messages of processing programs to a file. To prevent spool files getting overwritten, I use date and time in spool filenames. Using the construction COLUMN {var1} NEW_VALUE {var2} and a SELECT-statement is a lot of code for just having the current date. When spooling I want to suppress output of this construction, since it is not needed in a spool file. That takes extra coding.

Is the current date and time available in SQL*Plus?

I'm using this code:

SET echo OFF
SET feedback OFF
SET termout OFF

COLUMN bestandsnaam NEW_VALUE _vv_waarde

SELECT to_char ( sysdate, 'yyyyddmm_hh24mi') bestandsnaam
FROM DUAL;

SPOOL Verwerking_&_vv_waarde.

{ processing }

SPOOL OFF

But I'd like to have sysdate or something like that available in SQL*Plus so I could code something like:

SPOOL Verwerking_#sysdate#

{ processing }

SPOOL OFF

Spool file will be named 'Verwerking_20190409_1904'.

Answer 1

Well, there's a predefined _DATE variable (docs). But since all SQL*Plus variables are character datatype, you have to set NLS_DATE_FORMAT to the string you want.

alter session set nls_date_format = 'yyyyddmm_hh24mi';
SPOOL Verwerking_&_DATE.
...
SPOOL OFF