RE: Spooling SQL*Plus output to a unique file name (Unix) - SOLUTION

Thanks very much to everyone who replied! Basically, there were two different Oracle solutions suggested, both of which work quite well:

1. In SQL*Plus, define a column with new_value to hold the spool file name, populate it with a select whatever from dual, referencing the new column name, then SPOOL to the new column name.
2. Use UTL_FILE.

It was also pointed out that there are various Unix shell-related solutions for this, but since my Oracle programming is better than my shell programming, I think I'll go with one of the above Oracle options!

Thanks again,
Patricia

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Patricia
> Clarke
> Sent: Monday, May 01, 2000 7:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Spooling SQL*Plus output to a unique file name (Unix)
>
>
> Oracle 7.3.4, Dynix 4.2.3, SQL*Plus 3.3.4, PL/SQL 2.3.4
>
> I have a PL/SQL script that I am running on the server (Unix) and I am
> spooling the output into a log file. The problem is that I don't want to
> lose the previous output each time I run the script, so I want to
> be able to
> either spool to a unique file name each time (e.g. by appending a
> timestamp
> or the process ID or something to the log file name), or keep the
> same file
> name but *append* the new output to the old instead of overwriting
> everything each time.
>
> I have read the SQL*Plus documentation on the SPOOL command and don't see
> any options for appending output to an existing file instead of
> overwriting,
> so I'm guessing that's not possible...
>
> So, I guess I am looking to do something like:
>
> DECLARE unique_file_name VARCHAR2(18);
> ...
> BEGIN
> ...
> SELECT to_char(sysdate,'YYYYMMDDHHMISS')||'.log'
> INTO unique_file_name
> FROM dual;
> ...
> SPOOL unique_file_name
> ...
> SPOOL OFF
> END;
>
> But, this doesn't work because Oracle doesn't like the SPOOL statements
> within the PL/SQL block, but if I take them out of the PL/SQL
> block, I get a
> log file called unique_file_name.lst rather than 20000501103013.log (or
> whatever).
>
> I'm probably missing something stupid here, but can't seem to get any
> further...
>
> Thanks very much for any ideas,
> Patricia
>
> ********************************************
> Patricia Clarke
> Systems Analyst -- Information Systems
> Wilfrid Laurier University
> 75 University Ave. W.
> Waterloo, Ontario, Canada N2L 3C5
> E-mail: pclarke_at_mach2.wlu.ca
> ********************************************
>
> --
> Author: Patricia Clarke
> INET: pclarke_at_mach2.wlu.ca
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Tue May 02 2000 - 09:05:27 CDT