SQLPlus variable definition

Question

I want to do this in SQLPlus: define a file which contains today's date in its name and spool off the output of a SQL statement to that file. I know how to spool the output of an SQL statement to a file. Just don't know how to declare the file name as a variable containing today's date.

For example, select all the rows with today's timestamp from ERROR table and output the results to a file called TODAYS_ERRORS_YYYYMMDD.log.

I am using a BAT file which invokes SQL plus as follows:

sqlplus -silent user/password@errorDB @c:\temp\error_query.sql

I am able to run the above bat file and output the rows successfully to a file with a static file name, such as TODAYS_ERRORS.log. Just don't know how to declare the file name so that it contains today's date in its name.

This is how my error_query.sql file looks right now:

set feedback off;
set echo off;

spool c:\temp\TODAYS_ERRORS.log

SELECT created_time      AS "Created Time",
       error_severity    AS "Severity",
       error_desc        AS "Error Text"
FROM ERROR
WHERE
  to_date(to_char(created_time,'YYYYMMDD') = to_date(to_char(sysdate,'YYYYMMDD');

spool off;
exit;

Answer 1

SPOOL with dynamic file name.
http://www.lazydba.com/oracle/0__57310.html

Also, DOS command can be written to get the date.
1st link when searching: DOS + Batch + Date -> http://www.robvanderwoude.com/datetime.php