We help IT Professionals succeed at work.

Sql script to spool / save to a disk file (in YYYYMMDD.spl) a select output

sunhux
sunhux asked
on
I'm no sql scripting person & need an sql script to
print out (ie save to disk file) the list of dormant
users from our Oracle DB for last 30 days:

select * from ACTION_LOG where ACTION_DTTM >= trunc(sysdate-30)
  and ACTION_CD in ('SORAS') order by ACTION_DTTM DESC;

Will need someone to prefix it with
set spool ....   DormantAccts_YYYYMMDD.spl

to save output to a spool file which we'll email out
Comment
Watch Question

View Solution Only

Senior Oracle DBA
Commented:
From memory, didn't test it...
col dt new_value dt
select to_char(sysdate, 'yyyymmdd') dt from dual;
spool DormantAccts_&dt.spl

Open in new window

Or use the select to generate the full file name
col dt new_value dt
select 'DormantAccts_' || to_char(sysdate, 'yyyymmdd') || '.spl' dt from dual;
spool &dt

Open in new window

slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
johnsone's memory is much better than mine and he should get the points.

Small correction:  You need two periods when using a variable like that:
spool DormantAccts_&dt..spl
johnsoneSenior Oracle DBA

Commented:
I've done it a few times.....