Oracle Why the follwoing block of code is faild ?

2012-10-23  Source: Original Site  Category:Oracle  Views:0 

Advertisement

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"

Related articles
  • Oracle Why the follwoing block of code is faild ? 2012-10-23

    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, NU

  • How oracle finds the data blocks of a table while doing full table scan ? 2012-05-07

    Could someone please let me know,How oracle finds the data blocks of a table while doing full table scan ?

  • Oracle Why the space not be released? 2012-06-06

    In oracle, Why the tablespace not be released after delete many rows in which include ORDSYS.ORDVIDEO column where many video files are stored in?? How can I decrease the tablespace possession after delete rows in table_video table?

  • Oracle Why the intab.sp code generate an error? 2012-01-01

    i am trying the dynamic columns of a table. And i found a code that is coded by an Oracle Expert. ******************** CREATE OR REPLACE PROCEDURE intab ( -- intab.sp table_in IN VARCHAR2 , string_length_in IN INTEGER := 20 , where_in IN VARCHAR2 :=

  • Oracle Why the blocksize is multiples of 2? 2014-04-30

    Sorry if I stick to basic,I have query why the Oracle block size is multiples of 2 ? Rgds, Viswa

  • Oracle Why the CREATION_TIME of the backup copy is different with the reality? 2012-05-01

    Why the CREATION_TIME of the backup copy is different with the reality? I installed the Oracle 11g (11.0.1.6) database last month. But from the fixed table I can see most of the backups were made in 2007. How to explain it? SQL> col name format a9

  • Oracle Why the dropped table not in recyclebin? 2012-06-06

    Why the dropped table not lised in recyclebin? But listed in EM console. I think it maybe a Oracle bug. SQL> drop table jeff.job; Table dropped. SQL> select*from jeff.job; select*from jeff.job * 'ERROR at line 1: ORA-00942: table or view does n

  • Oracle why the indexes of one table are not used for the query? 2012-07-12

    why the indexes of one table are not used for the query? Thy indexes of table INCOM are not used and one index of table incom_msg_idx is used? SQL> select table_name,index_name from user_indexes where table_name='INCOM' OR table_name= 2 'INCOM_MSG

  • Oracle Customize the Anonymous Block. 2013-09-20

    DB - Oracle 9.2.0.1.0 With the help of you Guys , I am able to create the Anonymous block.But, In the following BLOCK, I need to add One more functionality, if someone needs to grant "create view" privilge inside the block , then the massage should b

  • Oracle Why the explain plan incorrent? 2013-12-27

    I have a sql in oracle10.2.03: select * from (select a.* from t_user a, t_message b where b.user_id = a.row_id and a.user_type = 1 order by b.happen_time desc) where rownum <= 5 there are indexes on column a.user_type,b.user_id,b.happen_time, and

  • Oracle Why the listener cannot start? All seems OK 2014-01-02

    Why the listener cannot start? It seems no problems. [[email protected] database]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-JUL-2009 10:46:33 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPT

  • Oracle Why the "/" and "\" are mixedly used in the directory? 2014-01-16

    In DOS, backslash shows directory and slash is used to specify parameters, Why the "/" and "\" are mixedly used in the directory? The following examples come from a Pfile: * C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL03\CONTROL01.CTL * db_recovery_file_des

  • Oracle Why the unnecessary index scan? 2014-01-28

    In the example below, why does Oracle do the INDEX UNIQUE SCAN (line 3 of the explain plan)? It doesn't appear to be needed for any reason. The view outer joins to table B, but the query selects only columns from table A. You can see in the explain p

  • Oracle Why the content of the Explain Plan not show out? 2014-03-15

    Why the content of the Explain Plan not show out? I am using Oracle 11g. I already ran the utlxplan.sql sctipt and I also set serveroutput on. SQL> set autotrace on SQL> EXPLAIN PLAN FOR SELECT*FROM DEMO_TABLE WHERE OWNER='HR'; Explained. Elaps

  • Oracle Why the table not lost? 2014-05-03

    I flashback database to the time before creating table scott.job. Why the table scott.job not lost after flashback recovery? SQL> select*from v$log_history; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# ---------- ---------- -

  • Oracle WHY THE TABLE CANNOT BE MODIFIIED? 2014-10-05

    WHY THE TABLE CANNOT BE MODIFIIED? No any constraint on it, no any ather limitations. UPDATE SYSTEM.JOB SET MIN_SALARY=20000,MAX_SALARY=30000 WHERE JOB_ID='SA_MAN'; UPDATE SYSTEM.JOB SET MIN_SALARY=20000,MAX_SALARY=30000 WHERE JOB_ID='SA_MAN' * ERROR

  • Oracle Why the out put of queries are not consistent? 2015-04-07

    Why the out put of queries are not consistent for the same object? SQL> select table_name,blocks from user_tables where table_name='INCOMING'; TABLE_NAME BLOCKS ---------- INCOMING 0 SQL> select count(*) from incoming; COUNT(*) 200000

  • Oracle Why the second query sentence not work? 2012-01-13

    Why the second query sentence not work? SQL> select object_name,original_name from recyclebin; OBJECT_NAME ORIGINAL_NAME -------------------------------- BIN$V62zjaZ8WZ/gQKjAAgBAhg==$0 EMPLOYE SQL>select object_name,original_name from recyclebi

  • Oracle Why the package use the triple-single-quotation marks? 2012-01-18

    Why the package use the triple-single-quotation marks? SQL>exec dbms_session.set_nls('nls_date_format','''yyyy-mm-dd''')

  • Oracle Why the dictionary view is not updated in time? 2012-02-24

    Why the dictionary view is not updated in time? Please see following example: SQL> conn system Enter password: Connected. *SQL> create table employee as select*from hr.employees;* Table created. SQL> select last_name,salary from employee whe