Re: Run command on sqlplus repetitively without reconnection.

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: