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.
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 email@example.com.