Re: Limits of PL/SQL FOR...LOOP variable?

Yes, that explains it, for 19c:

1. The FOR LOOP variable is always implicitly cast as PLS_INTEGER.
2. The variable declaration has a scope of the FOR LOOP.
3. PLS_INTEGER limits are -2G..2G-1.

Thanks all for the pointers and explanation!

Rich

On Tue, Sep 27, 2022 at 12:38 PM Chris Saxon <chris.saxon_at_gmail.com> wrote:

> The data type for index variables is implicitly PLS_INTEGER
>
>
> https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-control-statements.html#GUID-F8D95A75-6475-439E-B3F1-BB3D683E1751
>
> From 21c you can override this in the loop definition:
>
> DECLARE
> sseq NUMBER:=2147483646;
> eseq NUMBER:=2147483648;
> BEGIN
> FOR x NUMBER IN sseq..eseq LOOP
> dbms_output.put_line(TO_CHAR(x));
> END LOOP;
> END;
> /
>
> 2147483646
> 2147483647
> 2147483648
>
> On Tue, 27 Sept 2022 at 18:10, Rich J <rich242j_at_gmail.com> wrote:
>
>> Howdy,
>>
>> In 19.16, a dev has a quick anonymous block that fails with "ORA-1426:
>> numeric overflow". It appears that the FOR...LOOP has a limit of 2G-1. I
>> can't see where that would be documented or there's some implicit typing
>> going on that I'm missing. From experimentation:
>>
>> DECLARE
>> sseq NUMBER:=2147483647;
>> eseq NUMBER:=2147483647;
>> BEGIN
>> FOR x IN sseq..eseq LOOP
>> dbms_output.put_line(TO_CHAR(x));
>> END LOOP;
>> /
>>
>> This works. Bumping the end sequence up by 1 and it fails at the FOR
>> statement with ORA-1426 and nothing is output, so at compile time. I've
>> tried adding an explicit declaration of "x", changing them all to
>> NUMBER(12) or PLS_INTEGER, but still get the same error. I can't find any
>> reference to this limit at:
>>
>>
>> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/database-limits.htm
>>
>> Am I missing something (very possible) or is this an undocumented limit?
>>
>> Thanks,
>> Rich
>>
>
>
> --
> Thanks,
> Chris
> blogs.oracle.com/sql
> Twitter: _at_chrisrsaxon
>

--
http://www.freelists.org/webpage/oracle-l