create a package with many procedures as neededYes
where 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 (
c1 int
generated by default on null
as identity,
c2 int
);
declare
rec t%rowtype;
id t.c1%type;
begin
rec.c2 := 2;
insert into t values rec
returning c1 into id;
end;
/
select * from t;
C1 C2
1 2
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.:
insert all
when rn = 1 then
into orders ...
when 1 = 1 then
into order_items ...
select ... from json_table (
jdoc, ...
)
Note you'll need to get the PK sequence value before the insert for this to work.