The scheduler can be started and stopped using the start_scheduler and stop_scheduler procedures
SQL> exec tascha_pkg.start_scheduler; PL/SQL procedure successfully completed. SQL> select what, job, to_char(next_date,'HH24:MI') from user_jobs; WHAT JOB TO_CH ------------------------------ ---------- ----- taschx_pkg.check_schedule; 141 20:57 SQL> exec tascha_pkg.stop_scheduler; PL/SQL procedure successfully completed.
If an attempt is made to start the scheduler when it is already running then the "sched_already_started" exception is thrown. Likewise an attempt to stop an already stopped scheduler will raise the "sched_already_stopped" exception.
A task is basically a procedure you would like to run at some point in the future. No parameter values are specified when creating a task as these may change with different schedules. The "create_task" procedure is used to create tasks.
... tascha_pkg.create_task( I_procedure_name => 'task_test_proc' , I_title => 'Test Task Procedure.' , I_success_email => 'jobs@mydomain.net' , I_failure_email => 'errors@mydomain.net' , O_task_id => L_task_id); ...
A task can be scheduled many times over. Each schedule entry has it's own date/time specification and it's own set of runtime parameter values. A task is scheduled using the "schedule_task" procedure.
... tascha_pkg.schedule_task(I_task_id => L_task_id , I_hour => '8,17' , I_min => '0,30' , I_once_only => TRUE , O_sched_id => L_sched_id); ...
Blah
This simple example creates a test procedure and schedules it to run every 5 mins for the current hour. It assumes the scheduler is already running.
SQL> CREATE OR REPLACE PROCEDURE task_test_proc(I_in IN NUMBER, O_out OUT VARCHAR2)
AS
L_force_error NUMBER;
BEGIN
-- uncomment the line below to force an exception
--L_force_error := 'string';
taschx_pkg.logger_info('Extra logging in task_test_proc');
taschx_pkg.logger_info('The time is '||TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'));
O_out := 'this is some output';
END;
/
Procedure created.
SQL> set serveroutput on
SQL> DECLARE
L_task_id NUMBER;
L_sched_id NUMBER;
L_hour VARCHAR2 (30) := TO_CHAR(SYSDATE,'HH24'); -- current hour
L_min VARCHAR2 (30) := '0,10,20,30,40,50'; -- every 10 mins
L_dom NUMBER := TO_NUMBER(TO_CHAR(SYSDATE,'DD')); -- day of month
L_month NUMBER := TO_NUMBER(TO_CHAR(SYSDATE,'MM')); -- month number
BEGIN
-- Create a task for the test procedure created above.
tascha_pkg.create_task( I_procedure_name => 'task_test_proc'
, I_title => 'Task Test Procedure.'
, I_success_email => 'jobs@mydomain.net'
, I_failure_email => 'errors@mydomain.net'
, O_task_id => L_task_id);
dbms_output.put_line('task:'||L_task_id);
-- create a schedule for the task
tascha_pkg.schedule_task(I_task_id => L_task_id
, I_hour => L_hour
, I_min => L_min
, I_dom => L_dom
, I_month => L_month
, O_sched_id => L_sched_id);
dbms_output.put_line('sched:'||L_sched_id);
-- decide to pass a value of "12" into the procedure when run
tascha_pkg.add_task_prm(L_sched_id, 'I_in', '12');
COMMIT;
END;
/
task:12
sched:19
PL/SQL procedure successfully completed.
The task has now been scheduled so we wait until a 10 minute window has passed and then query the logs table.
set linesize 132 COLUMN log_txt FORMAT a132 SELECT l.log_txt FROM task_logs l WHERE l.sched_id = 19 ORDER BY l.log_seq ASC / LOG_TXT ------------------------------------------------------------------------------------------ Submitting task (sched ID : 19) Submitted job (dbms_job ID: 4143) Job starting at 21:30 Task ID => 12 Task title => "Task Test Procedure." Procedure => "task_test_proc" Parameters... I_in => "12" Extra logging in task_test_proc The time is 31.01.2005 21:30:05 Output parameter O_OUT => "this is some output" Sending email Email from => 'jobs@mydomain.net' Email to => 'jobs@mydomain.net' Email subject => Task Scheduler: Task "Task Test Procedure." completed successfully
As we have specified a success email recipient a message is sent (to jobs@mydomain.net) containing this log information.
blah