Skip to Main Content

Java and JavaScript in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

What could cause a Numeric Overflow exception?

561854Oct 23 2009 — edited Oct 27 2009
We have a jdbc application that reads from one Oracle table and builds a prepared statement to do an insert into a different table.

We are getting the following exception:
java.sql.SQLException: Numeric Overflow
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at
oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.j
ava:7465)
at
oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:103
)
at
oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:521)
at org.iobis.Moveit.setPlaceholderValue(Moveit.java:169)
at org.iobis.Moveit.moveit(Moveit.java:85)
at org.iobis.Moveit.main(Moveit.java:327)

The line of code referenced (Moveit.java:169) is part of a switch statement that picks the appropriate data type:
public void setPlaceholderValue ( int placeHolderNum, ResultSet rs, String oracleColumnName, PreparedStatement stmt ) throws Exception {

int columnIndex = rs.findColumn(oracleColumnName) ;
int columnType = rs.getMetaData().getColumnType(columnIndex) ;

if ( rs.getObject(oracleColumnName) != null ){
switch (columnType) {
case Types.VARCHAR: stmt.setString(placeHolderNum, rs.getString(columnIndex)); break;
case Types.INTEGER: stmt.setInt(placeHolderNum, rs.getInt(columnIndex)); break ;
case Types.DATE: stmt.setDate(placeHolderNum, rs.getDate(columnIndex)); break;
case Types.NUMERIC: stmt.setInt(placeHolderNum,rs.getInt(columnIndex)); break ;
case Types.FLOAT: stmt.setFloat(placeHolderNum, rs.getFloat(columnIndex)); break ;
case Types.TIMESTAMP: stmt.setTimestamp(placeHolderNum, rs.getTimestamp(columnIndex)); break ;
default: throw new SQLException("The result set column type " + rs.getMetaData().getColumnType(columnIndex) + " was not recognized. see http://java.sun.com/j2se/1.5.0/docs/api/");
}
} else {
stmt.setNull(placeHolderNum, columnType);
}
}

This utility has been used many times with no problems but never on the table that was being moved when this exception occurred. This table has 10 columns of type NUMBER 22. One of these is a recently added ID column. The value of the id column was probably the same as the record number when the exception occurred, 2,534,151.

Any help or advice would be appreciated,
-=beeky

Comments

624104
That just means that the value you are trying to insert is too big for that column's datatype scale. Try outputing the values before the exception is thrown to get an idea of how big it is first. See if the number is bigger than the datatype can handle. Output that id value as well, it could be the culprit.

TimS
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 24 2009
Added on Oct 23 2009
1 comment
46,089 views