CREATE TABLE TMP_EMP
(	
      EMP_ID          NUMBER(10,0), 
      EMP_NAME        VARCHAR2(100), 
      DEPT_ID         NUMBER(10,0), 
      SALARY          NUMBER(14,0), 
      COMMISSION_PCT  NUMBER(3,0),
      JOIN_DATE       DATE, 
      CONSTRAINT PK_TMP_EMP PRIMARY KEY( EMP_ID )
) ;

INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (1,'Manoj',10,10324,10,to_date('05-JAN-11','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (2,'Ravi',10,54412,4,to_date('07-FEB-11','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (3,'Harish',10,74548,6,to_date('15-AUG-80','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (4,'Naresh',10,65472,5,to_date('15-MAY-17','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (5,'Rakesh',10,78954,9,to_date('07-MAY-15','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (6,'Hari Ram',10,32145,7,to_date('07-JUL-08','DD-MON-RR'));
INSERT INTO TMP_EMP (EMP_ID,EMP_NAME,DEPT_ID,SALARY,COMMISSION_PCT,JOIN_DATE) 
VALUES (7,'Hari Ram',10,4578124,1,to_date('07-OCT-08','DD-MON-RR'));


CREATE OR REPLACE TYPE OBJ_TEST AS OBJECT ( EMP_ID NUMBER(10), EMP_NAME VARCHAR2(100) ) ;

CREATE OR REPLACE TYPE TBL_OBJ_TEST IS TABLE OF OBJ_TEST ;

CREATE OR REPLACE FUNCTION FUN_TEST 
    RETURN TBL_OBJ_TEST PIPELINED
AS
    LVOB_TEST  TBL_OBJ_TEST := TBL_OBJ_TEST() ;
BEGIN

    --Can we pipe all these rows at once which are fetched using BULK COLLECT?
    --I don't want to use loop

    /*
    SELECT OBJ_TEST(EMP_ID, EMP_NAME)
    BULK COLLECT INTO LVOB_TEST
    FROM TMP_EMP ;
    */    
    
    PIPE ROW( OBJ_TEST( 10, 'MANOJ' ) ) ;  --Hard coded for example. Will be dynamic in real world.

    RETURN ;

END ;

CREATE OR REPLACE
FUNCTION FUN_TEST 
    RETURN TBL_OBJ_TEST PIPELINED
AS
    LVOB_TEST  TBL_OBJ_TEST := TBL_OBJ_TEST() ;

BEGIN

    ------------------------------------------------------------------------
    --Bulk Fetch and Pipe one by one thereafter. 
    --Client who issued SQL must wait till table function produces complete collection.

    SELECT OBJ_TEST(EMP_ID, EMP_NAME)
    BULK COLLECT INTO LVOB_TEST
    FROM TMP_EMP ;
    
    FOR I IN 1..LVOB_TEST.COUNT 
    LOOP    
        PIPE ROW( OBJ_TEST( LVOB_TEST(I).EMP_ID, LVOB_TEST(I).EMP_NAME ) ) ;
    END LOOP ;

    RETURN ;
    ------------------------------------------------------------------------

END ;

CREATE OR REPLACE
FUNCTION FUN_TEST 
    RETURN TBL_OBJ_TEST PIPELINED
AS
    LVOB_TEST  TBL_OBJ_TEST := TBL_OBJ_TEST() ;
    
    CURSOR CUR_EMP
    IS
        SELECT EMP_ID, EMP_NAME
        FROM TMP_EMP ;
BEGIN

    --**********************************************************************

    --Fetching one row and Pipe it at same time. 
    --Client who issued SQL can process it right away.

    FOR REC_EMP IN CUR_EMP
    LOOP
        PIPE ROW( OBJ_TEST( LVOB_TEST( CUR_EMP%ROWCOUNT ).EMP_ID, LVOB_TEST( CUR_EMP%ROWCOUNT ).EMP_NAME ) ) ;
    END LOOP ;
    
    RETURN ;

    --**********************************************************************
END ;

CREATE OR REPLACE FUNCTION FUN_TEST RETURN SYS_REFCURSOR
IS
  l_res SYS_REFCURSOR;
BEGIN
  open l_res for SELECT EMPNO, ENAME FROM EMP ;
  RETURN l_res;
END;
/

SQL> CREATE OR REPLACE FUNCTION FUN_TEST RETURN SYS_REFCURSOR
  2  IS
  3    l_res SYS_REFCURSOR;
  4  BEGIN
  5    open l_res for SELECT EMPNO, ENAME FROM EMP ;
  6    RETURN l_res;
  7  END;
  8  /

Function created.

SQL> select fun_test from dual;
FUN_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

INSERT
  INTO tickertable
  SELECT  ticker,
          case lvl when 1 then 'O' else 'C' end,
          case lvl when 1 then open_price else close_price end,
          trade_date
    FROM  stocktable,
          (select level lvl from dual connect by level <= 2)
/