CODEWITHSUNDEEP
oracle-databaseloopsjobs
Pooja
Pooja

Reputation: 333

Oracle calling a job by passing dynamic values

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?

Upvotes: 1

Views: 2428

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Dbms_scheduler allows to create program definition with arguments. One problem is that boolean type is not support but it's not a big obstacle.

Example: 

create table log_do_somthing(p1 number, p2 varchar2(10));

create or replace procedure do_somthing(p1 number, p2 varchar2) is 
begin 
insert into log_do_somthing values ( p1,p2);
commit;
end;

Dummy structure 

begin
dbms_scheduler.create_program
(
program_name=>'do_somthing_prog',
program_action=>'do_somthing',
program_type=>'STORED_PROCEDURE',
number_of_arguments=>2, enabled=>FALSE
) ;

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'do_somthing_prog',
argument_position=>1,
argument_type=>'NUMBER');

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'do_somthing_prog',
argument_position=>2,
argument_type=>'VARCHAR2');

dbms_scheduler.enable('do_somthing_prog');
end;
/

Creating job using defined program and setting parameters. 

declare 
  job_name varchar2(100);
begin
for i in 1 .. 10 loop 
    job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('do_somthin_proefix');
    dbms_scheduler.create_job(job_name,program_name=>'do_somthing_prog');
    dbms_scheduler.set_job_argument_value(job_name,1,to_char(i));
    dbms_scheduler.set_job_argument_value(job_name,2,'TURE');
    dbms_scheduler.enable(job_name);
end loop;
end;
/

Naming convention in the scheduler package is straightforward and meaningful. But if you need more information description of all methods is here https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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;

Upvotes: 2

Related Questions