dcsimg
April 5, 2020
Hot Topics:

Scheduling Jobs in the Database

  • By Jeyarani Venkatasamy
  • Send Email »
  • More Articles »

Once the procedure is created successfully, you need to schedule the job by submitting the job into the Oracle Job Queue. The following is the PL/SQL code to schedule the job.

DECLARE
   v_jobno number;
   v_repeat_interval number;
BEGIN

   BEGIN
      SELECT repeat_interval INTO v_repeat_interval
      FROM PM_DELTA_CONFIG
      WHERE process_name='pr_AggregateMetrics';
   EXCEPTION

      WHEN NO_DATA_FOUND THEN
         v_repeat_interval := 60;
   END;
   dbms_job.submit(
      job =>v_jobno,
      what =>'begin pkg_PerformanceMetrics.pr_AggregateMetrics; end;',
      next_date =>sysdate+15*(1/24/60/60),    -- start at 15 secs
                                              -- from sysdate
      interval=>'sysdate+'||to_char(v_repeat_interval)||'*
         (1/24/60/60)');    -- wakes up every 60 seconds
                            -- (based on v_repeat_interval)
      COMMIT;

END;

Once the job is submitted into the job queue, the job starts 15 seconds from the sysdate for the first time and repeats every 60 seconds thereafter.

Conclusion

I hope this article helps you understand more about the Oracle job queue sub system and its usage in scheduling and executing PL/SQL routines at predefined times and/or repeated job execution at regular intervals.

About the Author

Jeyarani Venkatasamy holds a master's degree in Computer Applications from Manonmaniam Sundaranar University, India. She has worked as an Associate System Analyst in the Communications, Media and Entertainment division, Hewlett-Packard, Bangalore for more than three years involving Web services, Web Service Testing tools and J2EE technologies. The domain of her experience includes telecom, web services, XML, Java, and J2EE-related technologies. She can be reached at jeyarani.venkatasamy@hp.com.





Page 4 of 4



This article was originally published on November 30, 2007

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.


Thanks for your registration, follow us on our social networks to keep up-to-date