create a package with many procedures as needed
Yeswhere each procedure represents a insert for a specific table?
Not necessarily. To make procedures easier to read, test, debug, etc. they should be "small". Ideally the logic should all fit on a single screen.
So when inserting into tables with many columns, the insert statement could fill a screen.
You can get around this by inserting a PL/SQL record. For example:
create table t (
generated by default on null
rec.c2 := 2;
insert into t values rec
returning c1 into id;
select * from t;
This makes it more feasible to fit the logic in a few lines of code. And thus insert into more than one table in a procedure.
Or maybe you're receiving the data as a JSON document or object type. Which has values for parent and child tables in one parameter.
In which case you may be able to use a multitable insert and load everything in one statement. e.g.:
when rn = 1 then
into orders ...
when 1 = 1 then
into order_items ...
select ... from json_table (
Note you'll need to get the PK sequence value before the insert for this to work.