HELP! using variable in DDL

[coleex]

Hi, Iam new to pl/sql and Iam writing a script that should kill any user logged on as ELI. I need to store it as an procedure. For right now iam testing it out as a pl/sql block. I need to know how to pull the sid and serial# into my DDL command 'ALTER SYSTEM KILL SESSION 'SID,SERIAL#'. I have no idea how to make the ddl take values from a variable. Here is my script so far. Also feel free to point out other mistakes i have made =).

DECLARE
cursor_id integer;
ddl_command varchar2(100);
result_of_ddl integer;
ksid number;
kser number;

BEGIN
SELECT sid,serial# INTO KSID,KSER from v$session where username IN(select username
FROM v$session
where username = 'ELI');
IF ksid = (select sid from v$session where username = 'ELI') AND
kser = (select serial# from v$session where username = 'ELI')
cursor_id := dbms_sql.open_cursor;
ddl_command := 'ALTER SYSTEM KILL SESSION';
dbms_sql.parse ( cursor_id, ddl_command, dbms_sql.native );
result_of_ddl := dbms_sql.execute (cursor_id);
dbms_sql.close_cursor (cursor_id);
END IF;
end;
/

[santoshym]

Hi

Just a suggestion if you are using Oracle 8.1.7 why dont you use database triggers at the USER level when ever the USER ELI tries to log in.Deny login at the login level.

Regards,

[coleex]

Iam running 7.3.4

[APylypenko]

try to modify ddl_command as something like this:

ddl_command := 'ALTER SYSTEM KILL SESSION '''' ||ksid||','||kser||''''||;


Good luck,

Anna

[santoshym]

Hi

I have modified ur code a bit

DECLARE
cursor_id number(4);
ddl_command varchar2(100);
result_of_ddl number(4);
ksid number(4);
kser number(6);
user char(20);
BEGIN
SELECT sid,serial#,username INTO KSID,KSER,user from v$session where username = 'SCOTT';
dbms_output.put_line('sid :' || ksid);
dbms_output.put_line('serial No :' || kser);
If user = 'SCOTT'
then
cursor_id := dbms_sql.open_cursor;
ddl_command := 'ALTER SYSTEM KILL SESSION '''||KSID||','||KSER||''' ';
dbms_sql.parse(cursor_id, ddl_command, dbms_sql.V7);
result_of_ddl := dbms_sql.execute (cursor_id);
dbms_sql.close_cursor(cursor_id);
END IF;
end;

You still have to modify the code above so that it can be in a loop and use a cursor because the above code will work only when a single user is logged in because if multiple users are logged in as scott it will fetch more then one row and an error will be raised stating more then requested no of rows are fetched

Good luck for the rest of it

Regards

[coleex]

Thanks for your help...especially the last one that was right on the money.