ORA-01426 After Upgrade to 10g

Oracle seems to have a tendency to make small changes to database code which impacts application in a big way. Sad part is, these changes are not always documented properly. One of these cases is what I encountered. A simple PL/SQL code errors out as below:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> declare
2 v_code NUMBER(20);
3 BEGIN
4 v_code := 990 * 10000000;
5 END;
6 /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4

Same block works fine in any previous versions like 9i and 8i. As per Oracle this is “Expected Behavior”. Oracle used to perform number arithmetic. In 10g integer arithmetic is being used. Any application using such code would require to undergo change as follows:

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_v1 number :=990;
3 v_v2 number :=10000000;
4 v_code NUMBER(20);
5 BEGIN
6 v_code := v_v1 * v_v2 ;
7* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_v1 number :=990;
3 v_code NUMBER(20);
4 BEGIN
5 v_code := v_v1 * 10000000 ;
6* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> declare
2 v_code NUMBER(20);
3 BEGIN
4 v_code := 990 * 10000000.0;
5 END;
6 /

PL/SQL procedure successfully completed.

Using any of the above methods, error is not observed. It is although not clear why oracle made this change. Another hurdle in moving to 10g!!. Hope Oracle provides Database capture feature on 9i and Replay on 10g.

Tags: 10gdatabaseoraclePL/SQL