How to create an alert if a job fails in dbms_job package Options

[Maruthi Krishna]

Hi,

How can we create an alert i.e, send an e-mail to the user if the job fails ( or even if the job is sussesful) in the dbms_job package ?

A comprehensive way of achieving this task is through scheduling in the cron, but in my scenario not all the users have unix a/c s.

Can somebosy suggest me how to do this task through Oracle Scheduler? or do we need to append any piece of code in the dbms_job package to get an e-mail to the user upon the failure (or success) of the job.


Regards
Maruthi Krishna

[dave]

put that code to email in your job procedure

[Maruthi Krishna]

yes that is what i am asking ,

1.what shld be the code i need to append ?
2.where exactly i need insert the code in the dbms_job procedure ?

[burleson]

Hi Maruthi , and welcome to the forum!

>> How can we create an alert

In PL/SQL, right here:

http://www.dba-oracle.com/t_email_mailing_messages_plsql.htm

http://www.dba-oracle.com/oracle_tips_10g_utl_mail.htm

CODE

create or replace procedure
   e_mail_message
(
   from_name varchar2,
   to_name varchar2,
   subject varchar2,
   message varchar2
)
is
  l_mailhost    VARCHAR2(64) := 'burleson.cc';
  l_from        VARCHAR2(64) := 'linda@remote-dba.net';
  l_to          VARCHAR2(64) := 'don@burleson.cc';
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
  UTL_SMTP.helo(l_mail_conn, l_mailhost);
  UTL_SMTP.mail(l_mail_conn, l_from);
  UTL_SMTP.rcpt(l_mail_conn, l_to);

  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: '    || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'From: '    || l_from || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'To: '      || l_to || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, ''          || Chr(13));
  
  FOR i IN 1 .. 10 LOOP
    UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || To_Char(i) || Chr(13));
  END LOOP;
  
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/

*****************************************************
>> send an e-mail to the user if the job fails

You simply check the status of the dbms_scheduler job (when others) and invoke the e-nail procedure.

If you don't want to write your own code, see Dr. Hall's book on Oracle Job Scheduling:

http://www.rampant-books.com/book_2005_1_scheduling.htm

[Maruthi Krishna]

Thank you so much !

Regards
Maruthi Krishna Turaga

[Maruthi Krishna]

Hi ,

I have a question overe here.

Can create a seperate procedure (ie., above mentioned code) or do i need tp append the above procedure in the existing SYS.dbms_job package ?

[Maruthi Krishna]

soryy ..

* Can I create ....|| ||

[dave]

do what you want, your choice

[Maruthi Krishna]

Hi ,

I have encountered the following error when am trying to execute the procedure (attached here by) :

SQL> @ /oracle/e_mail_message.txt

Procedure created.

SQL> execute e_mail_message('x','y','f','u');
BEGIN e_mail_message('x','y','f','u'); END;

*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.E_MAIL_MESSAGE", line 16
ORA-06512: at line 1

I have checked for the prescence of the following packages:

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

The first package is already present but not the second one .

SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
SP2-0310: unable to open file "/oracle/app/oracle/product/9.2.0/rdbms/admin/initplsj.sql"


Can anyone help me out how sort out the above issue?

Attached File(s)

 e_mail_message.sql.txt ( 1.22K ) Number of downloads: 4

 

[aussie_dba]

Issue is right here:

https://metalink.oracle.com/metalink/plsql/...1,1,1,helvetica

Go to Control Panel->Add or Remove Programs->Click on
Add/Remove Wndows Components
Check IIS check box.
Select Internet Information Service (IIS) option and click on Details button
Check whether SMTP Service is checked or not.
If not selected then select SMTP check box.

This process should be done on server.

It will help out from ORA-29278: SMTP transient error: 421 Service not available problem.

[Maruthi Krishna]

Hi,

Thanks for the reply , but I am using a unix system :

HPUX: Version 10.2.0.4.0
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

what might be the solution now ?


P.S: not able to access the above link

[onkar]

Here is the information:

Applies to:
PL/SQL - Version: 9.2.0.7.0
This problem can occur on any platform.
Symptoms
When attempting to utilize UTL_SMTP on Microsoft Windows 2003 Server utilizing Oracle Database
9.2.0.7, calls for UTL_SMTP fail with:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at line 4

Attempts to ping the server show it can be communicated to yet the ethereal network analysis utility shows that the SMTP
requests do not even get to the network.
Cause
Another application, such as an anti-virus utility, is blocking access to the port required for UTL_SMTP..
Solution
To implement the solution, please execute the following steps:

1. Shutdown any applications that are blocking or preventing use of the port, 25, required by UTL_SMTP.

[Maruthi Krishna]

>>Shutdown any applications that are blocking or preventing use of the port, 25, required by UTL_SMTP.

how to know what are all the applications which are blocking or preventing the use of port 25, required by UTL_SMTP ?

[onkar]

Try "netstat -an"

Also check http://www.garykessler.net/library/is_tools_scan.html