| 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)
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
Excuse my poor english !
Try this SQL script :
column spool_id noprint new_value v_spool_id
select to_char(sysdate,'hh24miss') spool_id from dual;
spool &&v_spool_id
...
your PL/SQL bloc(s)
...
spool off
Note : since you are in a UNIX environment, you can also use the SHELL possibilities to do this.
regards.
-----Message d'origine-----
De : Patricia Clarke [mailto:pclarke_at_mach2.wlu.ca]
Envoy=E9 : mar. 2 mai 2000 00:34
=C0 : Multiple recipients of list ORACLE-L
Objet : 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
--=20
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).
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3Diso-8859-1">
<P><FONT SIZE=3D2>Excuse my poor english !</FONT> </P>
<P><FONT SIZE=3D2>Try this SQL script :</FONT> </P>
<P><FONT SIZE=3D2>column spool_id noprint new_value v_spool_id</FONT> <BR><FONT SIZE=3D2>select to_char(sysdate,'hh24miss') spool_id from = dual;</FONT>
<BR><FONT SIZE=3D2>spool &&v_spool_id</FONT> <BR><FONT SIZE=3D2>...</FONT> <BR><FONT SIZE=3D2>your PL/SQL bloc(s)</FONT> <BR><FONT SIZE=3D2>...</FONT> <BR><FONT SIZE=3D2>spool off</FONT> </P>
<P><FONT SIZE=3D2>Note : since you are in a UNIX environment, you can = also use the SHELL possibilities to do this.</FONT> </P>
<P><FONT SIZE=3D2>regards.</FONT> </P> <BR>
<P><FONT SIZE=3D2>-----Message d'origine-----</FONT>
<BR><FONT SIZE=3D2>De : Patricia Clarke [<A =
HREF=3D"mailto:pclarke_at_mach2.wlu.ca" =
TARGET=3D"_blank">mailto:pclarke_at_mach2.wlu.ca</A>]</FONT>
<BR><FONT SIZE=3D2>Envoy=E9 : mar. 2 mai 2000 00:34</FONT> <BR><FONT SIZE=3D2>=C0 : Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Objet : Spooling SQL*Plus output to a unique file =name (Unix)</FONT>
<P><FONT SIZE=3D2>Oracle 7.3.4, Dynix 4.2.3, SQL*Plus 3.3.4, PL/SQL =
2.3.4</FONT>
</P>
<P><FONT SIZE=3D2>I have a PL/SQL script that I am running on the =
server (Unix) and I am</FONT>
<BR><FONT SIZE=3D2>spooling the output into a log file. The =
problem is that I don't want to</FONT>
<BR><FONT SIZE=3D2>lose the previous output each time I run the script, =
so I want to be able to</FONT>
<BR><FONT SIZE=3D2>either spool to a unique file name each time (e.g. =
by appending a timestamp</FONT>
<BR><FONT SIZE=3D2>or the process ID or something to the log file =
name), or keep the same file</FONT>
<BR><FONT SIZE=3D2>name but *append* the new output to the old instead =
of overwriting</FONT>
<BR><FONT SIZE=3D2>everything each time.</FONT>
</P>
<P><FONT SIZE=3D2>I have read the SQL*Plus documentation on the SPOOL =
command and don't see</FONT>
<BR><FONT SIZE=3D2>any options for appending output to an existing file =
instead of overwriting,</FONT>
<BR><FONT SIZE=3D2>so I'm guessing that's not possible...</FONT>
</P>
<P><FONT SIZE=3D2>So, I guess I am looking to do something like:</FONT> </P>
<P><FONT SIZE=3D2>DECLARE unique_file_name VARCHAR2(18);</FONT> <BR><FONT SIZE=3D2>...</FONT> <BR><FONT SIZE=3D2>BEGIN</FONT> <BR><FONT SIZE=3D2> ...</FONT> <BR><FONT SIZE=3D2> SELECT =to_char(sysdate,'YYYYMMDDHHMISS')||'.log'</FONT>
<BR><FONT SIZE=3D2> INTO unique_file_name</FONT> <BR><FONT SIZE=3D2> FROM dual;</FONT> <BR><FONT SIZE=3D2> ...</FONT> <BR><FONT SIZE=3D2> SPOOL unique_file_name</FONT> <BR><FONT SIZE=3D2> ...</FONT> <BR><FONT SIZE=3D2> SPOOL OFF</FONT> <BR><FONT SIZE=3D2>END;</FONT> </P>
<P><FONT SIZE=3D2>But, this doesn't work because Oracle doesn't like =
the SPOOL statements</FONT>
<BR><FONT SIZE=3D2>within the PL/SQL block, but if I take them out of =
the PL/SQL block, I get a</FONT>
<BR><FONT SIZE=3D2>log file called unique_file_name.lst rather than =
20000501103013.log (or</FONT>
<BR><FONT SIZE=3D2>whatever).</FONT>
</P>
<P><FONT SIZE=3D2>I'm probably missing something stupid here, but can't =
seem to get any</FONT>
<BR><FONT SIZE=3D2>further...</FONT>
</P>
<P><FONT SIZE=3D2>Thanks very much for any ideas,</FONT> <BR><FONT SIZE=3D2>Patricia</FONT> </P> <P><FONT SIZE=3D2>********************************************</FONT> <BR><FONT SIZE=3D2>Patricia Clarke</FONT> <BR><FONT SIZE=3D2>Systems Analyst -- Information Systems</FONT> <BR><FONT SIZE=3D2>Wilfrid Laurier University</FONT> <BR><FONT SIZE=3D2>75 University Ave. W.</FONT> <BR><FONT SIZE=3D2>Waterloo, Ontario, Canada N2L 3C5</FONT> <BR><FONT SIZE=3D2>E-mail: pclarke_at_mach2.wlu.ca</FONT> <BR><FONT SIZE=3D2>********************************************</FONT></P>
<P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Patricia Clarke</FONT> <BR><FONT SIZE=3D2> INET: pclarke_at_mach2.wlu.ca</FONT> </P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=-----</FONT>
 |
 |