Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

EXCEPTION WHEN NO_DATA_FOUND

happy10319Mar 25 2019 — edited Mar 25 2019

Hi,

On 12c

In my PL/SQL program I treat EXCEPTION WHEN NO_DATA_FOUND as follows and after that I like to do: dbms_output.put_line('xxx');.

But it is not executed. Where is the end of EXCEPTION and how to continue after?

=========my program===================

DECLARE

i number;

CURSOR m_cur IS

SELECT * from TABLE1

UNION

SELECT * from TABLE2;

myrow m_cur%rowtype;

BEGIN

select id into i from table2 where id=284;

DBMS_output.put_line('I IS : '||i);

EXCEPTION WHEN NO_DATA_FOUND THEN

BEGIN

OPEN m_cur;

 LOOP

    FETCH m\_cur INTO myrow;

    EXIT WHEN m\_cur%NOTFOUND;

    i:=myrow.id;

dbms_output.put_line('i : '||i);

 END LOOP;

 CLOSE m\_cur;

END;

dbms_output.put_line('xxx');

END;

=============output========================

pastedImage_0.png

Thanks.

Construction:

create TABLE TABLE1 (id number, PROGRAM varchar2(25));

create TABLE TABLE2 (id number, PROGRAM varchar2(25));

Insert into TABLE1 (ID,PROGRAM) values ('1681','prog1');

Insert into TABLE1 (ID,PROGRAM) values ('1687','Z_prog1');

Insert into TABLE2 (ID,PROGRAM) values ('284','prog1');

Insert into TABLE2 (ID,PROGRAM) values ('285','prog1');

Insert into TABLE2 (ID,PROGRAM) values ('286','Z_prog1');

Insert into TABLE2 (ID,PROGRAM) values ('287','prog1');

Insert into TABLE2 (ID,PROGRAM) values ('288','prog1');

This post has been answered by Cookiemonster76 on Mar 25 2019
Jump to Answer
Comments
Post Details
Added on Mar 25 2019
7 comments
1,783 views