12.1 and 12.2 databases
From version 12 we get a pretty rich scheduler notification feature. Easy to config. Can schedule to send on error or failure. Works fine. We have other scheduling tools and we want to move jobs into oracle scheduler. We can configure plsql to email output from these jobs but am trying to configure so the scheduler includes the output in its notification. Maybe its easy and something Im missing or maybe its not doable. Seeing if anyone knows
-- create dummy procedure for some output
create or replace
procedure do_stuff is
begin
dbms_output.put_line('Commenced: ' || sysdate);
dbms_output.put_line('Working');
dbms_lock.sleep(5);
-- simulate an error output that we may want to pick up in text but have job continue
dbms_output.put_line('ORA-12345');
dbms_output.put_line('Done: ' || sysdate);
end;
/
add a job that calls that, run every minute to simulate a schedule
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_notification_job',
job_type => 'PLSQL_BLOCK',
job_action => 'do_stuff;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; bysecond=0',
end_date => SYSTIMESTAMP + 1/24,
enabled => TRUE);
END;
/
add a notification, this way more then we want but just shows capability
-- add notification
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'test_notification_job',
recipients => 'me@myemail.com',
sender => 'sender@send.com', -- this over rides the scheduler attributes
subject => 'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%',
body => 'Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code%
Error message: %error_message%' || chr(13)||chr(10)|| -- add some sys_context info to our output
'Host : ' || SYS_CONTEXT('USERENV','SERVER_HOST') || chr(13)||chr(10) ||
'Instance : ' || SYS_CONTEXT('USERENV','INSTANCE_NAME') ,
events => 'JOB_ALL_EVENTS',
filter_condition => NULL);
-- set attributes
DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'test_notification_job'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_FULL);
END;
/
I get lots of pretty stuff in the email but I dont get output. We know where output is though.
select output
from dba_Scheduler_job_run_Details
where job_name = 'TEST_NOTIFICATION_JOB'
order by log_id desc
fetch first row only
Commenced: 02-OCT-19
Working
ORA-12345
Done: 02-OCT-19
Is there anyway to include that output in the body of the scheduler notification ? Otherwise Im going to have to have another bit of additional plsql that will run to email the output with utl_mail.