DB2 FOR LUW / DB2FORLUW / 980 - DB2

In PL/SQL, when a string value is stored into a CHAR or VARCHAR 
variable that is too small to hold it, a VALUE_ERROR exception 
should be raised.  DB2 is not raising the exception as expected. 
 The following example demonstrates the problem: 
 
CREATE OR REPLACE FUNCTION test_func_plsql 
RETURN VARCHAR 
IS 
  v_text VARCHAR(1); 
BEGIN 
  v_text := 'Hello'; 
  RETURN 'FAILURE: Exception was not dispatched.'; 
 
EXCEPTION 
  WHEN VALUE_ERROR THEN 
 
    RETURN 'SUCCESS: Exception dispatched.'; 
 
END; 
/ 
 
BEGIN 
  test_func_plsql; 
END; 
/

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.8                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please see the LOCAL FIX section for detailed information    * 
* about                                                        * 
* how to handle truncation warnings as exceptions in PL/SQL    * 
* code.                                                        * 
* These recommended workarounds require DB2 Version 9.8 Fix    * 
* Pack                                                         * 
* 1 or later.                                                  * 
****************************************************************

This problem occurs because string truncation results in a 
warning condition in SQL routines under DB2, instead of the 
exception condition that is expected by PL/SQL.  Beginning with 
DB2 Version 9.8 Fix Pack 1, you can map user-declared 
conditions to specific DB2 SQLSTATE values, including warnings, 
using PRAGMA DB2_EXCEPTION_INIT. 
 
Here is an example of a PL/SQL routine with a condition handler 
to detect DB2 truncation warnings, SQLSTATE '01004': 
 
declare 
  TRUNCATION_ERROR exception; 
  pragma db2_exception_init(TRUNCATION_ERROR, '01004'); 
  v_text varchar(1); 
begin 
  v_text := 'Hello'; 
  dbms_output.put_line('FAIL: No truncation detected'); 
exception 
  when TRUNCATION_ERROR then 
    dbms_output.put_line('PASS: Truncation detected'); 
end; 
/ 
 
Please note that an OTHERS clause in an exception handler will 
not catch this type of exception by itself; if you wish to catch 
a truncation warning with an OTHERS clause, you must specify the 
exception name explicitly, along with OTHERS.  For example: 
 
declare 
  TRUNCATION_ERROR exception; 
  pragma db2_exception_init(TRUNCATION_ERROR, '01004'); 
  v_text varchar(1); 
begin 
  v_text := 'Hello'; 
  dbms_output.put_line('FAIL: No exception detected'); 
exception 
  when TRUNCATION_ERROR or OTHERS then 
    dbms_output.put_line('PASS: Exception detected'); 
end; 
/ 
 
You can find additional information about PRAGMA 
DB2_EXCEPTION_INIT in the DB2 Information Center, at: 
 
http://publib.boulder.ibm.com/infocenter/db2luw/v9r8/topic/com.i 
bm.db2.luw.apdv.plsql.doc/doc/c0053876.html

DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

Please see the LOCAL FIX section for detailed information about 
how to handle truncation warnings as exceptions in PL/SQL code. 
These recommended workarounds require DB2 Version 9.8 Fix Pack 
1 or later.

not known / see Local fix

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

19.03.2010
07.02.2012
07.02.2012

9.8.,
9.8.FP3