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;

shahkalpesh · Accepted Answer · 2009-07-21 03:14:05Z

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

answered Jul 21, 2009 at 3:14

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rob Heusdens · Accepted Answer · 2017-02-14 20:41:37Z

0

You can create a dynamic name for your spool file as follows:

col datum new_value today
select to_char(sysdate,'YYYYMMDD') as datum from dual;

set heading off
set linesize 128
set echo off
set feedback off

spool today_&today..log
rem Your query here ...     
select ...
/
spool off

answered Feb 14, 2017 at 20:41

Rob Heusdens

1555 bronze badges

Collectives™ on Stack Overflow

SQLPlus variable definition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related