3 replies to this topic

[rjhog]

Hi,

I have a small problem with numbers calculated in PL/SQL and used in PRO IV.

Basically PRO-IV is writing a number to a table. The PRO IV file definition has this number defined as ORANUM (storage format S20.20) This number is then manipulated by some PL/SQL code called by a trigger. The manipulation results in a number with more than 20 decimal places. The next time PRO-IV uses this number, it is truncating the additional decimal places.
After this truncation has occurred, we get a very strange Oracle error.
Say the value of the number is now 0.1234567, if you write a query that does this:

SELECT * FROM table WHERE value - 0.1234567 = 0

This completely throws Oracle, to the point where it drops the database connection.
Also, if you write a query to find the value, nothing is returned. ie:

SELECT * FROM table WHERE value = 0.1234567

returns no rows.

The solution is simple - round the PL/SQL calculation, but can anyone explain what PRO IV does to the number to cause this behaviour?

Rob

[Richard Bassett]

The fundamental limit on numbers in ProIV is 20 significant figures (L and ## variables ) or less (N and # variables).
Thus if you have more than 20 decimal places and the first decimal place is nonzero (or of course if there is any integer part) then some of your trailing decimals will necessarily be truncated.

Any external format (such as S20.20) should be completely irrelevant when using ORANUM because the purpose of ORANUM is to exchange numbers with Oracle in Oracle native format instead of a character string format (because it speeds things up).

The "S20.20" format was, as far as I know, a hack to allow up to 20 integer digits OR up to 20 decimal places when exchanging numbers with Oracle in character format (eg. with the old NUMBER external type). Note this cannot handle truly floating-point numbers whereas ORANUM can.

There was a slight problem with the earlier ProIV releases that supported ORANUM but I don't know if that's relevant to your problem - are you on an up-to-date release?

If you try to simply display the 'bad' number you think ProIV has written into your table - via SQL using some high-precision 'revealing' format say - what do you see?

[rjhog]

Richard,

We are currently using V5 R109. We had problems with ORANUM when we first went to V5, but we resolved these by upgrading.

SQL:
SELECT to_char(field)
FROM table

Returns:

3.06111800000000000

However, if I say

SELECT *
FROM table
WHERE field = 3.061118

No rows are found.

Rob

[Richard Bassett]

Oracle native number format can handle 38 (I think) digits of (decimal) floating point.

Those trailing zeroes are trying to tell you that there are more digits but SQL is clipping the output by default.
Get SQL to show you say 40 decimal places and see what's really there (that's what I meant by high-precision format - sorry if that wasn't clear).