Oracle11g numeric overflow in for loop

Question

I have a for loop in pl/sql function something like:

FOR i IN min..max LOOP

variables i, min, max are declared as NUMERIC

in my case min and max are ofen very large, but range itself is small, ie:

min = 3232236033
max = 3232236286

as You see range is about ~256, but with this values oracle throws a numeric overflow error and I stuck on how to get it working.

How I should iterate over those values?

EDIT

OK, I have a working answer, using of loop of max/min diff, but is it really not possible to loop through big values in oracle?

EDIT The error I retrieve is:

SQL Error: ORA-01426: nadmiar numeryczny
ORA-06512: przy "PS.DHCP", linia 88
01426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.
*Action:   Reduce the operands.

Line 88 of code is:

FOR client_ip IN min_host..max_host

min_host, max_host, client_ip is a result of inet_aton (numeric representation of IP)

Please show your code along with the exceptions you are receiving — tbone
– tbone, Commented Feb 18, 2011 at 17:53
The function has over 70 lines of code, but I'll paste, the error report — canni
– canni, Commented Feb 18, 2011 at 17:58

Harrison · Accepted Answer · 2011-02-18 18:12:09Z

9

it seems the problem comes from i being cast as too small a number (which seems to a a fault of pl/sql), you can change your loop type:

a While loop works fine

set serveroutput on
/
declare
 min_val number;
 max_val number ;
 iterator number ;
begin
    min_val := 3232236033 ;
    max_val := 3232236286 ;

    iterator := min_val;
    while iterator<=max_val loop
        dbms_output.put_line(iterator);
        iterator  := iterator  + 1;
    end loop ;

end;
/

From here: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/controlstatements.htm#BABEFFDC

FOR LOOP Index

The index of a FOR LOOP statement is implicitly declared as a variable of type INTEGER that is local to the loop. The statements in the loop can read the value of the index, but cannot change it. Statements outside the loop cannot reference the index. After the FOR LOOP statement runs, the index is undefined. (A loop index is sometimes called a loop counter.)

In Example 4-17, the FOR LOOP statement tries to change the value of its index, causing an error.

onwards to this: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/loop_statement.htm

index_name

An undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself; you cannot reference the index outside the loop.

The implicit declaration of index_name overrides any other declaration outside the loop. To refer to another variable with the same name, use a label. See Example 4-22, "Referencing Global Variable with Same Name as Loop Counter".

Inside a loop, the index is treated like a constant: it can appear in expressions, but cannot be assigned a value.

thus even though you declare the "index" in your declare, it is NOT being used within the loop and instead is using the implicitly created INDEX (which seems to have a precision too small for your needs)

edited Feb 18, 2011 at 18:12

answered Feb 18, 2011 at 18:02

Harrison

9,1401 gold badge34 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shannon Severance Over a year ago

+1. Also believe the Oracle documentation is mistaken when it says "The index of a FOR LOOP statement is implicitly declared as a variable of type INTEGER." Integer in Oracle are a synonym for number(38) which is plenty big enough. My guess is that the index of a for loop is actually a pls_integer which is a 32 bit integer and not big enough for OP's uses. See: download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/… for information about pls_integer.

Shannon Severance Over a year ago

The 11gR1 documenation makes it clear that pls_integer is being used: Internally, PL/SQL assigns the values of the bounds to temporary PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a PLS_INTEGER is -2147483648 to 2147483647, represented in 32 bits. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment. See PLS_INTEGER and BINARY_INTEGER Data Types. download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/….

Shannon Severance Over a year ago

cont... It may be that 11gR2 is actually using integer now, but I saw no mention in the "What's New" section of the manual. Or the 11gR2 documenation was simplified until it is wrong. I don't have 11gR2 to test on.

Harrison Over a year ago

@Shannon Severance, it appears that the first link I provided (which is for 11g Release 2 (11.2)) which states INTEGER is wrong...) I am running 11gr2 and I can attest that the FOR loop that OP had problem with is still having problems in 11gR2. The second link I provided (11gR1 states PLS_INTEGER clearly). I concur with your initial assessment that the documentation is incorrect (at least on the first link)

Luke Woodward · Accepted Answer · 2011-02-18 18:05:58Z

3

You could run the loop variable from 0 to the difference between min and max. Here's an example that merely writes the numbers to DBMS_OUTPUT:

DECLARE
  v_min     INTEGER := 3232236033;
  v_max     INTEGER := 3232236286;
  v_diff    PLS_INTEGER;
BEGIN
  v_diff := v_max - v_min;
  FOR i IN 0..v_diff
  LOOP
    -- Use v_min + i where you would have used i.
    dbms_output.put_line(v_min + i);
  END LOOP;
END;
/

EDIT: sadly you can't use the range operator to iterate through large numbers. The range operator .. is restricted to the range +/- 2³¹. From the PL/SQL documentation:

Internally, PL/SQL assigns the values of the bounds to temporary PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a PLS_INTEGER is -2**31 .. 2**31. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment.

edited Feb 18, 2011 at 18:05

answered Feb 18, 2011 at 17:35

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

1 Comment

canni Over a year ago

Ok, I see that this will work, but I do not understand, why I have to use such a pointless loop complication, is there really no way, to loop through big values in oracle?

Collectives™ on Stack Overflow

Oracle11g numeric overflow in for loop

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related