java.math.BigInteger cannot be cast to java.math.BigDecimal

Question

We are migrating from Oracle to PostgreSQL. Some queries that were specific to Oracle had to be changed to Postgres equivalents. Following is one such commit:

Initially,

Query query = getEntityManager().createNativeQuery("SELECT PC_SITE_GROUP_ID_SEQ.NEXTVAL from DUAL");
BigDecimal result = (BigDecimal) query.getSingleResult();

was changed to,

Query query = getEntityManager().createNativeQuery("SELECT NEXTVAL('pc_site_group_id_seq')");
BigDecimal result = (BigDecimal) query.getSingleResult();

On running, after the change, it threw error,

java.math.BigInteger cannot be cast to java.math.BigDecimal

My question is why it was working before? Just a reminder, the first change was run on Oracle database while the second one on Postgres database. Please help.

"My question is why it was working before" - just a guess but it might be the oracle jdbc driver that mapped the value to a BigDecimal. What does that "DUAL" mean here? How did you define your sequence? Could it be a floating/fixed point number in the Oracle case? — Thomas
– Thomas, Commented Jan 23, 2018 at 9:45
@Thomas it's the oracle "syntax requires a table, but there's no need for a table, so use DUAL as a pretend table" way. — Kayaman
– Kayaman, Commented Jan 23, 2018 at 9:48
@Life_Hacker It might be an issue with the Oracle DB Driver you have used, Can you try it with a latest DB driver. — J-J
– J-J, Commented Jan 23, 2018 at 10:00
I can't use Oracle. As said earlier we are migrating from it. — Rajat Mishra
– Rajat Mishra, Commented Jan 23, 2018 at 10:18

score 4 · Accepted Answer · 2018-01-23 10:39:10Z

4

Oracle has no "integer" data type in the database so everything is mapped to BigDecimal by the driver.

In Postgres there is a clear distinction between an integer (or bigint) and a decimal, so the driver returns a BigInteger for bigint values.

And a sequence always emits bigint values, so the driver returns a BigInteger for them.

edited Jan 23, 2018 at 10:39

answered Jan 23, 2018 at 10:01

user330315

Sign up to request clarification or add additional context in comments.

8 Comments

J-J Over a year ago

Can you take a look at this url, docs.oracle.com/cd/B12037_01/server.101/b10759/… It is mentioned that nextVal will return an integer

user330315 Over a year ago

@Jobin: Oracle has no native integer data type. Everything is a number which could contain decimals. And regardless on what the Oracle server sends, the driver converts this to a BigDecimal. Plus the default max value for a sequence would fit into a BigInteger - so the driver has to use a BigDecimal

Cowboy Farnz Over a year ago

BigDecimal (BigDecimal, without precision (large integer Postgres or double)) in Oracle and in Postgres db it has the equivalent column using an integer datatype.

Kayaman Over a year ago

So it's just a case of Oracle being Oracle.

user330315 Over a year ago

@Kayaman: no, really. BigDecimal is the only choice the driver has, because the potential values of a sequence (actually a number in general) do not fit into a BigInteger

|

Collectives™ on Stack Overflow

java.math.BigInteger cannot be cast to java.math.BigDecimal

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related