Example Script to Disable Database Jobs (DBMS_SCHEDULER.DISABLE

Sunday, April 14, 2019

Example Script to Disable Database Jobs (DBMS_SCHEDULER.DISABLE_JOB)

In this post, we have a given an example script to Disable a database job which is currently scheduled.

Wrapper Procedure:

CREATE OR REPLACE PROCEDURE disable_job

                        (pov_errbuf             OUT VARCHAR2,

                         pon_retcode            OUT NUMBER,

                         piv_job_name           IN VARCHAR2

                        )

IS

    le_end_of_program    EXCEPTION;

    lv_dummy_char        VARCHAR2(200);

    lrec_job             all_scheduler_jobs%ROWTYPE;

BEGIN

    -- check is job already exists

    BEGIN

      SELECT job_name

        INTO lv_dummy_char

        FROM all_scheduler_jobs

       WHERE UPPER(job_name) = piv_job_name;



    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        pov_errbuf := 'Job Does not exists.Error:'||SQLERRM;

        RAISE le_end_of_program;

      WHEN OTHERS THEN

        pov_errbuf := 'Error while validating Job.Error:'||SQLERRM;

        RAISE le_end_of_program;

    END;



    -- drop the jobs

    dbms_scheduler.disable(piv_job_name, TRUE);

EXCEPTION

    WHEN le_end_of_program THEN

      pon_retcode :=   1;

    WHEN OTHERS THEN

      pov_errbuf := 'Unhandled Error while disabling job.Error:'||SQLERRM;

      pon_retcode :=   2;

END disable_job;

Testing Script:

SET SERVEROUTPUT ON;

DECLARE

lv_errbuf VARCHAR2(4000);

ln_retcode NUMBER;

BEGIN

disable_job (pov_errbuf             => lv_errbuf,

               pon_retcode            => ln_retcode,

                     piv_job_action         => 'DROP',

               piv_job_name           => 'TEST_SHAREORACLEAPPS'

               );

END;