2 Replies Latest reply: May 19, 2014 2:35 AM by GregV RSS

    Dinamic scheduling

    9f2e7891-4777-4932-833b-48d07c0b5a06

      I have an interesting task. But I have no idea is it possible at all, because I'm technically retarded.

      There are several pl/sql procedures. They should execute by plan. But after each execution (depending on the result) the next date and time they should run changes.

      I created a table with two columns : PROCEDURE_ID, DATE. precedures from that table should be run in time order according to the DATE field.

      And after each execution this table is updated and next procedure run by plan (it could be even the same one).
      Is it possible? Which tools can help? What should I watch?

        • 1. Re: Dinamic scheduling
          spajdy

          Did your procedures run independently or affect each other ?

          If they run independently simply create a function that get procedure ID as parameter read run date from table and return it. Use this procedure to get next run date. If you use Scheduler then repeat_interval will be this function.

          create or replace function get_next_date(p_procedure_id in number) return date as
          v_retval date;
          begin
          select date into v_retval from <table> where procedure_id=p_procedure_id;
          return v_retval;
          end;
          /
          

           

          then define job with procedure ID 1

          begin
          dbms_scheduler.create_job(name=>'<job_name>',job_action=>'your_procedure',repeat_interval=>'get_next_date(1)',start_date=>..., enabled=>true);
          end;
          /
          

           

          When you procedures affect each other then define job for each procedure in disabled state and don't set start_date.

          Instead of write next run date into table simply set start_date for appropriate job and enable it.

          Start required jobs by setting start_date and enabling them.

          • 2. Re: Dinamic scheduling
            GregV

            Hi,

             

            There are several ways to do that. You can change the repeat_interval/start_date of your job according to your custom table settings. You don't necessarily need a table, you can change the job's attributes at the end of your procedure.