DB2 FOR LUW / DB2FORLUW / 970 - DB2

If you have a PL/SQL package procedure that references a 
package-level cursor in an exception handler, or an SQL PL 
module procedure that references a module-level cursor in a 
condition handler, the exception or condition is immediately 
dispatched again on entry to the handler, and the handler body 
does not run. 
 
In the following PL/SQL example, C1 is defined as a cursor in 
package RAISE_TEST.  The exception raised by the 'raise 
NO_DATA_FOUND;' statement in procedure RUN is correctly 
dispatched to the appropriate WHEN clause of the surrounding 
exception handler.  However, the exception is immediately 
re-dispatched before the handler body can run, resulting in an 
unhandled exception that causes procedure RUN to terminate 
prematurely. 
 
connect to test; 
 
set sqlcompat plsql; 
set serveroutput on; 
 
create table T1(COL1 integer); 
 
create or replace package raise_test 
as 
  CHECK_ERROR_EXCEPTION exception; 
 
  cursor C1 is 
    select COL1 from T1; 
 
  procedure run; 
end; 
/ 
 
create or replace package body raise_test 
as 
  procedure run 
  is 
  begin 
    open C1; 
    raise NO_DATA_FOUND; 
  exception 
    when NO_DATA_FOUND then 
      -- The body of this exception handler should run.  It does 
not. 
      dbms_output.put_line('Exception: NO_DATA_FOUND'); 
      close C1; -- note the cursor reference 
  end run; 
 
end raise_test; 
 
/ 
 
begin 
  raise_test.run; 
exception 
  when OTHERS then 
    dbms_output.put_line('Error: The exception should be handled 
in RUN.'); 
end; 
/ 
 
connect reset; 
terminate; 
 
The output from this test is: 
 
DB20000I  The SQL command completed successfully. 
 
Error: The exception should be handled in RUN. 
 
It should be: 
 
DB20000I  The SQL command completed successfully. 
 
Exception: NO_DATA_FOUND

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See the APAR description.                                    * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade DB2 UDB version 9.7 fixpack 2.                       * 
****************************************************************

If possible, redefine the package- or module-level cursor as a 
local cursor within the routines that use it.  Using the example 
in the error description: 
 
create or replace package raise_test 
as 
  CHECK_ERROR_EXCEPTION exception; 
 
  -- Move the cursor into the RUN procedure: 
  -- cursor C1 is 
 
    -- select COL1 from T1; 
 
  procedure run; 
end; 
/ 
 
create or replace package body raise_test 
as 
  procedure run 
  is 
    cursor C1 is 
      select COL1 from T1; 
  begin 
    open C1; 
    raise NO_DATA_FOUND; 
  exception 
    when NO_DATA_FOUND then 
      dbms_output.put_line('Exception: NO_DATA_FOUND'); 
      close C1; 
  end run; 
 
end raise_test; 
 
/

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 9.7 FixPack 2.

See LOCAL FIX

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

25.02.2010
17.05.2010
17.05.2010

9.7.FP2