How to schedule a series of stored procedures to run sequentially?

Question

I am new to coding in SQl and new to Oracle SQL developer as well. I managed to re-build an old MS Access database in Oracle . I have stored they queries in procedures but now I need to run the procedures in a specific order, a bit like you would do in a MS Access Macro. The goal is to be able to create a daily routine that should be launched by the scheduler. is there a way to accomplish this?

ie "macro full run"

1. run procedure a and when it is finished
2. run procedure b and when it is finished
3. run procedure c and so on....

thank you in advance Valentina

Answer 1

You can create a new procedure

CREATE OR REPLACE PROCEDURE MACRO_FULL_RUN AS
BEGIN
  PROCEDURE_A;
  PROCEDURE_B;
  PROCEDURE_C;
  ... and so on
END;

And then put it in the scheduler:

BEGIN
   DBMS_SCHEDULER.Create_job (
      job_name          => 'MY_JOB_THAT_RUNS_DAILY',
      repeat_interval   => 'FREQ=DAILY',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'MACRO_FULL_RUN',
      enabled           => TRUE
   );
END;