help on procedure

Quote:

---

Originally posted by manjunathk

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 like 'THE_BOREING_USER' and
b.ctime > time;

---

cursor c1 is
select 'alter system kill session = '''
||to_char(a.sid)||', '||to_char(a.serial#)||''' IMMEDIATE ;'
from v$session a, v$lock b
where a.sid = b.sid
and b.lmode = 6
and a.username like 'THE_BOREING_USER'
and b.ctime > time;

Sam

Hi,

To compile it successfully, just remove first and last || in

||to_char(a.sid)||', '||to_char(a.serial#)||

and think about how to pass THE_BOREING_USER parameter in your procedure.

Hope this helps,

Thanks Sam, Dmitriy.
I am still getting the following errors when I tried Sam's suggestion.

Errors for PROCEDURE KILL_LOCKED_USR:

7/1 PL/SQL: SQL Statement ignored
7/8 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

9/6 PLS-00201: identifier 'SYS.V_$SESSION' must be declared
17/1 PL/SQL: SQL Statement ignored


The select 'alter.... statement works fine, in sql plus but is returning the above error when tried in procedure
Manjunath

To undestand more, which script did you use? who is the owner of this script? Does the owner has a select-Catalog role granted?

Sam

Yes, the user who is trying to create this procedure has dba privileges.
Anyway i tried once more after giving grant on catalog specifically, but the same errors repeat.

I used the following :

create or replace procedure kill_locked_usr
(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;
/

Thanks
manjunath

Code:

---


connect internal
grant select on v$session to user;
grant select on v$lock to user;

---

select priv through a role is useless in pl/sql

Quote:

---

Originally posted by pando

Code:

---


connect internal
grant select on v$session to user;
grant select on v$lock to user;

---

select priv through a role is useless in pl/sql

---

This would fire

ORA-02030: can only select from fixed tables/views


The way to do is,

sqlplus sys@service_name

SQL> GRANT SELECT ON v_$session TO user;
SQL> GRANT SELECT ON v_$lock TO user;

SQL> connect user/passwd@service_name
SQL> @your_procedure


This will work.

Sam