Scheduling Jobs in the Database, Page 4

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.