JDBC Numeric Overflow with SQL error code: 17026

Usually when you see such an error — Numeric Overflow SQL error code 17026 in your JAVA application code, it means the data type used in the java code is not bigger enough to hold the result data fetched.

Oracle’s Java Developer guide has a mapping table called Java Type to SQL Type Mappings:

Java Type Java DB, Derby, CloudScape Oracle DB2 Sybase MS-SQL Server MySQL Server 
boolean, java.lang.BooleanSMALLINTNUMBER(1)SMALLINTBITBITTINYINT(1)
int, java.lang.IntegerINTEGERNUMBER(10)INTEGERINTEGERINTEGERINTEGER
long, java.lang.LongBIGINTNUMBER(19)INTEGERNUMERIC(19)NUMERIC(19)BIGINT
float, java.lang.FloatFLOATNUMBER(19,4)FLOATFLOAT(16)FLOAT(16)FLOAT
double, java.lang.DoubleFLOATNUMBER(19,4)FLOATFLOAT(32)FLOAT(32)DOUBLE
short, java.lang.ShortSMALLINTNUMBER(5)SMALLINTSMALLINTSMALLINTSMALLINT
byte, java.lang.ByteSMALLINTNUMBER(3)SMALLINTSMALLINTSMALLINTSMALLINT
java.lang.NumberDECIMALNUMBER(38)DECIMAL(15)NUMERIC(38)NUMERIC(28)DECIMAL(38)
java.math.BigIntegerBIGINTNUMBER(38)BIGINTNUMERIC(38)NUMERIC(28)BIGINT
java.math.BigDecimalDECIMALNUMBER(38)DECIMAL(15)NUMERIC(38)NUMERIC(28)DECIMAL(38)
Java Type to SQL Type Mappings

As the mapping table above shows, to fetch data and store it from a column with Oracle data type NUMBER(10) , you need to at least define a variable type with int and use getInt of ResultSet interface.

Sometimes it still is not enough because in Oracle, NUMBER(10) means 10 digits with precision 10 and scale 0, the value range stored in such a column could be much larger than Java int type which rangs from -2^31 ~ 2^31 -1 can hold. That’s because internally

Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle Database does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.” — from Oracle Concepts.

So to safely handle NUMBER(10), it’s better to suggest your JAVA devloper to use the JAVA type long instead of the type int and use the method getLong accordingly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s