|
Problem IC66498
|
Status: Closed |
String truncation does not raise an exception in PL/SQL code |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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;
/ |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 UDB Version 9.7 *
****************************************************************
* PROBLEM DESCRIPTION: *
* 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; *
* *
* / *
* ---- *
****************************************************************
* 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.7 Fix *
* Pack *
* 3 or later. *
**************************************************************** |
| Local Fix: |
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.7 Fix Pack 3, 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;
/
If necessary, the conditional compilation features of DB2 can
be used to isolate DB2-specific code in your PL/SQL
applications. For example:
update db cfg for testdb using SQL_CCFLAGS DB2_TRUNC:TRUE;
create or replace procedure test
is
$IF $$DB2_TRUNC $THEN
TRUNCATION_ERROR exception;
pragma db2_exception_init(TRUNCATION_ERROR, '01004');
$END
v_text varchar(1);
begin
v_text := 'Hello';
dbms_output.put_line('FAIL: No exception detected');
exception
$IF $$DB2_TRUNC $THEN
when TRUNCATION_ERROR or OTHERS then
$ELSE
when OTHERS then
$END
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/v9r7/topic/com.i
bm.db2.luw.apdv.plsql.doc/doc/c0053876.html |
| available fix packs: |
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
|
| Solution |
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.7 Fix Pack
3 or later. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC67332
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 19.02.2010
07.02.2012
07.02.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
