Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQLPLUS issue from ksh script

Re: SQLPLUS issue from ksh script

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


I thought I had escaped the $, which was the first problem I encountered this morning when it told me that "Table or View does not exist.." I had another issue with the execute immediate because I initially created the command string with a ; and although it came out in dbms_output.put_line as alter system kill session '13,4287'; that wasn't what execute immediate wanted, just the statement alter system kill session '13,4287'

The output I included below was run using ksh -x , so the reproduction of what Oracle is getting is shown. As you'll note the v$session with the single / is coming across okay - I think. Am I missing something else?

On 5/26/06, Reidy, Ron <Ron.Reidy_at_arraybiopharma.com> wrote:
>
> 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
Received on Fri May 26 2006 - 11:44:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US