From within SQL spooling output file whose name uses sysdate

[Dave Walley]

Hi.

I would like to spool out a file from within sql that uses as its name the time from sysdate.

eg. SYSDATE is now = 16:15 Wed 21 Jan 2004

I would like to spool a file whose name is
report_210120041615.lst. Can anyone please help.

Thanks for your help.

Dave

[Hoefnix]

if you now how to run a query from command line it's easy. I am not sure about the context, something like:

export file_name=report_`date`.lst
sqlplus user/passwd@db $file_name

myquery.sql is a file that contains your query.

See also man page of date for your favorite date-format.

Regards,

Peter

[Graham Cameron_1 · Accepted solution]

Dave.

It's a bit fiddly.
Something along these lines would do the trick...

col spooldest new_value v_spooldest noprint
select 'report_'||to_char(sysdate,'DDMMYYYYHHMM') spooldest from dual ;
spool &&v_spooldest

-- your sql here

spool off

-- Graham

[Kyri Pilavakis]

I found it easy to put the job in a script and cron it. Simple example is:

sqlplus -s kp/exa@db << EOF
spool /tmp/kak/hel_new/sort.lst
@/tmp/kak/hel_sql/check_sort_types.sql
spool off
exit
EOF

# Renaming the File and adding date for it
#
mv /tmp/kak/hel_new/sort.lst /tmp/kak/hel_new/sort.lst.`date +%a%d%b`
#
if [ -r "/tmp/kak/hel_new/sort.lst.`date +%a%d%b`" ]
then
lp /tmp/kak/hel_new/sort.lst.`date +%a%d%b`
else
echo " Cant Locate File /tmp/kak/hel_new/sort.lst.`date +%a%d%b`" > /tmp/kak/hel_new/sort.err
fi

You can play around with the date part i.e
sort.lst.`date +%a%d%b` till u get what u are after.

kyris

[Arturo Galbiati]

Hi Dave,
this is the way I usually use to have in the sp[ool file name the name of the instance and the sysdate:

/* Define the name of the spoolfile */
col spool_file_name new_value spool_file_name noprint
col file_name new_value file_name noprint
col file_date new_value file_date noprint
select ''||substr(global_name,1,instr(global_name,'.')-1)||'_' file_name from global_name;
select to_char(sysdate,'yyyyymmdd') file_date from dual;
select '&file_name'||'&file_date'||'.log' spool_file_name from dual;
spool &spool_file_name

.... your sql statement ...

spool off

I hope it help.
Art