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.

Possibly related:

Tagged datatype, pl/sql, sequence | Post a Comment

Comments (3)

LewisC wrote:
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 #
Eddie Awad wrote:
Your .02 is worth a million. Thanks Lewis.
July 27th, 2006, at 12:37 pm #
Kamrul wrote:
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 #