Search code examples
sqloracle-databaseplsqloracle-apexplsql-package

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


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


Solution

  • 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