| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC74343 | Status: Closed |
IN CLPPLUS, THE MESSAGE OUTPUTED BY DBMS_OUTPUT MODULE IS CARRIE D OVER IN THE NEXT SCREEN WHILE CONDUCTING WITH THE EXCEPTION | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
Regarding PLSQL anonymous block or procedures run in CLPPLUS,
the content in dbms_output buffer is not printed out in time if
it is conducted with the EXCEPTION block with RAISE statement,
but the output will be carried over in the next round of the
query or dbms_output run.
For example:
If you have a PL/SQL anonymous block as below:
set serveroutput on
DECLARE
START_CARD number := 0;
END_CARD number(9) := 10000;
min_cnt number;
max_cnt number;
from_card number(9);
TO_CARD number(9);
v_num_per_batch number := 1000;
AFFECTED_ROW number := null;
cnt number;
BEGIN
min_cnt := round(START_CARD/v_num_per_batch);
max_cnt := round(END_CARD/v_num_per_batch)-1;
FOR k IN min_cnt..max_cnt
LOOP
from_card := k * v_num_per_batch;
to_card := (k+1) * v_num_per_batch;
dbms_output.put_line ('k:'||to_char(k)||'
from:'||to_char(from_card)||' to:'||to_char(to_card));
--This statement will trigger exception as oct1 has unique key
constraint
insert into oct1 values('abc');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error Abort. Halt.');
commit;
RAISE;
END;
/
While run above blocks in CLPPLUS, you can perceive that you
only get result without having any message brought out by
dbms_output.put_line like "k:0 from:0 to:1000" and "Error Abort.
Halt." as below:
ERROR near line 1:
SQL0803N One or more values in the INSERT statement, UPDATE
statement, or foreign key update caused by a DELETE statement
are not valid because the primary key, unique constraint or
unique index identified by "1" constrains table "HUJINPEI.OCT1"
from having duplicate values for the index key.
But if you run the SELECT query or the exec dbms_output.put_line
trying to print something right away, the message in the
dbms_output buffer was finally displayed prior to anything that
we wanna print out, like below:
SQL>exec dbms_output.put_line('New line')
k:0 from:0 to:1000
Error Abort. Halt.
New line
DB250000I: The command completed successfully. | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Regarding PLSQL anonymous block or procedures run in *
* CLPPLUS, *
* the content in dbms_output buffer is not printed out in time *
* if *
* it is conducted with the EXCEPTION block with RAISE *
* statement, *
* but the output will be carried over in the next round of the *
* *
* query or dbms_output run. *
* *
* *
* *
* For example: *
* *
* If you have a PL/SQL anonymous block as below: *
* *
* *
* *
* set serveroutput on *
* *
* *
* *
* DECLARE *
* *
* START_CARD number := 0; *
* *
* END_CARD number(9) := 10000; *
* *
* min_cnt number; *
* *
* max_cnt number; *
* *
* from_card number(9); *
* *
* TO_CARD number(9); *
* *
* v_num_per_batch number := 1000; *
* *
* AFFECTED_ROW number := null; *
* *
* cnt number; *
* *
* BEGIN *
* *
* min_cnt := round(START_CARD/v_num_per_batch); *
* *
* max_cnt := round(END_CARD/v_num_per_batch)-1; *
* *
* *
* *
* FOR k IN min_cnt..max_cnt *
* *
* LOOP *
* *
* from_card := k * v_num_per_batch; *
* *
* to_card := (k+1) * v_num_per_batch; *
* *
* dbms_output.put_line ('k:'||to_char(k)||' *
* *
* from:'||to_char(from_card)||' to:'||to_char(to_card)); *
* *
* --This statement will trigger exception as oct1 has unique *
* key *
* constraint *
* *
* insert into oct1 values('abc'); *
* *
* END LOOP; *
* *
* *
* *
* EXCEPTION *
* *
* WHEN OTHERS THEN *
* *
* dbms_output.put_line ('Error Abort. Halt.'); *
* *
* commit; *
* *
* RAISE; *
* *
* *
* *
* END; *
* *
* / *
* *
* *
* *
* While run above blocks in CLPPLUS, you can perceive that you *
* *
* only get result without having any message brought out by *
* *
* dbms_output.put_line like "k:0 from:0 to:1000" and "Error *
* Abort. *
* Halt." as below: *
* *
* *
* *
* ERROR near line 1: *
* *
* SQL0803N One or more values in the INSERT statement, UPDATE *
* *
* statement, or foreign key update caused by a DELETE *
* statement *
* are not valid because the primary key, unique constraint or *
* *
* unique index identified by "1" constrains table *
* "HUJINPEI.OCT1" *
* from having duplicate values for the index key. *
* *
* *
* *
* But if you run the SELECT query or the exec *
* dbms_output.put_line *
* trying to print something right away, the message in the *
* *
* dbms_output buffer was finally displayed prior to anything *
* that *
* we wanna print out, like below: *
* *
* *
* *
* SQL>exec dbms_output.put_line('New line') *
* *
* k:0 from:0 to:1000 *
* *
* Error Abort. Halt. *
* *
* New line *
* *
* DB250000I: The command completed successfully. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v9.7FP5 *
**************************************************************** | |
| Local Fix: | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows | |
| Solution | |
The problem is fixed by a broader change. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 08.02.2011 10.12.2011 10.12.2011 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP5 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.5 |
|