0

I have a Oracle Database version 11g Release 11.2.0.3.0 and after updating a numeric (30,20) row, I discovered some awful behavior. Running update table set column = 9999999999.00390625 where someClause was okay, but selecting the same value resulted in 9999999999.003910450000000000.

I know about this issue and noticed stack overflow's warning about it. My question is:

Using Oracle 11g, is there any method or means available to deal with this imprecision? I thought about saving my number in 2 pieces (decimal and integer parts) and using some logic to sum them up before giving it back to who requested. But is this some boilerplate code?

EDIT

This is what all_tab_cols returned with respective where clause:

DATA_TYPE   NUMBER
DATA_LENGTH 22
DATA_PRECISION  30
DATA_SCALE  20
NULLABLE    Y
COLUMN_ID   2
DATA_DEFAULT    <Long>
NUM_DISTINCT    12
LOW_VALUE   80
HIGH_VALUE  BE640D51
DENSITY 0,0833333333333333
NUM_NULLS   0
NUM_BUCKETS 1
LAST_ANALYZED   07/07/2011 15:49:11
SAMPLE_SIZE 56  
GLOBAL_STATS    YES
USER_STATS  NO
AVG_COL_LEN 4
CHAR_LENGTH 0
V80_FMT_IMAGE   NO
DATA_UPGRADED   YES
HIDDEN_COLUMN   NO
VIRTUAL_COLUMN  NO
SEGMENT_COLUMN_ID   2
INTERNAL_COLUMN_ID  2
HISTOGRAM   NONE

All empty column values were omitted, as well as column, table and user names. I checked that no triggers are applied to this table and our Java application showed same value as PL/SQL Developer (pink one) as a proof this isn't some client problem.

Actually, using 0.00390625 instead of 9999999999.00390625 resulted no misbehavior, kind of proving the floating point problem's cause.

rado
  • 5,720
  • 5
  • 29
  • 51
  • 1
    The ANSI numeric type is treated the same as the natove number type ([doc ref](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Types.html#GUID-0BC16006-32F1-42B1-B45E-F27A494963FF). What is the actual column data type - what DDL was used to create it, and what does `all_tab_cols` report, for the actual data type, scale and precision? Also, which client are you seeing this is; and can you confirm the actual stored value via `dump()`; and have you ruled out someone else, or a trigger, modifynig the value you entered? – Alex Poole Dec 06 '18 at 16:09
  • Well, a `number(30,20)` column, which is what you seem to have, doesn't display the behaviour you describe. You only have to worry about that kind of discrepancy with floats - and that isn't what you have... [This demo](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=5e8b8ef6843e8235d51405cc38e015f0) is on 11.2.0.2, and works the same on 11.2.0.5; it's unlikely there's a bug that horrendous specific to 11.2.0.3. – Alex Poole Dec 06 '18 at 16:38

0 Answers0