Examples
Below is a list of Tascha examples/tutorials.
Starting and stopping the scheduler
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.
Creating a task
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' , I_sql_trace => false , I_category => 'TST' , O_task_id => L_task_id); ...
Only the I_procedure_name and O_task_id parameters are mandatory - all others can be omitted.
Scheduling a task
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_dow => 'MON,WED' , I_hour => '8,17' , I_min => '0,30' , O_sched_id => L_sched_id); ...
Viewing scheduled tasks
Scheduled tasks can be viewed using the show_sched.sql script supplied in the utils directory.
SQL> @show_sched132
Enter category (blank for all)
TASK SCHD ONCE DOW DOW
CAT ID TITLE PROCEDURE NAME ID HOUR MIN DOW DOM MON ENBL ONLY TRC STAT 1ST LST
--- ---- ----------------------- ----------------------- ---- ------------ ------------ -------- --- --- ---- ---- --- ---- --- ---
DBA 2 Analyze Schema TASCHA gather_schema_stats 2 01 05 SUN Y N N N
2 Analyze Schema PERFSTAT gather_schema_stats 3 01 00 SUN Y N N Y
3 Statspack Snapshot perfstat.statspack.snap 4 9,11,13,15,1 00,15 MON-FRI Y N N N
7
5 Schema Chain Count DBAU dbau.dba_code.find_chai 7 20 00 SUN Y N N N
n_cnt
TASK SCHD ONCE DOW DOW
CAT ID TITLE PROCEDURE NAME ID HOUR MIN DOW DOM MON ENBL ONLY TRC STAT 1ST LST
--- ---- ----------------------- ----------------------- ---- ------------ ------------ -------- --- --- ---- ---- --- ---- --- ---
DEL 1 Task Log Maintenance tascha_pkg.task_log_mai 1 00 00 SUN Y N N N N N
nt
6 Stores Box Deletion dbau.delete_pkg.d_store 8 01 00 SUN Y N N N N N
s_box
7 rows selected.
A simple example
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';
tascha_pkg.logger_info('Extra logging in task_test_proc');
tascha_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.
Re-scheduling a task
A task can be re-scheduled at any time. Each schedule entry has it's own id and by using this you can change any attributes of that schedule - such as DOW parameters. A task is re-scheduled using the "reschedule_task" procedure.
... SQL> exec tascha_pkg.reschedule_task(I_sched_id=>8,I_min=>'50',I_dow=>'THU'); PL/SQL procedure successfully completed. SQL> commit; Commit complete. ...
Dropping a task or schedule
Tasks and schedules can be dropped completely using the following procedures.
... SQL> exec tascha_pkg.drop_schedule(I_task_id=>2,I_sched_id=>6); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> exec tascha_pkg.drop_task(I_task_id=>6); SQL> commit; Commit complete. ...
Alternatively you can just disable task schedules using the reschedule_task() procedure described previously.