Solved

Execute immediate problem

Posted on 2007-11-28
2
1,162 Views
Last Modified: 2013-12-07
I got this code yesterday to track and kill TOAD sessions:
BEGIN
    FOR s IN (SELECT SID, serial#
                FROM v$session
               WHERE program LIKE '%TOAD%')
    LOOP
        EXECUTE IMMEDIATE 'alter system kill session ''' || TO_CHAR(s.SID) || ', ' || TO_CHAR(s.serial#)
                          || '''';
    END LOOP;
END;


It picks up the session, but the execute immediate does not successfully kill the session although the pl/sql completes successfully.  Within that code structure what modification do i need to make it work?
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 250 total points
ID: 20367231
Include immediate keyword. Trap the exception to show the error, if any.
BEGIN
 
FOR s IN (SELECT SID, serial# FROM v$session WHERE program LIKE '%TOAD%') LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || TO_CHAR(s.SID) || ', ' || TO_CHAR(s.serial#) || ''' IMMEDIATE';
END LOOP;
 
EXCEPTION
 
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
 
END;

Open in new window

0
 

Author Comment

by:xoxomos
ID: 20367715
I saw that in the docs, but did not pay attention!!!
Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question