Sunday, July 27, 2008

10g Migration Ramification Part 1: ORA-1426, Numeric Overflow

Oracle 11g was released over a year ago, but lets face it, not everyone is running Oracle 10g, let alone 11g. July 31st marks the end of the 'free' Premier Support Oracle offered up to all Oracle 9.2.0.8 customers last summer. With that spirit in mind, I thought I would share some of my Oracle 10g Migration Ramifications. ('Upgrade' is a boring word and doesn't have a lot of words that rhyme with it. It is also a great homage to Adam Sandler's 'Cajun Man').

In part 1, I will discuss an ORA-1426 you may encounter in PL/SQL code if you are using large literal integers and expecting a NUMBER type to hold it.

The code:

DECLARE
lv_results NUMBER := 0;
BEGIN
lv_results := 999999999 * 3;
END;
/

Oracle 9i Results:

PL/SQL procedure successfully completed.

Oracle 10g Results:

DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4


I believe the 10g result is related to a change made in Oracle 10.1. There are various PL/SQL optimizations and new features added in Oracle 10g Release 1 and Release 2. Check out Metalink DocID: #311971.1 "New Features for Oracle10g PL/SQL 10.x" for a good list. I believe what we are seeing in the code above is an optimization made to the PL/SQL engine. In the example above, the PL/SQL engine is attemping to use an integer math engine to perform its calculations. This makes sense the math involves two integers. It would be faster to use integer math over floating-point.

If we change the code slightly, we can force floating-point math and avoid the numeric overflow.

New Oracle 10g Code:

DECLARE
lv_results NUMBER := 0;
BEGIN
lv_results := 999999999 * 3.0;
END;
/


New Oracle 10g Results:

PL/SQL procedure successfully completed.


You could also avoid the issue altogether by using variables of type NUMBER.

The moral of the story with any database migration (or application change) is testing, testing, testing. Even the smoothest, most tested migration is going to come up with a few small issues (or one really big one). Even when you are armed with a list of all the new features, possible bugs etc, there are bound to be surprises for developers and dbas alike.

Good luck with getting your 9i databases migrated (or 8i , or even, 8 and 7)
blog comments powered by Disqus