CODEWITHSUNDEEP
oracle-databaseplsqlscheduled-tasks
elusive
elusive

Reputation: 35

Cleanly end plsql procedure started by Oracle Scheduler

I have a scheduled job that calls a pl/sql package procedure.
Presently the procedure will exit on its own if it completes or if it exceeds a time range based on values set in a configuration table (job execution time range 12AM to 3AM).

I would like to use Oracle Scheduler features to cleanly end the procedure if it exceeds the time range it is permitted to execute. Is there anyway to do this?

Oracle Version 12.2

Upvotes: 0

Views: 234

Answers (2)

TenG
TenG

Reputation: 4004

The DBMS_SCHEDULER.CREATE_JOB procedure allows you to set a number of properties for the job including max_run_duration.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHBCCJI

Here is a old question that covers this:

https://dba.stackexchange.com/questions/21540/why-does-dbms-scheduler-max-run-duration-not-raise-event-job-over-max-dur

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

Maybe by running another job to terminate it? Schedule this job to run at 3 AM everyday. It's JOB_ACTION would look like

DECLARE
     v_state user_scheduler_jobs.state%TYPE;
    BEGIN
    SELECT state
         INTO v_state
     FROM user_scheduler_jobs
           WHERE job_name = 'MY_JOB_NAME';

    IF v_state = 'RUNNING' THEN
       DBMS_SCHEDULER.STOP_JOB(job_name=>'MY_JOB_NAME',force =>true);
    END IF;

 EXCEPTION 
   WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('THE JOB MY_JOB_NAME IS NOT FOUND');

END;
/

Upvotes: 1

Related Questions