Scheduling Jobs in the Database
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 firstname.lastname@example.org.
Page 4 of 4