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

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
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What are the error messages did you get?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Oct 2000
    Posts
    211

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Apr 2001
    Posts
    47
    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,



  6. #6
    Join Date
    Oct 2000
    Posts
    211
    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

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Oct 2000
    Posts
    211
    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

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


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