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;

Testing:







 Hope This Helps!!



0 Responses to “Example Script to Drop Database Jobs (DBMS_SCHEDULER.DROP_JOB)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.