Re: Run command on sqlplus repetitively without reconnection.

From: Neil Overend <neiloverend_at_gmail.com>
Date: Wed, 9 May 2007 21:42:49 +0100
Message-ID: <5acbeade0705091342w3ad4891ds3ca01db7315cb1f2@mail.gmail.com>

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

Received on Wed May 09 2007 - 15:42:49 CDT