Home » SQL & PL/SQL » SQL & PL/SQL » Sending mail when DBMS_SCHEDULER job fails (Oracle 11g)
Sending mail when DBMS_SCHEDULER job fails [message #576179] Fri, 01 February 2013 04:02 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I have scheduled a job as below.
DECLARE
   n1 NUMBER :=7369;
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'APPS.SCHE_JOB_TEST'
      ,start_date      => SYSDATE
      ,repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=0'
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'APPS.DEL_EMP_PROC'
       ,number_of_arguments   =>1
      ,comments        => 'Run at 12 A.M.every Saturday'
    );
   DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name            => 'SCHE_JOB_TEST',
                                          argument_position   => 1,
                                          argument_value      => n1);
  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'APPS.SCHE_JOB_TEST');
END;
/


The code for the procedure is

CREATE OR REPLACE PROCEDURE del_emp_proc
(p_empno NUMBER)
IS
v_sal NUMBER;
BEGIN
DELETE FROM EMPLOYEE WHERE EMPNO=P_EMPNO;
COMMIT;
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/

I want to send an email to the following distridution list whenever the job fails.

krishna@tcs.com
pavan@tcs.com
kiran@tcs.com

I want to maintain the log table for the job with the following fields.

JOBNAME		RECORDS_DELETED   JOB_START_TIME	JOB_END_TIME	    JOB_STATUS	ERROR_MSG
SCHE_JOB_TEST    1                02/02/2013 00:00:00  02/02/2013 00:05:00   completed  null


Please help me.

Thanks in advance.
Re: Sending mail when DBMS_SCHEDULER job fails [message #576181 is a reply to message #576179] Fri, 01 February 2013 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);


Remove this it is a BUG which hides the error; read WHEN OTHERS.
By the way, who do you think is able to see what you put with dbms_output in a job?

Regards
Michel

[Updated on: Sat, 02 February 2013 02:13]

Report message to a moderator

Re: Sending mail when DBMS_SCHEDULER job fails [message #576191 is a reply to message #576181] Fri, 01 February 2013 04:30 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

In your code while capturing the when others call the sending email proc.
Re: Sending mail when DBMS_SCHEDULER job fails [message #576199 is a reply to message #576191] Fri, 01 February 2013 05:26 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your help.

Please help me on how to maintain log table for this job.

Thanks.
Re: Sending mail when DBMS_SCHEDULER job fails [message #576200 is a reply to message #576199] Fri, 01 February 2013 06:01 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

i am not clear for log table. Explain in detail.
Re: Sending mail when DBMS_SCHEDULER job fails [message #576201 is a reply to message #576200] Fri, 01 February 2013 06:03 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Per you comments,get the information from data dictionary table load the data into table.
Re: Sending mail when DBMS_SCHEDULER job fails [message #576212 is a reply to message #576191] Fri, 01 February 2013 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
muralikri wrote on Fri, 01 February 2013 11:30
In your code while capturing the when others call the sending email proc.


Wrong! Remove WHEN OTHERS and just have a look at alert.log to see the error.

Regards
Michel

[Updated on: Fri, 01 February 2013 07:43]

Report message to a moderator

Re: Sending mail when DBMS_SCHEDULER job fails [message #576213 is a reply to message #576199] Fri, 01 February 2013 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ajaykumarkona wrote on Fri, 01 February 2013 12:26
Thanks for your help.

Please help me on how to maintain log table for this job.

Thanks.


Do what I said, execute the job and tell us the error.

Regards
Michel

Re: Sending mail when DBMS_SCHEDULER job fails [message #576219 is a reply to message #576191] Fri, 01 February 2013 07:55 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
muralikri wrote on Fri, 01 February 2013 05:30
In your code while capturing the when others call the sending email proc.


In addition to this being a bug as Michel said, exceptions do not trigger if a delete finds no rows.

Re: Sending mail when DBMS_SCHEDULER job fails [message #576263 is a reply to message #576219] Sat, 02 February 2013 02:07 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
As per your suggestion I have called send_mail_proc procedure in the exception block.

CREATE OR REPLACE PROCEDURE del_emp_proc
(p_deptno NUMBER)
IS
v_sal NUMBER;
BEGIN
DELETE FROM EMPLOYEE WHERE EMPNO=P_deptno;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
send_mail_proc(par1_val,par2_value....);
END;
/

If there is any exception mail will be sending saying that "job is failed".
But in the USER_SCHEDULER_JOB_LOG table status is showing as "SUCCESSED".

Then it's a discrepancy.
Is there any way to forcefully fail the job when there is any exception in my "del_emp_proc" procedure.
Re: Sending mail when DBMS_SCHEDULER job fails [message #576264 is a reply to message #576263] Sat, 02 February 2013 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But in the USER_SCHEDULER_JOB_LOG table status is showing as "SUCCESSED".

Then it's a discrepancy.


No, and if you have read WHEN OTHERS as I adviced you you'd know why (or just carefully read my posts).

Regards
Michel

[Updated on: Sat, 02 February 2013 02:13]

Report message to a moderator

Re: Sending mail when DBMS_SCHEDULER job fails [message #576265 is a reply to message #576264] Sat, 02 February 2013 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any way to forcefully fail the job when there is any exception in my "del_emp_proc" procedure.


Yes, just add RAISE at the end of WHEN OTHERS part.

Regards
Michel
Re: Sending mail when DBMS_SCHEDULER job fails [message #576958 is a reply to message #576265] Mon, 11 February 2013 06:28 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi All,

I got the similar problem so I did like this .Please correct me if anything is wrong.
I want to capture the log while refreshing the materialized view or sending an email.I got that procedure from using utl_smtp
http://www.orafaq.com/wiki/Send_mail_from_PL/SQL .So it is throwing -20000 exception so I am checking if it is -20000 then email error otherwise mv error.
Below error_log is procedure which insert the info to log table.And the procedure p_error is scheduled to run everyday at 11 pm night.
PROCEDURE p_error
IS
  v_from        VARCHAR2(80) := 'scott@aol.com';
  v_subject     VARCHAR2(80) := 'refresh status';
  l_error_msg   VARCHAR2(200):='refresh failed.';
  l_success_msg VARCHAR2(200):='Refreshed successfully.';
  l_rcpt_list   VARCHAR2(20);
BEGIN
  l_rcpt_list:='miller@aol.com';
  BEGIN
    dbms_mview.REFRESH('mv_refresh');
    send_email(l_rcpt_list, v_from,v_subject,l_success_msg);
  EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE=-20000 THEN
      ERROR_LOG( v_package_name, 'error_track', 'Error while sending email.'||SQLCODE||sqlerrm, SYSTIMESTAMP );
    ELSE
      ERROR_LOG( v_package_name, 'error_track', 'Error while mv refreshing.'||SQLCODE||sqlerrm, SYSTIMESTAMP );
    END IF;
    BEGIN
      send_email(l_rcpt_list, v_from,v_subject,l_error_msg);
    EXCEPTION
    WHEN OTHERS THEN
      ERROR_LOG( v_package_name, 'error_track', 'Error while sending email.'||SQLCODE||sqlerrm, SYSTIMESTAMP );
    END;
  END;
END p_error;

Regards,
Nathan

[Updated on: Mon, 11 February 2013 06:29]

Report message to a moderator

Re: Sending mail when DBMS_SCHEDULER job fails [message #576966 is a reply to message #576958] Mon, 11 February 2013 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a bad code.

1/ If you have 2 statements and want to check the exceptions for both of them, then enclose them into a PL/SQL block (BEGIN EXCEPTION END) each one.

2/ If the first send_email does not work, why should the second work?

3/

Michel Cadot wrote on Sat, 02 February 2013 09:14
... just add RAISE at the end of WHEN OTHERS part...


Regards
Michel

[Updated on: Mon, 11 February 2013 07:39]

Report message to a moderator

Re: Sending mail when DBMS_SCHEDULER job fails [message #576974 is a reply to message #576966] Mon, 11 February 2013 08:39 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi Michel,

Thanks for response.I modified code as per your suggestion.I read when_others_then many times still I am confused.I think it is required to capture the error at the highest level if so many procedure as calling to each other otherwise we'll be missing the actual error.

But the following case it is standalone procedure and it is called by scheduler.If it raises exception, though only a log will be generated on USER_SCHEDULER_JOB_RUN_DETAILS and running status will be captured as FAILED.So I think RAISE should be used only case of refreshing the mv and not sending an email.Because the purpose is to checking mv refresh happened correctly or not.Please suggest me.

PROCEDURE p_error
IS
  v_from        VARCHAR2(80) := 'scott@aol.com';
  v_subject     VARCHAR2(80) := 'refresh status';
  l_error_msg   VARCHAR2(200):='refresh failed.';
  l_success_msg VARCHAR2(200):='Refreshed successfully.';
  l_rcpt_list   VARCHAR2(20);
  l_status      NUMBER;
BEGIN
  l_rcpt_list:='miller@aol.com';
  BEGIN
    dbms_mview.REFRESH('mv_refresh');
    l_status:=1;
  EXCEPTION
  WHEN OTHERS THEN
    ERROR_LOG( v_package_name, 'error_track', 'Error while mv refreshing.'||SQLCODE||sqlerrm, SYSTIMESTAMP );
    l_status:=0;
    RAISE;
  END;
  BEGIN
    IF l_status=1 THEN
      send_email(l_rcpt_list, v_from,v_subject,l_success_msg);
    ELSE
      send_email(l_rcpt_list, v_from,v_subject,l_error_msg);
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    ERROR_LOG( v_package_name, 'error_track', 'Error while sending email.'||SQLCODE||sqlerrm, SYSTIMESTAMP );
    RAISE;
  END;
END;
END p_error;


Regards,
Nathan
Re: Sending mail when DBMS_SCHEDULER job fails [message #576979 is a reply to message #576974] Mon, 11 February 2013 09:09 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
.I read when_others_then many times still I am confused.I think it is required to capture the error at the highest level if so many procedure as calling to each other otherwise we'll be missing the actual error.


No, you don't miss the error, it is propagated till the first level.
If you want to trap a non predefined error you can use one of the followings:
DECLARE
  excpt1 exception;
  excpt2 exception;
  PRAGMA EXCEPTION_INIT (excpt2, -20000);
BEGIN
  ...
  RAISE excpt1; -- exception with no number
  ...
  <calling something that may raise -20000 error>
  ...
EXCEPTION
  WHEN excpt1 THEN ...
  WHEN excpt2 THEN ...
END;

You can even use exceptions you declare in a package spec...

Regards
Michel
Previous Topic: How to display the result horizontally
Next Topic: Pivot output in equijoin
Goto Forum:
  


Current Time: Mon Dec 22 14:23:27 CST 2014

Total time taken to generate the page: 0.10192 seconds