create table sParent
  (
    pid number(2) primary key,
    dp date,
    v1 varchar2(10)
  );


create table sChild
  (
    sid number(2) primary key,
    ds date,
    v2 varchar2(10),
    fk_parent_id number(2) references sParent
  );



insert ALL
  into sParent(pid, dp, v1) values (1, to_date('01-01-2022','dd-mm-yyyy'), 5)
  into sParent(pid, dp, v1) values (2, to_date('01-02-2022','dd-mm-yyyy'), 15)
  into sParent(pid, dp, v1) values (3, to_date('01-03-2022','dd-mm-yyyy'), 51)
  into sParent(pid, dp, v1) values (4, to_date('01-01-2023','dd-mm-yyyy'), 25)
  into sParent(pid, dp, v1) values (5, to_date('01-02-2023','dd-mm-yyyy'), 53)
  into sParent(pid, dp, v1) values (6, to_date('01-03-2023','dd-mm-yyyy'), 45)
  into sChild(sid, ds, v2, FK_PARENT_ID) values (10, to_date('01-08-2023','dd-mm-yyyy'), 7, 1)
  into sChild(sid, ds, v2, FK_PARENT_ID) values (40, to_date('01-08-2023','dd-mm-yyyy'), 7, 4)
  into sChild(sid, ds, v2, FK_PARENT_ID) values (60, to_date('01-07-2023','dd-mm-yyyy'), 7, 6)
select * from dual;


CREATE OR REPLACE PROCEDURE P_DELETE_record
  (
    v2 in varchar2
  )
  AS
      D_ACQ_DUE DATE;
  BEGIN


      DELETE FROM schild WHERE v2 = v2;  -- I can start with child record but then I will not be able to delete any records from parent

      DELETE FROM sParent 
        WHERE pid IN 
              (
                SELECT FK_PARENT_ID FROM schild WHERE v2 = v2 -- this condition is only valid if child table exists.
              );

  END;

orclz>
orclz> set serverout on
orclz> declare
  2  type t is table of number;
  3  tab t;
  4  begin
  5  delete from schild where v2=7 returning fk_parent_id bulk collect into tab;
  6  for i in tab.first .. tab.last loop
  7  dbms_output.put_line(tab(i));
  8  end loop;
  9  end;
 10  /
1
4
6

PL/SQL procedure successfully completed.

orclz>

DELETE FROM schild WHERE v2 = v2; 

DELETE FROM schild WHERE v2 = P_DELETE_record.v2;