2 Replies Latest reply: Jul 3, 2014 10:03 AM by spajdy RSS

    schedulerjob with changing arguments


      Hi all,

      I am about to convert jobs (up to now handled with dbms_job) to scheduler jobs (cf. example below).


      My job is:


        dbms_scheduler.create_schedule( schedule_name   => 'sched_index_sync',

             repeat_interval => 'FREQ=Minutely; INTERVAL=2',

             comments => 'schedule to run every two minutes');


          ( program_name   => 'prg_sync_index',

            program_type   => 'PLSQL_BLOCK',

            program_action => 'BEGIN PKG_Admin_IM.PRCD_sync_index(''KATALOG_KATWERT_IM'');END;',

             enabled        => TRUE,

             comments       => 'Sync index KATALOG_KATWERT.'



        dbms_scheduler.create_job (


          program_name =>'prg_sync_index',

          schedule_name=> 'sched_index_sync',

          enabled => true,

          comments => 'sync katalog index.'




      Now my question is:

      task is syncing textindexes in a 2 minute interval. As I do have several indexes I want a solution that allows me to create one job which simply receives the apropriate attributes and then starts running.

      Attribut only changes in argument (index_name) handed over to package. My only idea how to handle this is a cahin of jobs but therefore it is necessary to create several jobs each handling a specific index (which I don't want). So is there a way to just create ONE job using ONE schedule and handling SEVERAL indexes?

      Any help appreciated.


        • 1. Re: schedulerjob with changing arguments



          If you want to handle several indexes in the same job, then use a loop in your PL/SQL block to call your proc several times.

          Another option is to create your program as a stored_procedure with 1 argument. This argument would be a collection of index names. Before running the job pass the collection as the argument value.

          • 2. Re: schedulerjob with changing arguments

            Or simply create a table where you store index names. Then you job is simple PL/SQL block that read data from table and for each row run a procedure and put read row data as argument to procedure.

            create table index_to _process(index_name varchar2(30 char) not null);
            for r in (select * from index_to_process)
            end loop;