Re: Run command on sqlplus repetitively without reconnection.
- From: "Neil Overend" <neiloverend@xxxxxxxxx>
- To: Ajay_Thotangare@xxxxxx
- Date: Wed, 9 May 2007 21:42:49 +0100
The PERL script made my head hurt :-) so I thought I'd do it using
SQL*Plus and PL/SQL
You can't use dbms_output because the buffer is flushed to screen
after the entire anonymous block has completed, You can't do spool
inside an anonymous block because spool is a SQL*Plus command not SQL
or PL/SQL.
So I'm left with utl_file
do this in one shell, it writes to /tmp/testfile.out ( /tmp must be
writeable by oracle)
sqlplus /nolog
conn sys as sysdba
create directory TEST_DIR as '/tmp';
grant read on directory test_dir to system;
grant write on directory test_dir to system;
conn system
DECLARE
l_file UTL_FILE.file_type;
BEGIN
l_file := UTL_FILE.fopen ('TEST_DIR', 'testfile.out', 'W');
UTL_FILE.put_line(l_file,'************************************************************************');
UTL_FILE.FFLUSH(l_file);
FOR i IN 1 .. 100
LOOP
UTL_FILE.put_line(l_file,'i = '||i);
FOR rec IN (SELECT event, COUNT (*) total
FROM v$session
GROUP BY event
ORDER BY event)
LOOP
UTL_FILE.put_line (l_file,RPAD (rec.event, 60) || rec.total);
END LOOP;
UTL_FILE.put_line(l_file,'************************************************************************');
UTL_FILE.FFLUSH(l_file);
DBMS_LOCK.sleep (2);
END LOOP;
UTL_FILE.fclose (l_file);
END;
/
while it's running, login to a new shell and tail -f /tmp/testfile.out
Worked for me on RHEL4 10.2.0.3. It loops 100 times and you'll see the
loop number in the file just to show that it's working.
Rgds
Neil
--
//www.freelists.org/webpage/oracle-l
Other related posts:
