Error in procedure body + plsql

Question

create or replace procedure kwp_SCMP_KillSessionTEST is

v_sql VARCHAR2(60); Cnt integer := 0; cursor cur is select t.sql_id, t.detail, t.sid from TESTPROCE t;

begin

for i in cur loop

begin

  cnt := cnt + 1;
  select distinct S.SID, S.SERIAL#, s. MACHINE, s.SQL_ID, s.EVENT
    from V$SESSION S , TESTPROCE
   where s.username <> 'SYS'
     and s.sql_id = t.sql_id

     and s.type <> 'BACKGROUND';
  v_sql := 'ALTER SYSTEM KILL SESSION ''' || i.sid || ',' || i.serial# ||
           ''' immediate';
  dbms_output.put_line(Cnt || '>>>>' || SQLID);
   --execute immediate (v_sql);

end;

END LOOP;

dbms_output.put_line(Cnt || '>>>>' || SQLID);

END kwp_SCMP_KillSessionTEST;

Compilation errors for PROCEDURE SYS.KWP_SCMP_KILLSESSIONTEST

Error: PL/SQL: ORA-00904: "T"."SQL_ID": invalid identifier Line: 21 Text: and s.sql_id = t.sql_id

Error: PL/SQL: SQL Statement ignored Line: 18 Text: select distinct S.SID, S.SERIAL#, s. MACHINE, s.SQL_ID, s.EVENT

Error: PLS-00302: component 'SERIAL#' must be declared Line: 23 Text: v_sql := 'ALTER SYSTEM KILL SESSION ''' || i.sid || ',' || i.serial# ||

Error: PL/SQL: Statement ignored Line: 23 Text: v_sql := 'ALTER SYSTEM KILL SESSION ''' || i.sid || ',' || i.serial# ||

Error: PLS-00201: identifier 'SQLID' must be declared Line: 25 Text: dbms_output.put_line(Cnt || '>>>>' || SQLID);

Error: PL/SQL: Statement ignored Line: 25 Text: dbms_output.put_line(Cnt || '>>>>' || SQLID);

Error: PLS-00201: identifier 'SQLID' must be declared Line: 31 Text: dbms_output.put_line(Cnt || '>>>>' || SQLID);

Error: PL/SQL: Statement ignored Line: 31 Text: dbms_output.put_line(Cnt || '>>>>' || SQLID);

Hi, welcome to Stack Overflow. You might want to read through some of the help pages on how to ask a good question, then click "edit" and add a few details of what you're actually asking. You can format the code correctly by selecting it and clicking the {} button in the text area toolbar. — IMSoP

Peter Crump Peter Crump · Accepted Answer · 2014-11-26T09:30:42

The compiler is correct; there are some basic syntactical errors in that code. Fix those first, and then re-submit in the form of a question.

Error in procedure body + plsql

1 Answers