How do I execute many Procedures inside one?

Question

I have looked it up already but couldn't find the right solution for my question. I have many Procedures which copy tables from one database Schema called 'source' into another Schema on the same database called 'target'. The Procedures themself work if I execute them individually. Now I want to execute them with only one command. The problem is I don't know how. I want to do that because there are many procedures and it is annoying to execute them all individually. It would be nice if someone could help me out. Thanks in advance :)

My approach (which is obviously wrong) was:

CREATE OR REPLACE PROCEDURE COPY_TABLES
IS
BEGIN
EXEC COPY_EAKTEPERSON;
EXEC COPY_EANDEREANSRPUECHE;
EXEC COPY_EANDEREBHBERECHT;
EXEC COPY_EBEIHILFEBEMSATZ;
EXEC COPY_EBESCHAEFTIGUNG;
EXEC COPY_EDIENSTSTELLE;
EXEC COPY_EEIGENBEHALT;
EXEC COPY_EPFLEGEVERS;
EXEC COPY_ESEHSCHAERFE;
EXEC COPY_EVERSLEISTUNG;
EXEC COPY_EWOHNISITZ;
EXEC COPY_EPERSON;
END;

Remove the EXEC command. EXEC is a SQL*PLUS command, which basically puts a statement you are trying to execute inside a BEGIN ... END block — Nick Krasnov, Jul 27 '18 at 07:42
omg it worked... LOL. Well I just started to learn pl sql. Thanks for the answer xD — AnSuS, Jul 27 '18 at 07:46
You cal many procedures the same way you call one procedure. — William Robertson, Jul 27 '18 at 11:13

score 3 · Answer 1 · answered Jul 27 '18 at 07:56

3

A procedure calling another procedure.

create or replace procedure COPY_TABLES(                                         
ret out varchar2) as
   error      varchar2(1000);
begin
   COPY_EAKTEPERSON();
   COPY_EANDEREANSRPUECHE();
   COPY_EANDEREBHBERECHT();
   COPY_EBEIHILFEBEMSATZ();
   COPY_EBESCHAEFTIGUNG();
   COPY_EDIENSTSTELLE();
   COPY_EEIGENBEHALT();
   COPY_EPFLEGEVERS();
   COPY_ESEHSCHAERFE();
   COPY_EVERSLEISTUNG();
   COPY_EWOHNISITZ();
   COPY_EPERSON();
   ret := ''
   return;
exception
   when others then
   error_info := sqlerrm;
   ret := error_info;
end COPY_TABLES;

answered Jul 27 '18 at 07:56

M.Laida

1,818
1
13
19

1

I have already got it to work becauase @Nick Krasnov answered it in the comment below my post. Thanks for the contribution though. – AnSuS Jul 27 '18 at 08:02
@AnSus Wow great job! – M.Laida Jul 27 '18 at 09:02
why the return? – Aleksej Jul 27 '18 at 09:38
1

also, ret := sqlErrM would be easier – Aleksej Jul 27 '18 at 09:46
@Aleksej: justo check if the procedures doesn't return any unhandled error & Yes indeed it would be much easier. Thanks =) – M.Laida Jul 27 '18 at 09:52
1

The question was about how to call a procedure, so I think the `return` logic and the exception hider are distractions from the answer. – William Robertson Jul 27 '18 at 11:12

How do I execute many Procedures inside one?

1 Answers1