| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Spooling SQL*Plus output to a unique file name (Unix)
Hi Patricia,
Please find enclosed a script which I had picked up from one of the Oracle sites.
Hope this meets your requirement..
Regards
Rajagopal Venkataramany
This bulletin illustrates a method of programming a script to automatically label each spool filename with a timestamp of the date and time of the script execution.
The following SQL*Plus script illustrates the timestamp method:
column timecol new_value timestamp
select to_char(sysdate,'.MMDDYY_HHMISS') timecol from dual;
spool output×tamp
select table_name from user_tables
order by table_name;
spool off
If this script is executed at 12:34:12 AM on July 17, 1992, the output would be spooled to an output file with the name 'output.071792_123412'. A user can modify the date format mask ('.MMDDYY_HHMISS') and/or the constant filename portion ('output') of the example to create different spool filename formats.
----Original Message Follows----
From: "Patricia Clarke" <pclarke_at_mach2.wlu.ca>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Subject: Spooling SQL*Plus output to a unique file name (Unix)
Date: Mon, 01 May 2000 15:33:30 -0800
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
-- 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 also send the HELP command for other information (like subscribing). ________________________________________________________________________Received on Mon May 01 2000 - 21:09:41 CDT
 |
 |