-- 1st get the data I want and hold it in a table type.

DECLARE
   TYPE t_All_Tab_cols IS TABLE OF schema.table@host%ROWTYPE;
   		v_TabCols t_All_Tab_Cols;
   CURSOR c1 IS
   SELECT * FROM schema.table@host
 BEGIN
   OPEN c1;
   LOOP
   FETCH c1 BULK COLLECT INTO v_TabCols limit 1000;

   EXIT WHEN c1%ROWCOUNT = 100000;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);	

   CLOSE c1;
END;


-- 2nd define a table type to return from my pipelined function.

CREATE OR REPLACE TYPE t_MyObject 
AS OBJECT
( 
  col1             NUMBER (4), 
  col2             NUMBER (13), 
  col3             NUMBER (13), 
  )
/

CREATE OR REPLACE TYPE t_MyTable
IS TABLE OF t_MyObject
/


-- 3rd create my table function w/1 IN param.

CREATE OR REPLACE PACKAGE MyPipePack AUTHID CURRENT_USER IS
 FUNCTION MyPipe RETURN t_MyTable PIPELINED;
END MyPipePack;
/


CREATE OR REPLACE PACKAGE BODY MyPipePack (
p_TabCols IN t_AllTabCols)AS
 FUNCTION MyPipe RETURN t_MyTable PIPELINED IS
 BEGIN
  FOR i in 1 .. 50
  LOOP
  PIPE ROW (t_MyTable(i)); 
  END LOOP;
 RETURN;
 END MyPipe;
END MyPipePack;
/