Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Zoliswa.

Asked: December 13, 2002 - 7:51 am UTC

Last updated: August 01, 2022 - 4:24 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hi Tom

When spooling the file on PL/SQL, i would like the file name to have sysdate attached to the filename and it should change dynamicaly..
e.g.PM-INT-20021129_FLASHNOTIFICATIONS.TXT

Thanx
Zoliswa


and Tom said...

column dt new_val X
select to_char(sysdate,'yyyymmdd') dt from dual;
spool pm_int_&X._flashnotications.txt
......

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I think

Sikandar Hayat Awan, December 13, 2002 - 10:37 pm UTC

I think that you should add time to avoid duplicate file name. You may create two files on the same day.

adding sysdate on the file name

Zoliswa, December 17, 2002 - 1:19 am UTC

I thank you very much as it worked like magic.......


adding sysdate on the file name

Zoliswa, December 17, 2002 - 7:33 am UTC

HI Tom

I apologise for bothering you ...
As the answer you gave me is excellent, it does attach date to the filename, but it also displays it(date)on the file of which i don't want it to do that, there's already a statement that displays date..how can i add this statement on my query without the date being displayed on the file, but only on the filename...

Thanking you in advance...




Tom Kyte
December 17, 2002 - 8:00 am UTC

I don't know what you mean.

You run the query to get the date to START spooling (hence, you are not spooling right now). so, how could this date "display on the file"?



Thomas Barry, December 17, 2002 - 8:24 am UTC

I think that when he says 'on the file', he means 'in the file'. So perhaps appropriate placement of spool on/spool off statements would help?

Zoliswa, December 17, 2002 - 9:38 am UTC

i apologise for the misunderstanding Tom, its such that when i run the format alone that you gave me in PL/SQL, it works fine, but when i add it on my query, it adds the date on the text filename of which is fine because that's the way i wanted it to be, but it also displays it at the begining of my result query as 20021217 of which i don't need it there as it has already done it's job of adding it to the text filename...i'll try to check as to why is returning the date on the result query......
e.g
When i run the ff query:

column dt new_val X
spool c:\test\pm_int_&X._flashnotications.txt
select to_char(sysdate,'yyyymmdd') dt from dual;
SELECT '<001>'||'PMFLASH-'|| to_char(sysdate, 'YYYYMMfmDDfm-HHSS' ) FROM DUAL;
select '<005>'||'CoCT FLASH' FROM DUAL;


Result query:

20021217 <001>PMFLASH-20021217-0440
<005>CoCT FLASH

On the Result query i don't need the first part of - 20021217, i need only the second part <001>PMFLASH-20021217-0440,<005>CoCT FLASH ...


Thanx
Zoliswa



A reader, December 17, 2002 - 9:48 am UTC

Zoliswa,

Re-read what Tom said. Here is it again for you:

column dt new_val X
select to_char(sysdate,'yyyymmdd') dt from dual;
spool pm_int_&X._flashnotications.txt
......

Why are you putting the select statement after your spool? That should be before you start spooling.

Tom, I just stepped-in to save you from wasting your time to repeat this silly thing. You sure have more important stuff to do.

Zoliswa, December 19, 2002 - 3:49 am UTC

i apologise for wasting your time Tom, i misread....

Reader - you really didnt have to be that harsh or rude everyone makes mistakes, i misread, so the first statemant of re-read was OK, the rest that you mentioned was uncalled for....

Re: Adding sysdate on the file name

Ken Madsen, March 03, 2004 - 10:14 am UTC

Right on the money! Was the exact solution I was looking for.

BTW the thread needed a final answer. If you wrap the select statement with termout controls it will supress the select statement output on the screen.

column dt new_val timestring
set termout off
select to_char(sysdate, 'YYYYMMDD-HH24MISS') dt from dual;
set termout on

spool submission&2._&timestring..log

Excellent

F.I., July 08, 2004 - 6:21 am UTC


Spool sql query with Date parameter

kalis, September 07, 2016 - 3:02 pm UTC

column dt new_val X
select to_char(sysdate,'yyyymmdd') dt from dual;
spool pm_int_&X._flashnotications..txt
......

A reader, December 17, 2019 - 1:46 pm UTC

Thanks, It works.

Updated solution

Casey Gierke, July 29, 2022 - 8:51 pm UTC

This is what ended up working for me. I think the key difference from what I saw here is double ampersand (@@)

set encoding windows-1252;
set sqlformat csv;
set define on;
column dt new_val date;
select to_char(sysdate, 'yyyy-mm-dd') dt from dual;
spool C:\Users\username\Desktop\new-query-&&date..csv;

Note, it's probably poor practice to use date as my variable name since I believe that reserved syntax but it worked to dump an outfile with the current date. It probably won't work if there are spaces in your path.
Chris Saxon
August 01, 2022 - 4:24 pm UTC

Thanks for sharing - yes using reserved words for variables is in general a bad idea!