Home » SQL & PL/SQL » SQL & PL/SQL » numeric overflow error
numeric overflow error [message #300318] Fri, 15 February 2008 01:11 Go to next message
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
Re: numeric overflow error [message #300320 is a reply to message #300318] Fri, 15 February 2008 01:15 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: numeric overflow error [message #300322 is a reply to message #300318] Fri, 15 February 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: numeric overflow error [message #300332 is a reply to message #300322] Fri, 15 February 2008 01:42 Go to previous messageGo to next message
misha76
Messages: 4
Registered: February 2008
Junior Member
*Action: Reduce the operands.

Does that mean that 10 union statements are too much?
Re: numeric overflow error [message #300334 is a reply to message #300332] Fri, 15 February 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: numeric overflow error [message #300335 is a reply to message #300332] Fri, 15 February 2008 01:49 Go to previous messageGo to next message
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.
Re: numeric overflow error [message #300338 is a reply to message #300318] Fri, 15 February 2008 02:09 Go to previous messageGo to next message
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

Re: numeric overflow error [message #300342 is a reply to message #300338] Fri, 15 February 2008 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change union to union all unless you have duplicates you want to don't see.

Regards
Michel
Re: numeric overflow error [message #300345 is a reply to message #300318] Fri, 15 February 2008 02:20 Go to previous message
misha76
Messages: 4
Registered: February 2008
Junior Member
Yuppie, it seems to work with UNION ALL Embarassed

Thanks, Misha Smile
Previous Topic: How to identify last column added
Next Topic: Pivot query (merged)
Goto Forum:
  


Current Time: Sat Dec 03 01:19:02 CST 2016

Total time taken to generate the page: 0.11237 seconds