problem in calling the procedure

Hi friends,
I am getting errors in calling the following procedure(which is compiled correctly)

create or replace procedure kill_locked_usr_tds2
(time in integer) as
my_cursor integer;
my_statement varchar2(80);
result integer;
cursor c1 is
select 'alter system kill session '''
||to_char(a.sid)||','||to_char(a.serial#)||''';'
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6
and a.username= 'TDS_LOADER'
and b.ctime > time;
begin
open c1;
loop
fetch c1 into my_statement;
exit when c1%notfound;
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor,my_statement,dbms_sql.v7);
result :=dbms_sql.execute(my_cursor);
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
end;
/

When there are locks present which satisfy the 'where' clause of the procedure then the call fails with the following errors:

SQL> begin
2 kill_locked_usr_tds2(50);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "TDS_MANAGER.KILL_LOCKED_USR_TDS2", line 20
ORA-06512: at line 2

However, when the 'where' conditions is not true then the call comes out with 'PL/SQL procedure successfully completed.' message.But, obviously this is not of any help as there were no locks in the first place.

Can someone help me to identify the problem?

Thanks
manjunath

This error is telling you that your SQL statment is not correct. I am guessing it is because of the ";" at the end of your statement. Also, I would ltrim(rtrim()) your to_char results because sometimes oracle puts a blank space in front of the value for a - sign holder.

Try:
create or replace procedure kill_locked_usr_tds2
(time in integer) as
my_cursor integer;
my_statement varchar2(80);
result integer;
cursor c1 is
select 'alter system kill session '''
||ltrim(rtrim(to_char(a.sid)))||','||ltrim(rtrim(to_char(a.serial#)))||''''
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6
and a.username= 'TDS_LOADER'
and b.ctime > time;
begin
open c1;
loop
fetch c1 into my_statement;
exit when c1%notfound;
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor,my_statement,dbms_sql.v7);
result :=dbms_sql.execute(my_cursor);
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
end;
/

Thanks, Jeff.
I will change the code as suggested by you. And I will update after the next deadlock occurs and I know the result of procedure call.
And what about the ';'? Without that it is not even compiling .
Thanks again
manjunath

[Edited by manjunathk on 09-28-2001 at 04:30 PM]