Re: Unique Filename in SQLPlus Spool

From: <jl34778_at_corp02.d51.lilly.com>
Date: 19 Nov 93 20:00:03 EST
Message-ID: <1993Nov19.200003.1_at_corp02.d51.lilly.com>

In article <1993Nov19.082108.1_at_mcvax2.d48.lilly.com>, rzx2122_at_mcvax2.d48.lilly.com writes:

> Jeroen Hermans, Nikhef-H, Amsterdam (the Netherlands) writes:
> 

>> select name, adres
>> from person
>> where name like upper('&NAME%');
>>
>> When I start this script the output is send to the file 'namelist'.
>> When I start the script again the file 'namelist' will be deleted
>> and exchanged by a new list. Can I make a unique name 'namelist'
>> by putting a timestamp behind it or another way.
>> Now I have to make a list leave SQLPlus move the file to another
>> name start SQLPlus again and start the script again.
>> Is there another sollution within SQLPlus??????
>
Tom Harleman gave one solution that requires creating a temporary script file and then running that script. The following example uses the NEW_VALUE option on the COLUMN statement to load a SQLPLUS variable. I think it is a bit more elegant.

Tom, if it's any consolation, my disclaimer isn't as pretty as yours.... (for those of you reading this, Tom and I know each other, so I'm not being  obnoxious to a stranger.)

---

rem
rem each time this script is run, a different spool file is created, in the rem format SPOOLFILEyymmddhh24miss.OUT. rem
column spoolname new_value spoolname noprint select 'spoolfile'||to_char(sysdate,'yymmddhh24miss')||'.out' spoolname

       from dual;
set pagesize 0
set feedback off
set termout off
set echo off
spool &spoolname
select * from cat;
spool off

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285

Received on Sat Nov 20 1993 - 02:00:03 CET