Hi,

I have a working SQL script which consists of some report formatting and a select statment. My problem is that this script is going to be set as a daily automated task on a solaris system which will generate a report. However, I am having extreme difficulty with making the report file name as the previous date in the format YYYYMMDD. (For example, today's date is August 6, 2003 and the automated task runs and it should generate a report with file name 20030805.lst) How do I do this? I have minimal knowledge with PL/SQL. I really don't have a clue how to write a procedure that can store sysdate-1 (which is essentially the previous day's date) in a variable for me to use in the SPOOL statement. I need to get this done asap and I really need help. I have looked at online documents but I don't understand how the block structure works.

How can I generate this file with the specific file name with my existing SQL statement?

Thanks in advance!!

Did you try:

col mydate new_value _today
break on report
select to_char(sysdate,'YYYYMMDD') mydate from dual;
spool &_today
...etc...


PS: spool command cannot be used in PL/SQL, only in SQL Plus.

Thank you! It works perfectly....I've never thought of doing it this way!!