problem in calling the procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: problem in calling the procedure

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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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;
    /
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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

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