When The Combination of an Oracle Sequence and a PLS_INTEGER is Deadly
The following is a true story. We had our Oracle MRP system down for a couple of days because of a simple, yet hidden bug that went unnoticed for years until it hit us couple of days ago (somehow the image of a terrorist cell comes to mind).
In Oracle Applications (11.03) there is a table that stores forecast records (mrp_forecast_dates). In this table there is the column transaction_id that gets populated from a sequence (mrp_forecast_dates_s).
When the current value of the sequence exceeded the number 2,147,483,647 the MRP concurrent programs started to fail with an “ORA-01426: numeric overflow” error. After a lot of research, and an Oracle Service Request, we discovered what the problem was and what was special about the number 2,147,483,647.
Let me reproduce the problem in the following example:
EDDIE@XE> CREATE SEQUENCE my_seq
2 START WITH 2147483647
3 /
Sequence created.
EDDIE@XE> DECLARE
2 l_var PLS_INTEGER;
3 BEGIN
4 FOR i IN 1 .. 2
5 LOOP
6 SELECT my_seq.NEXTVAL
7 INTO l_var
8 FROM DUAL;
9
10 DBMS_OUTPUT.put_line ('l_var = ' || TO_CHAR (l_var));
11 END LOOP;
12 EXCEPTION
13 WHEN OTHERS
14 THEN
15 DBMS_OUTPUT.put_line ('What: ' || DBMS_UTILITY.format_error_stack);
16 DBMS_OUTPUT.put_line ('Where: ' ||DBMS_UTILITY.format_error_backtrace);
);
17 END;
18 /
l_var = 2147483647
What: ORA-01426: numeric overflow
Where: ORA-06512: at line 6
PL/SQL procedure successfully completed.
So, what happened? In one of Oracle Applications’ PL/SQL programs, an Oracle developer decided to store the value of the sequence’s nextval in a local variable. This is fine, except that the local variable was a PLS_INTEGER. This is fine too, except that the PLS_INTEGER datatype stores signed integers in the range -2,147,483,647 through 2,147,483,647.
So, when the next value in the sequence was 2,147,483,648 BOOM! BANG! BLOW! Numeric Overflow!
The NUMBER datatype should have been used instead of PLS_INTEGER. But, since we could not change the Oracle supplied programs, we reset the “START WITH” of the sequence to 1 (also making sure there were no duplicate transaction_id’s in the table). MRP Programs are running smoothly now.
Lesson learned: in your PL/SQL programs, make sure you select the RIGHT datatype for your variables.
Related articles:
- CASE gotcha in Oracle 8i
- How to find where an error was raised in PL/SQL
- Here’s a Quick Way to Get the Line Number in PL/SQL
Hi Eddie,
I think the best solution would have been an to use an anchored declaration and declare mrp_forecast_dates.transaction_id%TYPE. Then the programmer would have been assured of a proper data type match.
Just my .02. heh
LewisC
Your .02 is worth a million. Thanks Lewis.
i am getting this ora-01426 for my following code
CREATE OR REPLACE procedure WHDATA is begin declare v_counter number; begin for rec in (select RECHARGEVALUE,ARRIVALDATE,STARTSERIALNR, ENDSERIALNR from wh_voucher_summary) loop for v_counter in rec.STARTSERIALNR..rec.ENDSERIALNR loop insert into dummy values(v_counter); end loop; end loop; end; end;
–the value of startserialnr =100000000000 –the value of endserialnr =100000099999
pleasehelp me out of this error