Filenames with sysdates

[KyraVald]

I realize this may be a simple question. And, I hope you can help me with this.

I currently run data files every day, and these files are spooled into a folder with typical file names. "letterout.txt" , "fulfillment.txt" , etc.

Like so:
spool Y:\Back_UP\Letters\letterout.txt
(data extract)
spool off

However, in my recent efforts to send these files via FTP, the customer requires the system dates to be attached to the filename. And unfortunately, I don't know how to do this using the SQL code.

How do I create a file name with the sysdate included? Example: letters04162004.txt OR fulfill0419.txt

Do I have to spool the file first and then rename the text file? Or can the filename be created in advance, when the data is spooled to the folder location?

I'm still fairly new to SQL and have not been able to find any references in the books I have. Any assistance you can provide would be very helpful! Thanks!

Sincerely,

KyraVald
(KyraVald@hotmail.com)

 

[randall2nd]

you should be able to get the date using SYSDATE.

You can format it by doing:
to_char(SYSDATE,'DDMMYYYY') which is 19042004
to_char(SYSDATE,'DDMONYYYY') which is 19APR2004

for example

SELECT 'Y:\Back_UP\Letters\letterout'|| to_char(SYSDATE,'DDMMYYYY') ||'.txt' INTO letterout_name from dual

not sure if that will work in your process but should give you an idea.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[parbhani]

Hi ,

Basic question is, are you doing this on a Unix machine or a Windows SQL*Plus client on your machine.
The solution will varry depending on that.

If you are on Unix machine, use following shell script,

#!/usr/bin/ksh
today_date=`date '+%Y%m%d'`
sqlplus -s <username/password@connect_str> <<EOF
spool $today_date
/** Insert here your existing SQL code **/
/** Following EOF must be at the starting of new line **/
spool off
EOF

Put the above stuff in a shell script and run it. That will work.
If you are on windows, there will be different solution.

Cheers