help on procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: help on procedure

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi friends,
    I was trying to compile the following procedure found in the forum. It is not compiling properly.
    Can someone help me what is the problem?

    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 like 'THE_BOREING_USER' 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

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    Try:

    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 like 'THE_BOREING_USER' 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;

  3. #3
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    You have the same question in the DBA forum. There you can find the corect answer on your question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width