Here is the description of the problem we are having with Oracle (ver. 7.3.3).
Our database has two tables that are combined in a certain way in a view
to be queried by the front-end Java based client.
This view has some calculated columns, with SUM and TO_NUMBER
functions applied to them.
The front-end client totally relies on the database to provide the right
data type for each column in the view. That means that it doesn't even know
the data types of these columns.
Now, the real problem is that Oracle functions SUM() and TO_NUMBER()
both return a NUMBER data type. This type is specific to Oracle and by
default (without any precision specifier) it is equivalent to DOUBLE. It is
implemented in this way to accommodate all possible numeric types.
For any specific type you have to use the precision: NUMBER(9) is for
integer, NUMBER(9,2) is for float, etc.
Unfortunately, SUM() and TO_NUMBER() both return type double.
Again, we have a view and we cannot enforce a data type on a column
(like we would be doing it on a table).
