I wrote a pipelined function to show when fetching is stopped.
create or replace package testas as
type tt is table of number;
function producer(n number) return tt pipelined;
procedure debug;
end testas;
/
create or replace package body testas as
dmesg varchar2(500 char);
di number := 0;
function producer(n number) return tt pipelined as
begin
dmesg := 'start';
for i in 1..n loop
di := i;
pipe row(i);
end loop;
exception
when no_data_needed then
dmesg := sqlerrm;
raise;
end producer;
procedure debug as
begin
dbms_output.put_line('di='||di||' '||dmesg);
end debug;
end testas;
/
When ARRAYSIZE=33 and MAXROWS=2, function continues fetching up to 50 rows.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set maxrows 2
SQL> set arraysize 66
SQL> select * from table(testas.producer(1001));
COLUMN_VALUE
_______________
1
2
SQL> exec testas.debug
di=66 ORA-06548: no more rows needed
PL/SQL procedure successfully completed.
SQL> set arraysize 33
SQL> select * from table(testas.producer(1001));
COLUMN_VALUE
_______________
1
2
SQL> exec testas.debug
di=50 ORA-06548: no more rows needed
PL/SQL procedure successfully completed.
SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.1.1.0 build: 21.1.1.113.1704
SQL>