Multiple procedures to schedule jobs

jobsoracleoracle-12cstored-procedures

I have a package named pkg_cd. In it there are 8 procedures:

pkg_cd.PRC_CD_CASEDATAENTRY;
pkg_cd.PRC_CD_DEDUP;
pkg_cd.PRC_CD_CIBIL;
pkg_cd.PRC_CD_TVR;
pkg_cd.PRC_CD_SANCTION;
pkg_cd.PRC_CD_DOWNPAY;
pkg_cd.PRC_CD_DOC_CPA;
pkg_cd.PRC_CD_DOC_QA;
pkg_cd.PRC_CD_PDN_REQ;
pkg_cd.PRC_CD_PDN_AUTHORIZE;

I want to schedule all the 8 procedures to run every minute. How can I do this?

Best Answer

You will need to schedule this using Oracle DBMS scheduler.

In order to run the 8 procedures, there are couple of ways, you can create a small procedure that calls the 8 procedures respectively.

After that, you will create a DBMS scheduler job that will execute the procedure which calls the main procedure.

The script below creates the main procedure to call the other procedures

CREATE OR REPLACE PROCEDURE RUN_8PROCEDURES AS
BEGIN
  pkg_cd.PRC_CD_CASEDATAENTRY;
  pkg_cd.PRC_CD_DEDUP;
  pkg_cd.PRC_CD_CIBIL;
  pkg_cd.PRC_CD_TVR;
  pkg_cd.PRC_CD_SANCTION;
  pkg_cd.PRC_CD_DOWNPAY;
  pkg_cd.PRC_CD_DOC_CPA;
  pkg_cd.PRC_CD_DOC_QA;
  pkg_cd.PRC_CD_PDN_REQ;
  pkg_cd.PRC_CD_PDN_AUTHORIZE;
END;
/

This script creates a job that runs every minute and runs the main procedure.

BEGIN
   DBMS_SCHEDULER.Create_job (
      job_name          => 'MY_JOB_THAT_RUNS_EVERY_MINUTE',
      repeat_interval   => 'FREQ=minutely;interval=1',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'RUN_8PROCEDURES',
      enabled           => TRUE
   );
END;
/
Related Question