Re: SQLPLUS issue from ksh script

Okay, that worked.

I wanted the script to be reaaaaaaaaaaally informative, but it's more important that it performs at this point. Whoever wants to read it it just going to have to take a detour.

Thanks.

On 5/26/06, Goulet, Dick <DGoulet_at_vicr.com> wrote:
>
> Put it into a sql script so that the KSH doesn't see it. Your KSH script
> then becomes
>
> sqlplus "/ as sysdba" @kill.sql
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Reidy, Ron
> *Sent:* Friday, May 26, 2006 12:34 PM
> *To:* david.barbour1_at_gmail.com; Oracle-L Freelists
> *Subject:* RE: SQLPLUS issue from ksh script
>
> Escape the "$" in v$session => v\#session
>
>
>
> --
>
> Ron Reidy
>
> Lead DBA
>
> Array BioPharma, Inc.
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *David Barbour
> *Sent:* Friday, May 26, 2006 10:29 AM
> *To:* Oracle-L Freelists
> *Subject:* SQLPLUS issue from ksh script
>
>
>
> 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.
>
> ------------------------------
> This electronic message transmission is a PRIVATE communication which
> contains information
> which may be confidential or privileged. The information is intended to be
> for the use of the individual
> or entity named above. If you are not the intended recipient, please be
> aware that any disclosure,
> copying, distribution or use of the contents of this information is
> prohibited. Please notify the sender
> of the delivery error by replying to this message, or notify us by
> telephone (877-633-2436, ext. 0),
> and then delete it from your system.
>
>

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