Oracle Scheduler Jobs with multiple frequency

Question

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.

Answer 1

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

Answer 2

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