Hello,

As there is NO_DATA_FOUND exception in oracle similarly what and how can we handle a NO_DATA_FOUND exception in a DB2 Procedure eg.

start procedure
select field_name from table_name where <condition>
end procedure

if there are no fields in this table "table_name" i.e if there are no records in this table which meets the given condition then I would like to handle NO_DATA_FOUND exception in DB2, can anybody give me some idea as to how this is handled in DB2 procedures, also plz provide me a piece of code that does this.

thanks

as far as i know DB2 does not support this feature.... yet.
Just wait 5 more weeks. The next version of db2 (V9.7) DOES understand the PL-SQL syntax.

In the mean time: a way to avoid this is to use the MERGE verb intead of UPDATE with an INSERT in the not-found-block.

? why

DECLARE V_ROW_COUNT INTEGER DEFAULT 0;

-- Declare Exception Handlers

DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET V_ROW_COUNT = 1 ;

Fascinating!
Still: I used to be a cobol programmer using embedded SQL. Also in cobol you can test the SQLCODE act accordonly. However, SQLCODES 0 and 100 are ANSI standards (and an update throws another non-ansi code if a row is not found) so I always propagated to check first (select count(*)) and based upon the ANSI-value of SQLCODE do an update or an insert. The same can be coded in (PL)SQL(PL) and clean and portable code as a result.

But, like I posted before: this is all history because we have the MERGE verb now