Monday, April 16, 2012

Oracle 11g Job Email Notification


exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','mail.kshitijdomain.com:25');

----------------exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','info@kshitijdomain.com');


BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'COMPILE',
recipients => 'kshitij@kshitijdomain.com',
sender => 'info@kshitijdomain.com',
subject => 'Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% at %event_timestamp%. %error_message%',
events => 'JOB_SUCCEEDED,JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR');
END;
/

--------------------------------------

SET serveroutput ON
declare
v_att VARCHAR2(64);
v_att2 varchar2(64);
BEGIN
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
dbms_output.put_line('server: ' || v_att);
dbms_output.put_line('sender: ' || v_att2);
END;
/

------------------------------------

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'compile',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN UTL_RECOMP.recomp_serial(''DPCDSL''); END;',
start_date => '01-APR-12 11:00.00.00 PM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB to compile invalid objects');
END;
/


exec DBMS_SCHEDULER.run_job ('compile');

------------------------------------------------------------------------------------------------------------------------

BEGIN
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('COMPILE');
END;
/

No comments:

Post a Comment

Followers