[PL/SQL]how to make a loop that runs multiple procedures

Question

suppose I have a list of procedures p1,p2,p3..pn , each procedure has only one output number parameter, basically each procedure is a constant number. How could I create a loop that runs all these procedures and the results are displayed on the screen? I tried with EXECUTE IMMEDIATE something like:

DECLARE
 i number;
 n number:=5;
 print number;
begin
 for i in 1 .. n loop
  EXECUTE IMMEDIATE 'BEGIN p'||i||'('||print||') END;' ;
  dbms_output.put_line(print);
 end loop;
end;

I also tried the form [EXECUTE IMMEDIATE sql_stmt INTO print USING i]
but I still haven't done anything

score 2 · Accepted Answer · answered Nov 26 '21 at 19:26

It's BEGIN myprocedure; END; - you're missing the ";" after the procedure call. It's unclear what you want the "print" to do, that is not declared anywhere so it will just print an empty value.

CREATE OR REPLACE procedure p1
AS
BEGIN
  dbms_output.put_line('this is 1');
END;
/
CREATE OR REPLACE procedure p2
AS
BEGIN
  dbms_output.put_line('this is 2');
END;
/
DECLARE
 n number:=2;
begin
 for i in 1 .. n loop
  EXECUTE IMMEDIATE 'BEGIN p'||i||'; END;' ;
 end loop;
end;
/

this is 1

this is 2

[PL/SQL]how to make a loop that runs multiple procedures

1 Answers1