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: Verma, Amit <AVerma_at_chx.com>
Date: Fri, 26 May 2006 11:50:49 -0500
Message-ID: <51338F3CEE91164C89AFCC010FEC7F6F0477A605@MX3.chx.com>


I don't think it's the v\$session, it's the session keyword after kill. Create a stored procedure inside Oracle and call the procedure in your script.  

HTH -Amit.  


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Barbour Sent: Friday, May 26, 2006 11:44 AM
To: Reidy, Ron
Cc: Oracle-L Freelists
Subject: Re: SQLPLUS issue from ksh script  

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 <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:50:49 CDT

Original text of this message

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