SQLPLUS issue from ksh script

From: David Barbour <david.barbour1_at_gmail.com>
Date: Fri, 26 May 2006 12:28:54 -0400
Message-ID: <69eafc3f0605260928t60ff3dc4sb23fa7b4114dc99e@mail.gmail.com>

I'm missing something easy here, so I'd really appreciate a fresh set of eyes. I've got a persistent jdbc connection to a primary database I need to get rid of before I can switch to the standby. The only way I've been able to come with to get rid of it for the time I need to switch over is to lock the user account, find the SID and SERIAL# of the session(s) and kill them. Maybe there's a better way, but it works. Until I try to script it. Here's the relevant portion of the script(ksh): declare
  STR VARCHAR2(2000);
begin
  for x in (select * from v\$session) LOOP     IF x.user# = 27 then
      str := ' alter system kill session ''' || x.sid || ',' || x.serial#||'''';

      execute immediate str;
     END IF;

   END LOOP;
end;
/
But when I run it I get:
sqlplus "/ as sysdba" <<EOJ

declare

  STR VARCHAR2(2000); begin

  for x in (select * from v$session) loop     IF x.user# = 27 then

      str := ' alter system kill session ''' || x.sid || ',' || x.serial#||'''';
      execute immediate str;

    End if;
  END LOOP;
end;

EOJ
session: Undefined variable.
logout

It works in "pure" sqlplus, just not in the script.

Ideas? Thanks.

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

Received on Fri May 26 2006 - 11:28:54 CDT