Oracle calling a job by passing dynamic values

Question

These is a procedure which needs to be executed by passing 2 parameters (number, Boolean) through a job. and according to the requirement it needs to be executed 10 times for set for 100 pages each starting from 800 to 1900.

Currently I have done this:

BEGIN
  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_8',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(8,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_9',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(9,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_10',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(10,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_11',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(11,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_12',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(12,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_13',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(13,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);
  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_14',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(14,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_15',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(15,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_16',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(16,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);

  sys.dbms_scheduler.create_job(
            job_name => 'WEB.TEMP_COMPILE_JOB_17',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                            WEB.PAGE_REFRESH.compile_page(17,TRUE);
                          END;',
            start_date => from_tz(sys_extract_utc(localtimestamp), 'UTC'),
            job_class => 'DEFAULT_JOB_CLASS',
            comments => 'refresh',
            auto_drop => TRUE,
            enabled => TRUE);
END;

But now I am trying to pass this numeric value dynamic. How can I proceed?

Answer 1

You may use a FOR LOOP by setting first the start job number and end job numbers.

DECLARE
   v_start_job_number   INTEGER := 8;
   v_end_job_number     INTEGER := 17;
BEGIN
   FOR v_job_number IN v_start_job_number .. v_end_job_number
   LOOP
      sys.DBMS_SCHEDULER.create_job (
         job_name     => 'WEB.TEMP_COMPILE_JOB_' || v_job_number,
         job_type     => 'PLSQL_BLOCK',
         job_action   =>    'BEGIN
                                                WEB.PAGE_REFRESH.compile_page('
                         || v_job_number
                         || ',TRUE);
                                              END;',
         start_date   => FROM_TZ (SYS_EXTRACT_UTC (LOCALTIMESTAMP), 'UTC'),
         job_class    => 'DEFAULT_JOB_CLASS',
         comments     => 'refresh',
         auto_drop    => TRUE,
         enabled      => TRUE);
   END LOOP;

END;