Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Spooling SQL*Plus output to a unique file name (Unix)

Spooling SQL*Plus output to a unique file name (Unix)

From: Patricia Clarke <pclarke_at_mach2.wlu.ca>
Date: Mon, 1 May 2000 18:19:58 -0400
Message-Id: <10484.104625@fatcity.com>


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
Received on Mon May 01 2000 - 17:19:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US