CODEWITHSUNDEEP
debuggingplsqldb2
David SCHERRER
David SCHERRER

Reputation: 73

Cannot identify the origin of an error - hypothesis : decimal format conflicts

While executing a DB2 (V8) Stored Procedure, I get the following error :

SQL0304N A value cannot be assigned to a host variable because the value is not within the range of the host variable's data type. SQLSTATE=22003

I did not set any kind of tracing or specific error handling and as the error only occurs in our client's validation environment that I'm not allowed to play with, I do not have many options but analyze my code again.

Here is the result of my current analysis. Google is not much of a help...

My "10 pages" procedure creates a CURSOR over a set of data, goes though it and computes values for each element to be inserted it in a table.

I have checked (hopefully) all my variables types versus data types used to fill them and versus the data types of the target table and I do not see any conflict there.

Since there are a lot of decimal numbers, multiplications and additions, my only hypothesis is that a computed value becomes too large for a defined variable. Could anyone confirm that would be the "correct error" ? And would it also apply if the number of digits after the decimal point generated by computing is greater than allowed by the targeted variable type (eg. 100000.123 in decimal(6,2)) ?

I also tried to find a way to debug db2 pl sql through a client but I did not find any solution. If you have any suggestion...

Many thanks in advance for any clue :)

Upvotes: 1

Views: 426

Answers (1)

David SCHERRER
David SCHERRER

Reputation: 73

I answer myself...

First, my last question => I did not find any way to debug db2 pl sql through a client (with DB2 V8 at least).

After I was authorized to work on our integration client's environment, I could confirm my hypothesis was right. The variable format receiving the multiplication was sometimes too small (decimal(10,2)) for the computed result.

The solution adopted was to change the variable format to decimal(15,2) and since the final value to insert still had to be decimal(10,2) upon client's requirements, we validated the following with our client :

1-Check the variable value :

if (myval > 9999999,99)
then 
  set myval = 9999999,99;
end if;

=> "back to decimal(10,2) requirement"

2-Get back to decimal(10,2) at insert : This last bit of code also solves the issue when there are too many digits after the decimal point. That was causing an error as well at insert time

insert into mytable values (
... ,
CAST(myval AS DECIMAL( 12 , 2 )),
...
)

Upvotes: 1

Related Questions