
September 20th, 2011, 01:53 AM
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 1
Time spent in forums: 46 m 21 sec
Reputation Power: 0
|
|
|
PL/SQL Procedure to Kill a session
Hi all, I was trying to create a stored procedure but i doesn't seem to work please help me in doing this, I'm trying to write a procedure to kill the session of a particular users and my code is
create or replace procedure testkill ( p_user varchar2)
IS
den varchar2(100);
v_count number(6) := length(regexp_replace(p_user, '[[:alpha:]]', '')) - 2;
query varchar2(20000);
BEGIN
for i in 1..v_count LOOP
den :=substr(p_user,
((instr (p_user, ',',1,i)+1)),
(((instr (p_user, ',',1,i+1))) - (((instr (p_user, ',',1,i)+1))))
);
query := 'select sid ||' ||''','''|| '|| serial# from v$session where username like ''' || '%' || den || '%''' ;
dbms_output.put_line( query);
execute IMMEDIATE('alter system kill session ' ||''''|| query || '''');
END LOOP;
END testkill;
This procedure throws error when executed. If I execute this query,
EXEC testkill (',GOPI1,HR,')
it throws error, i'm a beginer in writing procedures please provide me a solution or assist me in fixing this procedure.
|