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)

Answer 1

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)

Answer 2

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.