Hi,
I need your help to understand why the follwing code is faild.
for example , why there is a problem with line 9 ?
SQL> begin
2 for i in ( SELECT COUNT(*) cnt , decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) qc_sid ,
3 decode(px.qcinst_id, NULL ,to_char(s.serial#) ,px.qcserial#) qc_serial
4 FROM v$SESSION s, v$TRANSACTION t, v$px_session px
5 WHERE s.saddr = t.ses_addr
6 and px.sid(+) =s.sid
7 and px.serial#(+)=s.serial#
8 GROUP BY decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid),
9 decode(px.qcinst_id, NULL ,to_char(s.serial#) ,px.qcserial#) loop
10 for y in (SELECT decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) qc_sid ,
11 decode(px.qcinst_id, NULL ,to_char(s.serial#) ,px.qcserial#) qc_serial,
12 s.username, s.schemaname,s.osuser,s.module,s.program,
SUBSTR((SELECT sql_text FROM v$sql v WHERE v.hash_value = s.sql_hash_value AND ROWNUM=1),1,100) sql_text
13 14 FROM v$SESSION s, v$TRANSACTION t, v$px_session px
WHERE s.saddr = t.ses_addr
and px.sid(+) =s.sid
and px.serial#(+)=s.serial#) loop
if (upper(y.module) like upper('%PL%SQL%') or upper(y.module) like upper('%TOAD%') ) then
15 16 17 18 19 dbms_output.put_line('================The following statment is going to be killed : ========================');
20 dbms_output.put_line(y.sql_text);
dbms_output.put_line('================The following session are going to be kill 21 ed : ========================');
dbms_output.put_line('killed sid='||y.qc_sid||' serial#='||y.qc_serial||' progra 22 m='||y.program||' username='||y.username);
execute immediate ' alter system kill session '''||y.qc_sid||','||y.qc_serial||'''';
if i.cnt > 1 then -- THIS IS A PDML. AFTER KILLING THE CORDINATOR YOU SHOULD EXIT
EXIT;
end if;
23 24 25 26 27 else
dbms_output.put_line('Active transaction on sid='||y.qc_sid||' serial#='||y.qc_serial||' program='||y.program||' username='||y.username||' sql text='||y.sql_text);
end if;
end loop;
end loop;
28 29 30 31 32 end;
/ 33
decode(px.qcinst_id, NULL ,to_char(s.serial#) ,px.qcserial#) loop
*
ERROR at line 9:
ORA-06550: line 9, column 62:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 2, column 10:
PL/SQL: SQL Statement ignored
ORA-06550: line 19, column 112:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
ORA-06550: line 27, column 1:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimite
ORA-06550: line 30, column 1:
PLS-00103: Encountered the symbol "END"
Thanks