CREATE TABLE TEST_CASE
  (
    ID NUMBER,
    NAME1 VARCHAR(10),
    VALUE1 NUMBER
  );

INSERT ALL
  INTO TEST_CASE VALUES (1, 'TTT', 77)
  INTO TEST_CASE VALUES (2, 'RRR', 737)
  INTO TEST_CASE VALUES (3, 'TT', 477)
  INTO TEST_CASE VALUES (4, 'HHH', 775)
  INTO TEST_CASE VALUES (5, 'TFFFTT', 66)
  INTO TEST_CASE VALUES (6, 'III', 18)
SELECT * FROM DUAL;


CREATE OR REPLACE TYPE OBJ_TEST_CASE AS OBJECT
(
    ID NUMBER,
    NAME1 VARCHAR(10),
    VALUE1 NUMBER
);


CREATE TYPE TBL_TEST_CASE AS
    TABLE OF OBJ_TEST_CASE;

-- A function that get the result for a single id as a table
CREATE OR REPLACE FUNCTION F_TEST_CASE
    (
      I_ID IN NUMBER
    )
  RETURN TBL_TEST_CASE AS
    AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;
  BEGIN
    SELECT OBJ_TEST_CASE(
                          ID ,
                          NAME1,
                          VALUE1 
                         )
      BULK COLLECT INTO AGR_ACCOUNT_STATEMENT
      FROM TEST_CASE WHERE ID = I_ID;

    RETURN (AGR_ACCOUNT_STATEMENT);
   END ;



-- an array to hold a list of rows
CREATE OR REPLACE TYPE ARY_AGREEMENT AS VARRAY(200) OF NUMBER;

-- Using the single value function to return a table for many rows
CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
    RETURN TBL_TEST_CASE AS
  AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;

  VARY_AGREEMENT ARY_AGREEMENT:=  ARY_AGREEMENT();
BEGIN
      SELECT ID BULK COLLECT INTO VARY_AGREEMENT  FROM  TEST_CASE 
       WHERE VALUE1 > MYMAXVALUE
         ;
  
    FOR I IN 1..VARY_AGREEMENT.COUNT LOOP
      
        SELECT OBJ_TEST_CASE(
                              ID ,
                              NAME1,
                              VALUE1 
                            )
        BULK COLLECT INTO AGR_ACCOUNT_STATEMENT 
        FROM TABLE(F_TEST_CASE(VARY_AGREEMENT(I)));
    END LOOP;

    RETURN AGR_ACCOUNT_STATEMENT;
     
 END;


SELECT * FROM TABLE(F_TEST_CASE_ALL(18));

--still a single row (the last row) is returned not many! what am I doing wrong? 

SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
  2      RETURN sys_refcursor AS
  3    c sys_refcursor;
  4  BEGIN
  5    open c for select id, name1, value1 from TEST_CASE where VALUE1 > MYMAXVALUE;
  6    return c;
  7  END;
  8  /

Function created.

SQL> var c refcursor
SQL> exec :c := F_TEST_CASE_ALL(18);

PL/SQL procedure successfully completed.

SQL> print c
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.

SQL> select F_TEST_CASE_ALL(18) from dual;
F_TEST_CASE_ALL(18)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.

1 row selected.

SQL> CREATE OR REPLACE TYPE OBJ_TEST_CASE AS OBJECT
  2  (
  3      ID NUMBER,
  4      NAME1 VARCHAR(10),
  5      VALUE1 NUMBER
  6  );
  7  /

Type created.

SQL> CREATE TYPE TBL_TEST_CASE AS
  2      TABLE OF OBJ_TEST_CASE;
  3  /

Type created.

SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
  2      RETURN TBL_TEST_CASE pipelined AS
  3  BEGIN
  4    for rec in (
  5      select id, name1, value1 from TEST_CASE where VALUE1 > MYMAXVALUE
  6    ) loop
  7      pipe row (OBJ_TEST_CASE(rec.id, rec.name1, rec.value1));
  8    end loop;
  9  END;
 10  /

Function created.

SQL> select * from TABLE(F_TEST_CASE_ALL(18));
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.

CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
    RETURN TBL_TEST_CASE pipelined AS
BEGIN
  for rec in (
    select id from TEST_CASE where VALUE1 > MYMAXVALUE
  ) loop
    pipe row  table(F_TEST_CASE(id));  -- <------ the call to the single-row function
  end loop;
END;

SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE
  2      (
  3        I_ID IN NUMBER
  4      )
  5    RETURN OBJ_TEST_CASE AS
  6      AGR_ACCOUNT_STATEMENT OBJ_TEST_CASE;
  7    BEGIN
  8      SELECT OBJ_TEST_CASE(
  9                            ID ,
 10                            NAME1,
 11                            VALUE1
 12                           )
 13        INTO AGR_ACCOUNT_STATEMENT
 14        FROM TEST_CASE WHERE ID = I_ID;
 15
 16      RETURN (AGR_ACCOUNT_STATEMENT);
 17     END ;
 18  /

Function created.

SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
  2      RETURN TBL_TEST_CASE pipelined AS
  3  BEGIN
  4    for rec in (
  5      select id from TEST_CASE where VALUE1 > MYMAXVALUE
  6    ) loop
  7      pipe row  (F_TEST_CASE(rec.id));
  8    end loop;
  9  END;
 10  /

Function created.

SQL> select * from TABLE(F_TEST_CASE_ALL(18));
        ID NAME1          VALUE1
---------- ---------- ----------
         1 TTT                77
         2 RRR               737
         3 TT                477
         4 HHH               775
         5 TFFFTT             66

5 rows selected.

CREATE OR REPLACE FUNCTION F_TEST_CASE
    (
      I_ID IN NUMBER
    )
  RETURN TBL_TEST_CASE AS    <---- I have to use Table not object type
    AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;
  BEGIN
    SELECT OBJ_TEST_CASE(
                          ID ,
                          NAME1,
                          VALUE1 
                         )
      BULK COLLECT INTO AGR_ACCOUNT_STATEMENT
      FROM TEST_CASE  WHERE id = I_ID or id = i_id+1;  --just a condition to make it return more than one row

    RETURN (AGR_ACCOUNT_STATEMENT);
   END ;