Stored procedure exception handling

Question

SQL>  DECLARE
2    TotalUpd   NUMBER(36) := 0;
3  BEGIN
4   dbms_output.put_line ('Job Start time............... : ' || to_char(SYSDATE, '             hh24:mi:ss'));
5   UPDATE Asset SET _status = 'PROGRESS' WHERE status is null;
6   TotalUpd := SQL%ROWCOUNT;
7   dbms_output.put_line('Total Records Updated. : ' || TotalUpd);
8    COMMIT;
9   EXCEPTION
10   WHEN NO_DATA_FOUND THEN
11  dbms_output.put_line ('No more data to update.');
12  WHEN OTHERS THEN
13  dbms_output.put_line ('Error while status as SUCCESS ');
14  END ;
15  /

The result for the above procedure is Job Start time............... : 04:41:41 Total Records Updated. : 0

But my expected result is "No more row to be updated" must be printed,since i have truncated the table Asset.Please tell where I went wrong in this.

René Nyffenegger · Accepted Answer · 2011-09-29 12:02:34Z

4

The NO_DATA_FOUND error is not thrown in update statements.

It is thrown in select into statements if the select statement would return nothing.

See also Tahiti on select into under select_item: *If the SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.*

Oracle does not consider it an exception if an update statement does not update anything, hence no exception is thrown. However, if a select into statement cannot fill the variables, it is considered an error (and therefore in this case the NO_DATA_EXCEPTION is thrown(

edited Sep 29, 2011 at 12:02

answered Sep 29, 2011 at 11:56

René Nyffenegger

40.9k35 gold badges180 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin Burton · Accepted Answer · 2011-09-29 11:56:31Z

3

it is as simple as the update does not geneate an error if there is no data.

you need to look at the value of TotalUpd if you want to control the flow of your code

 DECLARE 
 TotalUpd   NUMBER(36) := 0;
 BEGIN
    dbms_output.put_line ('Job Start time............... : ' 
        || TO_CHAR(SYSDATE, '             hh24:mi:ss'));
    UPDATE Asset SET _status = 'PROGRESS' WHERE status IS null;
    TotalUpd := SQL%ROWCOUNT; 
    IF TotalUpd = 0 THEN
        dbms_output.put_line ('No more data to update.');
    ELSE
        dbms_output.put_line('Total Records Updated. : '
            || TotalUpd);
    END IF; 
    COMMIT; 
 EXCEPTION 
 WHEN OTHERS THEN
    dbms_output.put_line ('Error while status as SUCCESS '); 
 END;

answered Sep 29, 2011 at 11:56

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

4 Comments

mahi_0707 Over a year ago

You can add SQLERRM & SQLCODE while using WHEN OTHERS exception. WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR IS : ' || SQLCODE || ' : ' || SQLERRM);

William Robertson Over a year ago

@Mahi007 - SQLERRM already includes SQLCODE. And handling an OTHERS exception with a debug message is not generally advisable.

Kevin Burton Over a year ago

remember I'm simply answering the question asked, I find it is best to assume that the functionality is correct, and there could there be a dependency on the output.

mahi_0707 Over a year ago

@William Robertson - Agree that Sqlerrm includes sqlcode. I was considering it as non production enviornment. Perhaps using 'when no data found` then Raise application error (-20001,<custom message> ) ` seem to be better option or alternatively insert into a ` error log table`

vc 74 · Accepted Answer · 2011-09-29 11:56:48Z

1

NO_DATA_FOUND is thrown if a select into does not return any row, not if no rows were updated after an update statement.

I suggest you move your logic for handling this exception after the update itself:

IF (SQL%ROWCOUNT = 0) THEN  
  dbms_output.put_line ('No more data to update.');

answered Sep 29, 2011 at 11:56

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

Comments

OTTA · Accepted Answer · 2011-09-29 11:57:18Z

0

Think NO_DATA_FOUND exception is only raised by a SELECT statement which you aren't using. Try testing SQL%COUNT and output as required.

answered Sep 29, 2011 at 11:57

OTTA

1,0797 silver badges8 bronze badges

1 Comment

Kent Pawar Over a year ago

...I guess you meant to write SQL%ROWCOUNT

Collectives™ on Stack Overflow

Stored procedure exception handling

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related