DELETE FROM CHILD WHERE child_grp = 'val'

FOR chld IN (
  SELECT child_grp
  FROM child
  WHERE child_grp = 'val'
) LOOP
    dbms_output.put_line('Deleting ' || chld.rowid);

    SELECT child_grp
    INTO   dummy
    FROM   child_grp
    WHERE  child_grp = chld.child_grp
    FOR UPDATE NOWAIT;

    DELETE FROM chld WHERE rowid = chld.rowid;
END LOOP;

drop table dl2 purge;
drop table dl1 purge;

create table dl1 ( a number, b number, c number
        ,primary key (a,b)
);

create table dl2 ( a number, b number, c number
        ,primary key (c, a, b)
        ,foreign key (a,b) references dl1(a,b)
);

--create index dl2_i1 on dl2(a,b);

insert into dl1 values (1 , 2, 3);
insert into dl1 values (4 , 5, 6);

insert into dl2 values (1 , 2, 3);
insert into dl2 values (4 , 5, 6);

commit;

prompt RUN THIS IN SESSION 1: delete from dl2 where a = 4;
prompt RUN THIS IN SESSION 2: delete from dl2 where a = 1;
prompt RUN THIS IN SESSION 1: delete from dl1 where a = 4;
prompt RUN THIS IN SESSION 2: delete from dl1 where a = 1;