Overflow error on SQL/Oracle/DB2 Databases

Like OS’s and development frameworks, each database type out there has its own set of error codes when something goes wrong. For calculations/dates and times an Arithmetic Overflow error can be common.

Error depending on Database:

DB2 – Numeric Value out of Range

Oracle/SQL Server — Arithmetic overflow

No matter which database you are using the reason is the same. You are trying to put 10 pounds of stuff in a 5 pound bag.

Typically where I have seen these errors occur is decimals. A data type (4, 2) is commonly mistaken as four digits to the left and two digits to the right. Which to some sounds perfectly logical reasoning (myself included). What this actually means is that you can have 4 digits in total, with two of those four being to the right.

Examples that work: Examples that will give the error:
1234

12.34

123.4

00.22

1.234

1234.5

Dates and times can cause the same error; mainly caused by the way different platforms handle the values.

DB2: Oracle: SQL Server (Pre 2008) SQL Server (2008 +)
Date – Date

Time – Time

Date – Date

Time –Time Stamp

Date – DateTime

Time – DateTime

Date – Date

Time – Time

v As you can see from the chart above SQL Server 2008 introduced a new date/time data type, however to maintain compatibility with older versions/data models you can still use the DateTime data type.

How this error can occur when supporting multiple platforms is if you have the following:

SQL Server DateTime object {Dec 21, 2009 AD 23:59:59.9999999} and you try and place it in an Oracle date field. Oracle will only accept a date object like this {Dec 21, 2009 AD}.

These examples are not the only times Arithmetic overflow error will occur, they are just the most common that I have seen.

Advertisements
This entry was posted in Database and tagged , , . Bookmark the permalink.

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 )

Google+ photo

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

Connecting to %s