RE: Run command on sqlplus repetitively without reconnection.

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 9 May 2007 12:38:18 -0400
Message-ID: <00f701c79258$73b72bf0$1100a8c0@rsiz.com>

IF you're on 10g+, then you can use spool file with the append and spoof end. That should flush it on most systems. I'm not aware of a flush the outstanding buffer command that all operating systems will respect, but if you tail -f the spool file that should show the buffer on each close. Of course you've got the spool file open and close overhead, but that should be much less than sqlplus reconnection.

Prior to 10g's addition of the append option if you really needed this you needed to do something like close the spool file and use a host command to cat it appending the real destination and then reopen with replace, or some other variety of excessively baroque and costly (file open close versus buffer flush) manner.

I am also interested in this in general if I managed to miss someone figuring out a better way or a way that works on all the ports, not just the UNIX derivations.

Maybe Jared's got a perly way?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thotangare, Ajay (GTI)
Sent: Wednesday, May 09, 2007 11:23 AM
To: oracle-l_at_freelists.org
Subject: Run command on sqlplus repetitively without reconnection.

I want to run a command on sqlplus without making re-connection. At the same time I also want to see the output.

I can do this using UNIX but only problem is I have to make reconnection after every interval(which I don't want)

For e.g I run following script on unix prompt

#########################################################

while true

sqlplus -S "/as sysdba" <<EOF

select event,count(*) from v$session group by event;

exit;

EOF sleep 2

done

#########################################################

I want to achive same functionality without making reconnection after every 2 secs.

I tried to use loop,spool file and dbms_lock.sleep() but spool file does not display output till the procedure is completed

Can anybody help to achive this functionality ?

regards,

Ajay

If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here <http://www.ml.com/email_terms/> for important additional terms relating to this e-mail. http://www.ml.com/email_terms/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 09 2007 - 11:38:18 CDT