Oracle Apps Knowledge Sharing

CREATE OR REPLACE PROCEDURE stop_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;

    position_            NUMBER;

  BEGIN

   

    position_ := 10;

    -- 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;

   

    position_ := 20;

    -- drop the jobs

    dbms_scheduler.stop_job(job_name => piv_job_name,

                            force    => TRUE

                           );   

 

  EXCEPTION

    WHEN le_end_of_program THEN

      pon_retcode :=   1;

    WHEN OTHERS THEN

      pov_errbuf := 'Unhandled Error while stopping job.Error:'||SQLERRM||'. At position :'||position_;

      pon_retcode :=   2;

  END stop_job;

SET SERVEROUTPUT ON;

DECLARE

  lv_errbuf  VARCHAR2(4000);

  ln_retcode NUMBER;

BEGIN

  stop_job (pov_errbuf             => lv_errbuf,

              pon_retcode            => ln_retcode,

              piv_job_name           => 'TEST_SHAREORACLEAPPS'

              );

END;

Testing