Delete from multiple tables

no need for 'execute immediate'.  Just do a straight delete.

drop table tab2 purge;
drop table tab1 purge;

create table tab1(col1 char(1) primary key);

create table tab2(col1 char(1), col2 char(1),
	constraint tab_fk foreign key (col2) 
		references tab1(col1)
	);

insert into tab1 values('a');

insert into tab2 values('a','a');
commit;

create or replace trigger tab1_del_trig
before delete on tab1
for each row
begin
	delete from tab2 where col2 = :old.col1;
end;
/

show errors

delete from tab1;
rollback;

Select allOpen in new window

If you can do two execute immediate deletes, you should be able to do two regular deletes.

create or replace trigger tab1_del_trig
before delete on tab1
for each row
begin

execute immediate 'delete from OMNI_ENT_' || :old.type ' where <key_col> = ''' || :old.SECTION_ID || ''';

end;

Select allOpen in new window

Throw the task back to the designers/developers.

The issue with execute immediate is you lose the dependencies (the user_dependencies view).

I take this back, it does involve changing the trigger if you ever get a new table but you could use a case statement of if-then structure and still not use execute immediate.