DB2 - Problem description
Problem IC66926 | Status: Closed |
DB2 CAN NOT HOOK NO_DATA_FOUND INSIDE OF A FOR CURSOR LOOP IN PL/SQL. | |
product: | |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 | |
Problem description: | |
The following PL/SQL script does not display the "Bottom of FOR loop" message. Assume that table TEST2 contains one or more rows, and that TEST1 is empty: create or replace procedure cursor_test is cursor c2 is select col1 from test2; testv varchar2(10); begin for c2_rec in c2 loop dbms_output.put_line('Top of FOR loop'); begin select col1 into testv from test1; exception when no_data_found then dbms_output.put_line('NO DATA FOUND'); when others then null; end; dbms_output.put_line('Bottom of FOR loop'); end loop; end; / After the first execution of the SELECT ... INTO statement, the FOR loop exits prematurely and the exception clause for NO_DATA_FOUND is not run. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * The following PL/SQL script does not display the * * "BottomofFORloop" message. Assume that table TEST2 contains * * one ormorerows, and that TEST1 is empty:create or * * replaceprocedure cursor_testiscursor c2 isselect col1 * * fromtest2;testv varchar2(10);beginfor c2_rec in * * c2loopdbms_output.put_line('Top of FOR loop');beginselect * * col1into testv from test1;exceptionwhen * * no_data_foundthendbms_output.put_line('NODATAFOUND');when * * othersthennull;end;dbms_output.put_line('Bottom of FOR * * loop');endloop;end;/After the first execution of the SELECT * * ... INTOstatement,theFOR loop exits prematurely and the * * exceptionclause forNO_DATA_FOUND is not run. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 UDB version 9.8 fix pack 3. * **************************************************************** | |
Local Fix: | |
Wrap the SELECT ... INTO statement in a separate PL/SQL function, then invoke this function to obtain the column value; for example: create or replace function get_test1_col1 return varchar2(2) as declare v varchar2(2); begin select col1 into v from test1; return v; end; / -- In the calling procedure... for c2_rec in c2 loop dbms_output.put_line('Top of FOR loop'); begin testv := get_test1_col1; exception when no_data_found then dbms_output.put_line('NO_DATA_FOUND'); when others then null; end; This will produce the intended behaviour. | |
available fix packs: | |
DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows | |
Solution | |
Problem was first fixed in DB2 UDB Version 9.8 Fix Pack 3. | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 05.03.2010 08.11.2012 08.11.2012 |
Problem solved at the following versions (IBM BugInfos) | |
9.7.FP3, 9.8.FP3 | |
Problem solved according to the fixlist(s) of the following version(s) | |
9.8.0.3 |