We’re using DI 11.7.0.1 here. I have pasted the following from the technical manuals:
Input that is more precise than the data type of the column or variable in
which it is stored is rounded. Input out of range (absolute value is too large)
causes a runtime error.
If we define $L_DECIMAL as decimal(4,2) and use the following script:
$L_DECIMAL = 123456789.55555
print($L_DECIMAL);
The printed output is:
123456789.56
I would have expected a runtime error as the absolute value is far larger than the precision of the variable!
This is playing hell with our CSV input validations. Input columns in the file format are specified as decimal (4,2) and values far larger are being let in without error. Can anyone advise?
I think you have casted that string into a decimal (4,2). I think it will automatically round that string to a decimal(4,2).
I have had customer that were concerned about that. I have placed in larger decimal precision and used trunc() function in DI.
About the error/warning.
I am not sure but DI may give you a warning about decimal out of range.
If you want to throw an error you will have to use something like isvalid_decimal() function or manually check length of input as string and make sure the length after the “.” is 2.
If those functions fail throw an exception to stop execution of the job.
i.e raise_exception
The fraction values are fine. The described problem is about the number of digits. A decimal(4,x) can handle 4 digit numbers at the max, however, it seems DI does not raise an error and processes the decimal value ignoring the digit constraint. I have checked, if you assign a value too large to handle to an INT column, the engine(!) does raise an error. So should the decimal(4,2).
And it’s not about how to workaround, it’s about the default DI behaviour.
Werner is correct. The DI Reference Guide specifically states that raising an absolute value too large for a prescribed decimal precision will throw an error.
What’s happening to us is that DI is carrying the decimal value through, and then attempting to insert it into the database, resulting in a database error. We can’t trap it in DI. Using is_valid_decimal isn’t working either.
I’ve attached a simple job that illustrates the dilemma. What happens in this job is that we read a flat file, grab a value, and shove it into Oracle. Oracle rejects the insert as the decimal value is too large.
You will see that in this job I use is_valid_decimal a on a few strings. All of them return ‘1’, as noted in the trace log.
Following the thread, I can see the incorrect behaviour has been present since at least 2007. My question is really, has it ever been fixed and then regressed, or never fixed.
(BOB member since 2004-12-17)
(BOB member since 2004-06-09)
(BOB member since 2008-07-17)