create or replace package body test is



  function tt1 return t_pipe_info pipelined
  is
  begin
    for i in (select  level 
                from  dual
             connect by level < 2000000)
    loop   
      pipe row (i);
    end loop;
  end;

  function tt2 return t_pipe_info pipelined
  is
  begin
    for i in (select * from table(tt1))
    loop   
      if mod(i.a,2) = 0 then
      pipe row (i);           
      end if;     
    end loop;
  end;

  function find return sys_refcursor
  is
    lcresult sys_refcursor;
  begin
    open lcresult for
    select * from table(tt2) order by 1 desc;
    return lcresult;
  end;

  function tt3 return t_pipe_info
  is
    tt  t_pipe_info;
  begin
    select  level 
            bulk collect into tt
      from  dual
    connect by level < 2000000;
    return tt;
  end;

  function tt4 return t_pipe_info
  is
    tt   t_pipe_info;
    tt1  t_pipe_info := t_pipe_info();
  begin
    tt1 := tt3;
    select  a 
            bulk collect into tt
      from  table(tt1) t
     where  mod(a,2) = 0;
    return tt;
  end;

  function find3 return sys_refcursor
  is
    lcresult      sys_refcursor;
    c_t_pipe_info t_pipe_info := t_pipe_info();
  begin
    c_t_pipe_info := tt4;
    open lcresult for
    select * from table(c_t_pipe_info) order by a desc;
   return lcresult;
  end;

end test;