declare
  n number;
begin
  n := 10; --:block2.id;
  for c in (select column_value as id from table(usr_pipe_test.gen_numbers(n))) loop  << -- так валится, а с константой - работает
    -- some excel processing or another
		message('ID: '||c.id, acknowledge);
  end loop;
end;

--
create or replace package usr_pipe_test is
  
  n_qty number;

  procedure set_param(n in number);
  function gen_numbers(n in number default null) return sys.odcinumberlist pipelined;
  
end usr_pipe_test;
--
create or replace package body usr_pipe_test is

procedure set_param(n in number) is
begin
  n_qty := n;
end;

function gen_numbers(n in number default null)
return sys.odcinumberlist pipelined as
begin
  for i in 1 .. nvl(n, n_qty)
  loop
    pipe row(i);
  end loop;
  return;
end;

end usr_pipe_test;
--
-- А в триггере, например, в WHEN-BUTTON-PRESSED
--
begin

  usr_pipe_test.set_param(:block2.id);

  for c in (select column_value as id from table(usr_pipe_test.gen_numbers(null))) loop

    -- some excel processing or another
		message('ID: '||c.id, acknowledge);
		
  end loop;

end;