• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1179
  • Last Modified:

Execute immediate problem

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
xoxomos
Asked:
xoxomos
1 Solution
 
Jinesh KamdarCommented:
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
 
xoxomosAuthor Commented:
I saw that in the docs, but did not pay attention!!!
Thanks.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now