Tuesday, November 12, 2024

End loop statement can raise ORA-06502 too

I was puzzled when I got an error message allegedly occurring at a line containing an "end loop" statement and it took me a while to figure out that this occurs when either bound of the loop is NULL.

In my case both the initial and final bounds are variables and they were supposed to be not null or so I thought...

Here is a code snippet reproducing the error:

begin
  for i in null..100
  loop
    null;
  end loop;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

So, if you see this error reported at this unusual location, you know what you are up against.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles