Forum Stats

  • 3,734,235 Users
  • 2,246,916 Discussions
  • 7,857,194 Comments

Discussions

SET ARRAYSIZE cannot be set below 50

User_H3J7U
User_H3J7U Member Posts: 169 Silver Badge

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>
Sign In or Register to comment.