CODEWITHSUNDEEP
sqloracle-databasedbms-job
mohammed mazin
mohammed mazin

Reputation: 445

Oracle Scheduler Jobs with multiple frequency

I am creating Scheduler Jobs for doing Backup via stored procedure and I am in kind of a situation that, the user will be selecting multiple frequencies like

In first case user selects Monthly as frequency of Job.

In second case user selects Weekly as frequency of Job(May be of different day combinations).

In third case user selects Daily as frequency of Job and so on.

So as per now there are 3(may be more as per the user schedules it) frequency for the same job. What's the best way to do this? I've to schedule same job with multiple frequencies or any other better way.

Upvotes: 0

Views: 865

Answers (2)

Popeye
Popeye

Reputation: 35930

I think the best and clean way to achieve it is to create 3 different JOBS for different frequencies and enable/disable them according to input from the user

How to enable/disable job in PL/SQL:

BEGIN
dbms_scheduler.disable('<JOB_NAME>');
END;
/

BEGIN
dbms_scheduler.enable('<JOB_NAME>');
END;
/

Cheers!!

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 2006

You can create a procedure and based on user's input you can dynamically change the parameters in the following:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'job_name',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'PROCEDURE_NAME',
   start_date         =>  'START_DATE',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=1', /* every other day */
   end_date           =>  'END_DATE',
   auto_drop          =>   FALSE,
   comments           =>  'My new job');
END;
/

Upvotes: 0

Related Questions