Purpose of Procedure: Kill SQL*Net/Break sessions and log them into separate table. This will be run using SYS account only.
If I remove all cursor2 related code to insert records into table, it works fine.
Issue: Need to troubleshoot this procedure quickly. Keep getting the (PLS-00103: Encountered the symbol "end-of-file") error.
/* Code attached below. */
-- Table Creation code
CREATE TABLE V$SESSION_LOG
(
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2 (30 CHAR),
STATUS VARCHAR2 (8 CHAR),
OSUSER VARCHAR2 (30 CHAR),
EVENT VARCHAR2 (64 CHAR),
LOG_DATE TIMESTAMP (6) WITH TIME ZONE,
COMMENTS VARCHAR2 (50 CHAR),
SQL_FULLTEXT CLOB
);
--------------------------------------------------------
-- DDL for Procedure SESS_KILL
--------------------------------------------------------
SET DEFINE OFF;
CREATE OR REPLACE PROCEDURE "SESS_KILL" AS
--DECLARE
v_sid number;
v_serial# number;
v_username varchar2(30);
v_count number;
-- Cursor 1
CURSOR checkuser IS
select sid,serial#,username
from v$session
WHERE USERNAME='PI_USER' and STATUS = 'INACTIVE'
AND EVENT = 'SQL*Net break/reset to client';
-- Cursor 2
CURSOR counts IS
select count(1) into v_count from V$SESSION
where USERNAME = 'PI_USER' and STATUS = 'INACTIVE'
and EVENT in ('SQL*Net break/reset to client');
-- Code to Open Cursor 2 and insert record into V$SESSION_LOG table
BEGIN
OPEN counts;
LOOP
EXIT when counts%NOTFOUND;
IF v_count>0 THEN
-- insert event record into log table along with current timestamp
INSERT INTO V$SESSION_LOG (SID, SERIAL#, USERNAME, STATUS, OSUSER, EVENT, LOG_DATE, SQL_FULLTEXT)
SELECT a.SID, a.SERIAL#, a.USERNAME, a.STATUS, a.OSUSER, a.EVENT,
SYSTIMESTAMP, b.SQL_FULLTEXT
FROM V$SESSION a
LEFT OUTER JOIN V$SQL b
ON a.SQL_ID = b.SQL_ID
WHERE a.USERNAME='PI_USER'
AND a.STATUS = 'INACTIVE'
AND a.EVENT IN ('SQL*Net break/reset to client');
ELSE
-- Insert null record into table if event not found along with timestamp. If event found then insert 0 records using this statement
INSERT INTO V$SESSION_LOG (SID, SERIAL#, USERNAME, STATUS,
OSUSER, EVENT, LOG_DATE, SQL_FULLTEXT)
SELECT null, null, null, null, null, 'SQL*Net break/reset to client', SYSTIMESTAMP,
null from dual
WHERE NOT EXISTS (
SELECT a.SID, a.SERIAL#, a.USERNAME, a.STATUS, a.OSUSER, a.EVENT,
SYSTIMESTAMP, b.SQL_FULLTEXT
FROM V$SESSION a
LEFT OUTER JOIN V$SQL b
ON a.SQL_ID = b.SQL_ID
WHERE a.USERNAME='PI_USER'
AND a.STATUS = 'INACTIVE'
AND a.EVENT IN ('SQL*Net break/reset to client'));
END IF;
END LOOP;
CLOSE counts;
BEGIN
OPEN checkuser;
LOOP
FETCH checkuser into v_sid,v_serial#,v_username;
EXIT when checkuser%NOTFOUND;
IF v_username is not null THEN
execute immediate 'alter system kill session '||chr(39)||v_sid||','||v_serial#||chr(39);
END IF;
END LOOP;
CLOSE checkuser;
END SESS_KILL;