create table test_parent 
  (
    id number(2) primary key,
    val varchar(10)
  );

create table test_child1
  (
    id number(2) primary key,
    fk_pid number(2) references test_parent,
    val varchar(10)
  );

create table test_child2
  (
    id number(2) primary key,
    fk_c1id number(2) references test_child1,
    val varchar(10)
  );

insert all
  into test_parent values(1,'p1')
  into test_parent values(2,'p2')
  into test_child1 values(1,1,'c1')
  into test_child1 values(2,1,'c1')
  into test_child1 values(3,2,'c1')
  into test_child1 values(4,2,'c1')
  into test_child2 values(1,1,'c2')
  into test_child2 values(2,1,'c2')
  into test_child2 values(3,2,'c2')
  into test_child2 values(4,3,'c2')
select * from dual;

select * from TEST_CHILD2 where FK_C1ID in (select id from TEST_CHILD1 where FK_PID = 1);



CREATE OR REPLACE PROCEDURE p_get_children(
                            I_parent_ID IN NUMBER, 
                            CUR_child1 OUT SYS_REFCURSOR, 
                            CUR_child2 OUT SYS_REFCURSOR)
is 
  type v_array is varray (6) of number;
  array_id v_array := v_array();
BEGIN
  
open CUR_child1 for select id /*bulk collect into array_id*/
  from TEST_CHILD1 where FK_PID = I_parent_ID;


open CUR_child2 for select id, FK_C1ID, val from TEST_CHILD2  
where FK_C1ID in (select id from TEST_CHILD1 where FK_PID = I_parent_ID); /*<-- this works but I have to repeat the first statement*/
--open CUR_child2 for select id, FK_C1ID, val from TEST_CHILD2  
--where FK_C1ID in (array_id);  <-- here I don't know the correct syntax


END;

SCOTT@orcl> CREATE OR REPLACE TYPE v_array AS TABLE OF NUMBER;
  2  /

Type created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE PROCEDURE p_get_children
  2    (i_parent_id IN	NUMBER,
  3  	cur_child1  OUT SYS_REFCURSOR,
  4  	cur_child2  OUT SYS_REFCURSOR)
  5  is
  6    array_id v_array := v_array();
  7  BEGIN
  8    SELECT id BULK COLLECT INTO array_id
  9    FROM   test_child1
 10    WHERE  fk_pid = i_parent_id;
 11  
 12    OPEN cur_child1 FOR
 13  	 SELECT * FROM TABLE (array_id);
 14  
 15    OPEN cur_child2 FOR
 16  	 SELECT id, fk_c1id, val
 17  	 FROM	test_child2, TABLE (array_id)
 18  	 WHERE	fk_c1id = COLUMN_VALUE;
 19  END;
 20  /

Procedure created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> VARIABLE child1_cur REFCURSOR
SCOTT@orcl> VARIABLE child2_cur REFCURSOR
SCOTT@orcl> EXEC p_get_children (1, :child1_cur, :child2_cur)

PL/SQL procedure successfully completed.

SCOTT@orcl> PRINT child1_cur

COLUMN_VALUE
------------
           1
           2

2 rows selected.

SCOTT@orcl> PRINT child2_cur

        ID    FK_C1ID VAL
---------- ---------- ----------
         1          1 c2
         2          1 c2
         3          2 c2

3 rows selected.