RE: Run command on sqlplus repetitively without reconnection.

Ajay,  

I don't have a complete answer for you, but it looks to me like you will need to use a combination of dbms_lock.sleep and an anonymous PL/SQL block inside your code. So you would eliminate the Unix while loop and simply execute sqlplus. Inside of sqlplus you would do a while loop using PL/SQL and dbms_lock.sleep. To get your output, I am thinking you would have to use utl_file and dbms_output. Alternatively you could just store the results in a table. Hmmm, maybe an external table? Sorry I don't have code examples for you, but maybe this gives you some ideas of how to proceed.  

Cheers,  

David Taft

From: Thotangare, Ajay

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

do

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 ?

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