URGENT: Killing Users using dynamic SQL

From: Martin <mmarhold_at_yahoo.com>
Date: Wed, 12 Jan 2000 13:28:04 +0100
Message-ID: <387c7402@s21ntpn001.via.at>

I´m trying to kill sessions using dynamic SQL. The code that should do this is:

aFetchCursor := SYS.DBMS_SQL.OPEN_CURSOR; aKillCursor := SYS.DBMS_SQL.OPEN_CURSOR; aSqlCmd := 'SELECT sid, serial# FROM Temp_Online_Users';

SYS.DBMS_SQL.PARSE(aFetchCursor, aSqlCmd, DBMS_SQL.V7);
SYS.DBMS_SQL.DEFINE_COLUMN(aFetchCursor, 1, aUserSid);
SYS.DBMS_SQL.DEFINE_COLUMN(aFetchCursor, 2, aUserSerial#);

aRetCode := SYS.DBMS_SQL.EXECUTE(aFetchCursor); LOOP
   IF DBMS_SQL.FETCH_ROWS(aFetchCursor) = 0 THEN

      EXIT;
   END IF;
   DBMS_SQL.COLUMN_VALUE(aFetchCursor, 1, aUserSid);    DBMS_SQL.COLUMN_VALUE(aFetchCursor, 2, aUserSerial#);    aSqlCmd := 'ALTER SYSTEM KILL SESSION '''||aUserSid||','||aUserSerial#||'''';

      SYS.DBMS_SQL.PARSE(aKillCursor, aSqlCmd, DBMS_SQL.V7);
      aRetCode := SYS.DBMS_SQL.EXECUTE(aKillCursor);

END LOOP;
SYS.DBMS_SQL.CLOSE_CURSOR(aKillCursor); SYS.DBMS_SQL.CLOSE_CURSOR(aFetchCursor);

Although I´m connected as User SYS (who has the privilege to kill sessions) the code above returns the folowing error:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
ORA-06512: at "SYS.DBMS_SQL", line 328

Does anyone have a solution for this?

Thanks in advance, Martin Received on Wed Jan 12 2000 - 06:28:04 CST