OraFAQ Forum: SQL & PL/SQL » ora-01426 numeric overflow

Home » SQL & PL/SQL » SQL & PL/SQL » ora-01426 numeric overflow

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora-01426 numeric overflow [message #203397]

Wed, 15 November 2006 00:43

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

Report message to a moderator

Re: ora-01426 numeric overflow [message #203408 is a reply to message #203397]

Wed, 15 November 2006 01:14

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: ora-01426 numeric overflow [message #203416 is a reply to message #203408]

Wed, 15 November 2006 01:46

Frank
Messages: 7901
Registered: March 2000

Senior Member

Littlefoot, my hero! You put me on the right track to solve a very non-reproducable bug!
Cheers man!

/forum/fa/1578/0/

Report message to a moderator

Re: ora-01426 numeric overflow [message #203422 is a reply to message #203397]

Wed, 15 November 2006 02:06

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.

Report message to a moderator

Re: ora-01426 numeric overflow [message #203423 is a reply to message #203416]

Wed, 15 November 2006 02:08

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Wow!

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

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;

Report message to a moderator

Re: ora-01426 numeric overflow [message #203467 is a reply to message #203397]

Wed, 15 November 2006 04:02

truedeath1@hotmail.com
Messages: 9
Registered: November 2006

Junior Member

Thnx
It worked.

Report message to a moderator

Re: ora-01426 numeric overflow [message #203615 is a reply to message #203397]

Wed, 15 November 2006 18:50

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

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.

Report message to a moderator

Previous Topic:	Issue with UPDATE
Next Topic:	Error compiling in 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 05:11:42 CST 2024