Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: NO DATA FOUND IN db2

    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
    Last edited by donraja_ht; 04-22-09 at 06:41.

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by dr_te_z
    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.
    ? 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 ;
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •