toooki
asked on
Oralce 11g Scheduled Job Email notification
I have a created a scheduled job to run on Oracle 11g database everyday night at 8PM. I could I create an email notification so that it will automatically send me an email when the Job finishes (completes its run)?
I attached my scheduled job, which calls an Oracle stored procedure.
I attached my scheduled job, which calls an Oracle stored procedure.
name => 'TestJOB', force => TRUE);
DBMS_SCHEDULER.CREATE_JOB(job_name => 'MyJobName',
job_type => 'STORED_PROCEDURE',
job_action => 'procedurename',
start_date => to_timestamp_tz('01/10/2011 20:00:00 US/Central',
'mm/dd/yyyy hh24:mi:ss tzr'),
repeat_interval => 'FREQ=hourly; INTERVAL=6',
enabled => true,
auto_drop => false,
comments => 'Refreshes SoF tables');
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also note, the notification procedure is only available in 11gR2 and above.
10g and 11gR1 don't have it, so you would have to implement the email some other way
either as an exception handler within a job, or as a separate job that monitors the other job and emails based on the results of previous executions
10g and 11gR1 don't have it, so you would have to implement the email some other way
either as an exception handler within a job, or as a separate job that monitors the other job and emails based on the results of previous executions
ASKER
Thank you. I am a bit stuck as I could not figure out if my database in 11gR2 (or 11gR1). I do not have DBA privilege.
SQL>SELECT version FROM v$instance;
Says I do not have access to the table.
Is there any other way to find the DB version? However, I know it is of course 11g.
SQL>SELECT version FROM v$instance;
Says I do not have access to the table.
Is there any other way to find the DB version? However, I know it is of course 11g.
select * from v$version
ASKER
Thank you. Shows 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.
So will follow your previous directions.
So will follow your previous directions.
ASKER
I get the attached error when I try to create this notification job
ORA-00900: Invalid SQL statement
ORA-00900: Invalid SQL statement
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => MY_LOAD_DATA_JOB,
recipients => 'myemail@yahoo.com',
--sender IN VARCHAR2 DEFAULT NULL,
--subject IN VARCHAR2 DEFAULT DBMS_SCHEDULER.DEFAULT_NOTIFICATION_SUBJECT,
--body IN VARCHAR2 DEFAULT DBMS_SCHEDULER.DEFAULT_NOTIFICATION_BODY,
events => VARCHAR2 DEFAULT 'JOB_SUCCEEDED,JOB_FAILED,JOB_STARTED',
--filter_condition IN VARCHAR2 DEFAULT NULL
);
ASKER
Sorry this is the query I used and getting the same error:
DBMS_SCHEDULER.ADD_JOB_EMA IL_NOTIFIC ATION (
job_name => 'MY_LOAD_DATA_JOB',
recipients => 'myemail@yahoo.com',
events => 'job_started, job_succeeded');
DBMS_SCHEDULER.ADD_JOB_EMA
job_name => 'MY_LOAD_DATA_JOB',
recipients => 'myemail@yahoo.com',
events => 'job_started, job_succeeded');
you must run the pl/sql procedure from within a pl/sql block
begin
DBMS_SCHEDULER.ADD_JOB_EMA IL_NOTIFIC ATION .....
end;
/
begin
DBMS_SCHEDULER.ADD_JOB_EMA
end;
/
ASKER
Thank you. I forgot that.
This time I get an error when I compile the above email notification code:
ORA-24098: invalid value <NULL> for EMAIL_SERVER.
How do I specify this value?
I know similar other database in which this EMAIL_SERVER value might be specified as that runs email notification. Is there any query that could let me find out the specified EMAIL_SERVER value in the other database...?
This time I get an error when I compile the above email notification code:
ORA-24098: invalid value <NULL> for EMAIL_SERVER.
How do I specify this value?
I know similar other database in which this EMAIL_SERVER value might be specified as that runs email notification. Is there any query that could let me find out the specified EMAIL_SERVER value in the other database...?
ASKER
I got this syntax and tried:
BEGIN
DBMS_SCHEDULER.SET_SCHEDUL ER_ATTRIBU TE (
attribute => 'EMAIL_SERVER',
value => 'remotesmtp.myco.com');
END;
Than it worked.
Thank you all for all your help.
BEGIN
DBMS_SCHEDULER.SET_SCHEDUL
attribute => 'EMAIL_SERVER',
value => 'remotesmtp.myco.com');
END;
Than it worked.
Thank you all for all your help.
events => 'job_succeeded');
you can also get emailed for errors, then
events => 'job_failed');
you can include specific errors to check for with the filter_condition parameter
examples and syntax are in the link above