5

During a software project we stumbled upon a bug, where we inserted a large number (17 digits) from Java into Oracle DB procedure. The number changed it's value sometimes +1 or -1. Sometimes stayed the same. We checked the oracle DB driver debug log and saw that it printed the correct number. We also tried restarting Java application server between each request, to eliminate wierd caching errors. Still got the same results.

Any ideas why this is happening?

Here's the code:

// Datasource configuration
<New class="oracle.jdbc.pool.OracleDataSource">
    <Set name="DriverType">thin</Set>
    <Set name="URL">jdbc:oracle:thin:@xxxx</Set>
    <Set name="User">uuuu</Set>
    <Set name="Password">pppp</Set>
</New>

// JAVA
SimpleJdbcCall testMsg = new SimpleJdbcCall(dataSource).
    withSchemaName(schema).
    withCatalogName(catalog).
    withProcedureName("test_msg");

public void testMessage(Long n) {
  testMsg.execute(n, n, n.toString());
}

// PL_SQL
procedure test_msg(
    i           integer,
    n           number,
    v           varchar2
) is
    log_prfx log_pkg.t_log_prfx := 'test_msg: ';
begin
    g_log.log_debug(log_prfx||'i='||to_char(i));
    g_log.log_debug(log_prfx||'n='||to_char(n));
    g_log.log_debug(log_prfx||'v='||v);
end test_msg;

Now calling 

testMessage(10000000000000005L);
testMessage(10000000000000007L);
testMessage(10000000000000009L);

End up with logs like 

test_msg: i=10000000000000005
test_msg: n=10000000000000005
test_msg: v=10000000000000005


test_msg: i=10000000000000008
test_msg: n=10000000000000008
test_msg: v=10000000000000007

test_msg: i=10000000000000008
test_msg: n=10000000000000008
test_msg: v=10000000000000009

Versions we are using.

  • Spring 3.2.11
  • Oracle driver ojdbc7_g-12.1.0.2 (we also tested with 11.2.0.4)
  • Oracle DB is version 12.1.0.1.0
  • Jetty 9.2.2 and JBoss 7 (the same behaviour appeared in both)
Joosep Simm
  • 462
  • 1
  • 5
  • 13
  • 2
    I'm not sure whether it helps, but maybe you should use BigDecimal instead of Long. Due it's nature the corresponding type to Oracle's NUMBER is Java's BigDecimal. Anyway you mostly use "numbers" as IDs you do not use them for any computations. – ibre5041 Sep 23 '14 at 08:19
  • Changed the order, the log table was just ordered in the other way before. – Joosep Simm Sep 23 '14 at 10:46
  • 1
    I'll try the BigDecimal and BINARY_DOUBLE later. Will let you know about the results.' – Joosep Simm Sep 23 '14 at 10:46
  • BTW you might expect even more curious issues when using BINARY_DOUBLE. Image that that database gets migrated onto AIX(PowerPC CPU). Then your double(Java/Intel) has to be converted onto different representation (although also conforming to IEEE standard). BINARY_DOUBLE represents the native float representation of the architecture where the DB server is running. – ibre5041 Sep 24 '14 at 08:36

2 Answers2

0

The number you're passing in is larger than integer max value for oracle, so unpredictable results can occur. Max int value in oracle is 2147483647. Use proper type of your number as described in oracle docs

Nadir
  • 1,369
  • 1
  • 15
  • 28
  • 2
    It doesn't explain why this happens with NUMBER type. Or does it? – Joosep Simm Sep 23 '14 at 07:16
  • 2
    INTEGER is not a native type in Oracle, it is merely an alias for NUMBER(38). (In PL/SQL only there is a datatype PLS_INTEGER for signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits, but that is not the case here.) More likely it is some rounding error from a floating point representation to a NUMBER. Either try on the Java side to use BigDecimal instead of Long as suggested by Ivan, or try on the PL/SQL side to use BINARY_DOUBLE instead of NUMBER. – Kim Berg Hansen Sep 23 '14 at 09:18
  • 2
    To expand on @KimBergHansen's comment - IEEE double-precision binary floating point is limited to 15.95 decimal digits of accuracy (i.e. some - even most" 16 digit numbers can be represented - but not all :-), but at 17 digits you've exceeded the limit and, yes, you can get funky behavior like this. Solution - pass everything back and forth between your app and the database as a character string and convert it as needed. Best of luck. – Bob Jarvis - Слава Україні Sep 23 '14 at 17:54
  • Sorry about no line spaces. Seems I'm not able to produce those... I made a few tests now. 1) BigDecimal in from Java. I couldn't pass it from Java. Got "Invalid column type binary_double". I didn't want to mess with this, so dropped this test. 2) Passed in BigDecimal from Java Both "integer" and "number" types worked nicely 3) Tested 16 and 17 digits with Long from Java 16 digits worked nicely. 17 digits got the error. – Joosep Simm Sep 24 '14 at 07:41
0

So in the end I went with passing in BigDecimal from Java and using NUMBER in plsql (though integer would have worked as well).

Thanx Ivan for this tip!

Joosep Simm
  • 462
  • 1
  • 5
  • 13