dcsimg
August 17, 2018
Hot Topics:

Scheduling Jobs in the Database

  • November 30, 2007
  • 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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

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