Example Script to Drop Database Jobs (DBMS_SCHEDULER.DROP

Sunday, April 14, 2019

Example Script to Drop Database Jobs (DBMS_SCHEDULER.DROP_JOB)

In this post, we have a given an example script to drop a database job which was already created using CREATE_JOB

Wrapper Procedure:

CREATE OR REPLACE PROCEDURE drop_job

                   (pov_errbuf             OUT VARCHAR2,

                    pon_retcode            OUT NUMBER,

                    piv_drop_job_name       IN VARCHAR2

                    )

IS

    le_end_of_program    EXCEPTION;

    lv_dummy_char        VARCHAR2(200);

    lrec_job             all_scheduler_jobs%ROWTYPE;

      position_            NUMBER;

BEGIN

    -- check is job already exists

    BEGIN

      SELECT job_name

        INTO lv_dummy_char

        FROM all_scheduler_jobs

       WHERE UPPER(job_name) = piv_drop_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.drop_job(piv_drop_job_name, TRUE);



EXCEPTION

    WHEN le_end_of_program THEN

      pon_retcode :=   1;

    WHEN OTHERS THEN

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

      pon_retcode :=   2;

END drop_job;

Testing Script:

SET SERVEROUTPUT ON;

DECLARE

lv_errbuf VARCHAR2(4000);

ln_retcode NUMBER;

BEGIN

drop_job (pov_errbuf             => lv_errbuf,

            pon_retcode            => ln_retcode,

            piv_job_name           => 'TEST_SHAREORACLEAPPS'

            );

END;