CREATE OR REPLACE  PROCEDURE P_GET_DATA_TEST
  ( 
    I_NUM     IN NUMBER, 
    CUR_REF   OUT SYS_REFCURSOR, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    TYPE ARY_ARRAY IS TABLE OF SYS_REFCURSOR;
    AR_MY_ARRAY ARY_ARRAY;
    
  BEGIN
  

    OPEN CUR_REF FOR 
    SELECT  ROWNUM 
    FROM ALL_OBJECTS
    WHERE ROWNUM <= i_num ;

   I_total_rows:=0;

   FETCH CUR_REF BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
   I_total_rows := I_total_rows + 1;
   END LOOP;

/*
I want to avoid:
    OPEN CUR_REF FOR 
    SELECT  count(ROWNUM ) into I_total_rows 
    FROM ALL_OBJECTS
    WHERE ROWNUM <= i_num ;
*/

   CLOSE CUR_REF;    


  END;

create table test1 
  (
    id number primary key,
    value0 number
  );

create table test2 
  (
    id number,
    value1 number,
    value2 number,
    fk_test1 number references test1(id)
  );
  INTO test1 values (1, 500)
  INTO test1 values (2, 600)
  INTO test1 values (3, 700)
  INTO test1 values (4, 800)
  INTO test1 values (5, 900)
insert all 


  INTO test2 values (1, 400, 70, 1)
  INTO test2 values (2, 400, 70, 2)
  INTO test2 values (3, 400, 70, 3)
  INTO test2 values (4, 400, 70, 4)
  INTO test2 values (5, 400, 70, 5)
select * from dual;

CREATE OR REPLACE  PROCEDURE P_GET_DATA_TEST
  ( 
    I_NUM     IN NUMBER, 
    CUR_REF   OUT SYS_REFCURSOR, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    TYPE ARY_ARRAY IS TABLE OF SYS_REFCURSOR;
    AR_MY_ARRAY ARY_ARRAY;
    
  BEGIN
  

    OPEN CUR_REF FOR 
    SELECT  test1.id, value0, value1, value2 
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;

   I_total_rows:=0;

   FETCH CUR_REF BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
    I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
   END LOOP;

  /* instead of using another cursor and 
    OPEN CUR_ANOTHER_CURSOR FOR 
    SELECT  sum( value0+ value1* value2 ) into I_total_rows
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;    
*/
   CLOSE CUR_REF;    


  END;

Thanks,
Ferro

SQL> select * from test1;
        ID     VALUE0
---------- ----------
         1        500
         2        600
         3        700
         4        800
         5        900

5 rows selected.

SQL> select * from test2;
        ID     VALUE1     VALUE2   FK_TEST1
---------- ---------- ---------- ----------
         1        400         70          1
         2        400         70          2
         3        400         70          3
         4        400         70          4
         5        400         70          5

5 rows selected.

SQL> CREATE OR REPLACE  PROCEDURE P_GET_DATA_TESTCUR_REF (CUR_REF OUT SYS_REFCURSOR)
  2  AS
  3  BEGIN
  4    OPEN CUR_REF FOR
  5    SELECT  test1.id, value0, value1, value2,
  6            sum( value0+ value1* value2 ) over () TOTAL
  7    FROM  test1, test2
  8    WHERE test1.id = test2.FK_TEST1 ;
  9  END;
 10  /

Procedure created.

SQL> var c refcursor
SQL> exec P_GET_DATA_TESTCUR_REF (:c)

PL/SQL procedure successfully completed.

SQL> print c
        ID     VALUE0     VALUE1     VALUE2      TOTAL
---------- ---------- ---------- ---------- ----------
         1        500        400         70     143500
         2        600        400         70     143500
         3        700        400         70     143500
         4        800        400         70     143500
         5        900        400         70     143500

5 rows selected.