Java Mailing List Archive

http://www.dba.5341.com/

Home » Home (12/2007) » oracle l »

SQLPLUS issue from ksh script

David Barbour

2006-05-26

Replies:

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.
©2008 dba.5341.com - Jax Systems, LLC, U.S.A.