OraFAQ Forum: SQL & PL/SQL » numeric overflow error

Home » SQL & PL/SQL » SQL & PL/SQL » numeric overflow error

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

numeric overflow error [message #300318]

Fri, 15 February 2008 01:11

misha76
Messages: 4
Registered: February 2008

Junior Member

Hi all!

I’m using the SQL Developer Tool and trying to create a union view from 10 other views and
I’m getting a ORA-01426 numeric overflow error.
As the union view has many rows I suppose that the problem is there.
Can anybody tell me how many rows Oracle 10g can handle before getting in trouble?

Thanks, Misha

Report message to a moderator

Re: numeric overflow error [message #300320 is a reply to message #300318]

Fri, 15 February 2008 01:15

Frank
Messages: 7901
Registered: March 2000

Senior Member

Probably more than you try to handle Smile

Also, that would not be a message I would expect when Oracle reaches its limits.

Report message to a moderator

Re: numeric overflow error [message #300322 is a reply to message #300318]

Fri, 15 February 2008 01:15

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-01426: numeric overflow
 *Cause: Evaluation of an value expression causes an overflow/underflow.
 *Action: Reduce the operands.

Oracle can handle an infinite number of rows.
The problem is in your data/expression not in the number of rows.

Regards
Michel

Report message to a moderator

Re: numeric overflow error [message #300332 is a reply to message #300322]

Fri, 15 February 2008 01:42

misha76
Messages: 4
Registered: February 2008

Junior Member

*Action: Reduce the operands.

Does that mean that 10 union statements are too much?

Report message to a moderator

Re: numeric overflow error [message #300334 is a reply to message #300332]

Fri, 15 February 2008 01:47

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No.
I mean if you have power(10,1000) you have overflow.
It is all about data and computation on them not SQL.

Regards
Michel

Report message to a moderator

Re: numeric overflow error [message #300335 is a reply to message #300332]

Fri, 15 February 2008 01:49

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

misha76 wrote on Fri, 15 February 2008 08:42

*Action: Reduce the operands.

Does that mean that 10 union statements are too much?

No, it has nothing to do with the view, but rather with the query you're using to build that view.
Just try that query as a stand-alone query and see what happens.

Report message to a moderator

Re: numeric overflow error [message #300338 is a reply to message #300318]

Fri, 15 February 2008 02:09

misha76
Messages: 4
Registered: February 2008

Junior Member

Mmm, there's my (newbie) sql statement:

CREATE OR REPLACE FORCE VIEW "EXPLORER"."TEST" ("PAR_ID", "PR_ID", "GRENZWERT_GUT") AS 
  SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p10_b
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p9_b
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p8_b
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p7_b
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p6_c1
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p5_c1
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p4_c1
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p2_c1
UNION
SELECT par_id,
  pr_id,
  grenzwert_gut
FROM v_p1

I'm trying the union on different views (that are all working), handling three numeric values,
but without powers or other calculations.

Also, I've tried to reduce the statement with an where clausel to one "pr_id" and it's working...

Misha

[Updated on: Fri, 15 February 2008 02:10]

Report message to a moderator