CODEWITHSUNDEEP
oracle-databasefor-loopplsql
JaMaBing
JaMaBing

Reputation: 1041

maximum value of for loop in PL/SQL

I'd like to know, what is the maximum value in a for loop statement? Is the datatype NUMBER?

BEGIN
  -- Bounds are numeric literals:
  FOR j IN 1..5000 LOOP
    NULL;
  END LOOP;
END;

I will need up to 3x10^14

Upvotes: 1

Views: 2431

Answers (3)

Thangamani Eraniyan
Thangamani Eraniyan

Reputation: 83

I am not sure, why you may needed this much bigger loop.

may be you can try this

Thanks, Thangamani Eraniyan

BEGIN
    FOR j IN 3 LOOP
    begin
        FOR K IN 10 LOOP
            begin
            for L in 14 loop
                Null /* you can try your code here */
            End Loop;
        End Loop;
    END LOOP;
END;

Upvotes: -1

Sentinel
Sentinel

Reputation: 6449

You can give yourself some more room with regards to the upper bound on the loop iterator. For example if you have a loop like this:

declare
  lower_bound number := 2147483640;
  upper_bound number := 2147483650; -- <==Exceeds PLS_INTEGER max value
begin
  for i in lower_bound..upper_bound
  loop
    ... do something with i ...
  end loop;
end;

that encounters an ORA-01426: numeric overflow error you can rewrite your loop like this:

declare
  lower_bound number := 2147483640;
  upper_bound number := 2147483650;
  i number;
begin
  for j in 0 .. upper_bound-lower_bound
  loop
    i := j + lower_bound;

    ... do something with i ...
  end loop;
end;

In the above code, I've changed the loop iterator from i to j, changed the loop bounds to always iterate from zero to the number of iterations required, and added a new local variable i to be used by your original code. Finally adding i := j + lower_bound; as the first statement inside the loop ensures that your code sees i the way it expects.

Now as long as you aren't iterating 2,147,483,647 times you should be good.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311978

As noted in the documentation, the lower and upper bounds of a for expression are stored in a temporary pls_integer variable. According to pls_integer's documentation, it can hold values from -2147483648 to 2147483647, represented in 32 bits.

Upvotes: 5

Related Questions