PL/SQL loop stuck in batch file. please help
Hi,
I noticed that when I run PL/SQL scripts with loops in a batch, the script gets stuck.
For instance, I can run this script in SQL Developer with out any problems.
--Kill all the Oracle connections script
DECLARE -- declare variables
CURSOR c1 IS
select sid, serial# from v$session where username='MWFUSER' or username='ORSUSER';
-- declare cursor-- declare record variable that represents a row fetched
kill_it c1%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
-- open the explicit cursor c1
OPEN c1;
LOOP
FETCH c1 INTO kill_it; -- retrieve record
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''|| kill_it.sid ||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
However, when I run this same script in a batch or from command line like this.
SQLPLUS SYS/PASSWORD@MWFTST AS SYSDBA @"C:\scheduled batches\REFRESH\KILLSESSIONS.SQL"
Then I get this stuck script. Any Ideas? thanks
C:\Documents and Settings\MYNAME>SQLPLUS SYS/PASSWORD@MWFTST AS SYSDBA @"C:\sche
duled batches\REFRESH\KILLSESSIONS.SQL"
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 14 09:19:31 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production
22
23
24
25
26
27
28
29
30
31
32
33
Edited by: user9934078 on Jan 14, 2010 9:45 AM