Home » SQL & PL/SQL » SQL & PL/SQL » ora-01426 numeric overflow
ora-01426 numeric overflow [message #203397] Wed, 15 November 2006 00:43 Go to next message
truedeath1@hotmail.com
Messages: 9
Registered: November 2006
Junior Member
I am getting this error for the following procedure

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
dbms_output.put_line (rec.STARTSERIALNR);
for v_counter in rec.STARTSERIALNR..rec.ENDSERIALNR loop
insert into dummy values(v_counter);
end loop;
end loop;
end;
end;


my dummy table has only one column
dummy NUMBER



wh_voucher_summary has the following columns
RECHARGEVALUE NUMBER
ARRIVALDATE DATE
STARTSERIALNR NUMBER
ENDSERIALNR NUMBER

the value of

STARTSERIALNR =100000000000
ENDSERIALNR =100000099999

i am using oracle 9i in XP


Re: ora-01426 numeric overflow [message #203408 is a reply to message #203397] Wed, 15 November 2006 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Upper and lower FOR LOOP bounds are out of range; reduce them to acceptable value.
You have      : 100,000,000,000
Max allowed is:   2,147,483,648

Read more about it here.
Re: ora-01426 numeric overflow [message #203416 is a reply to message #203408] Wed, 15 November 2006 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Littlefoot, my hero! You put me on the right track to solve a very non-reproducable bug!
Cheers man!

./fa/1578/0/
Re: ora-01426 numeric overflow [message #203422 is a reply to message #203397] Wed, 15 November 2006 02:06 Go to previous messageGo to next message
truedeath1@hotmail.com
Messages: 9
Registered: November 2006
Junior Member
But i need to insert those numbers between (STARTSERIALNR=100000000000) and (ENDSERIALNR =100000099999) into another table.


how can i do this if i can not use FOR LOOP.
Re: ora-01426 numeric overflow [message #203423 is a reply to message #203416] Wed, 15 November 2006 02:08 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Wow! ./fa/1683/0/ I'm glad if it was helpful, Frank!

@OP: Using the WHILE loop?

[Updated on: Wed, 15 November 2006 02:09]

Report message to a moderator

Re: ora-01426 numeric overflow [message #203425 is a reply to message #203422] Wed, 15 November 2006 02:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do something like:
FOR idx IN 1.. (rec.ENDSERIALNR - rec.STARTSERIALNR) LOOP
INSERT INTO dummy VALUES(idx + rec.STARTSERIALNR);
END LOOP;
Re: ora-01426 numeric overflow [message #203467 is a reply to message #203397] Wed, 15 November 2006 04:02 Go to previous messageGo to next message
truedeath1@hotmail.com
Messages: 9
Registered: November 2006
Junior Member
Thnx
It worked.
Re: ora-01426 numeric overflow [message #203615 is a reply to message #203397] Wed, 15 November 2006 18:50 Go to previous messageGo to next message
artmast
Messages: 11
Registered: October 2006
Junior Member
Well, according to oratip http://oratip.com/ORA-01426.html the error ORA-01426 has the following cause and action:
Cause
Evaluation of an value expression causes an overflow/underflow.
Action
Reduce the operands.
In other words your values are too big.

[Updated on: Wed, 15 November 2006 18:51]

Report message to a moderator

Re: ora-01426 numeric overflow [message #203701 is a reply to message #203615] Thu, 16 November 2006 01:40 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that point was made quite nicely, and with more useful detail, by @Littlefoot in the first reply to the OP.
Previous Topic: Issue with UPDATE
Next Topic: Error compiling in 10g
Goto Forum:
  


Current Time: Sun Dec 04 06:44:05 CST 2016

Total time taken to generate the page: 0.11867 seconds