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.
Possibly related:
- CURRVAL and NEXTVAL
- Comparison between MS SQL 2005 and Oracle 10g
- 6 Useful Links You Should Check Out Today (2007-10-26)
- Oracle Database 11g New PL/SQL Features in your 10g Code Today
- The Case of Better Readable Code
Tagged datatype, pl/sql, sequence | Post a Comment


















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
July 27th, 2006, at 12:28 pm #Your .02 is worth a million. Thanks Lewis.
July 27th, 2006, at 12:37 pm #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
November 14th, 2006, at 10:18 pm #