Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How to create an alert if a job fails in dbms_job package
Maruthi Krishna
post Feb 3 2009, 01:54 PM
Post #1


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



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


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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
dave
post Feb 3 2009, 02:47 PM
Post #2


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



put that code to email in your job procedure
Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 3 2009, 03:06 PM
Post #3


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



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 ?

wink.gif



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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
burleson
post Feb 3 2009, 03:20 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 3 2009, 06:20 PM
Post #5


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



Thank you so much !

Regards
Maruthi Krishna Turaga


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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 10 2009, 03:45 AM
Post #6


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



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 ?



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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 10 2009, 03:52 AM
Post #7


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



soryy ..

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


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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
dave
post Feb 10 2009, 03:57 AM
Post #8


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



do what you want, your choice
Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 11 2009, 10:03 AM
Post #9


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



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)
Attached File  e_mail_message.sql.txt ( 1.22K ) Number of downloads: 5
 


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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
aussie_dba
post Feb 11 2009, 01:06 PM
Post #10


Advanced Member
***

Group: Members
Posts: 618
Joined: 28-August 04
Member No.: 495




Issue is right here:

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



QUOTE
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.

Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 12 2009, 04:21 AM
Post #11


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



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


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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
onkar
post Feb 12 2009, 05:03 AM
Post #12


Advanced Member
***

Group: Members
Posts: 77
Joined: 12-January 05
From: India
Member No.: 1,280



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.



--------------------
Thanks & Regards,
T. Onkar Nath
(Oracle Certified DBA 9i)
(Teradata Certified DBA V2R5)
email : to_onkar@yahoo.com
Go to the top of the page
 
+Quote Post
Maruthi Krishna
post Feb 13 2009, 06:41 AM
Post #13


Advanced Member
***

Group: Members
Posts: 59
Joined: 3-February 09
From: India
Member No.: 39,635



>>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 ?


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


Thanks & Regards
Maruthi Krishna Turaga
Go to the top of the page
 
+Quote Post
onkar
post Feb 13 2009, 09:53 AM
Post #14


Advanced Member
***

Group: Members
Posts: 77
Joined: 12-January 05
From: India
Member No.: 1,280



Try "netstat -an"

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


--------------------
Thanks & Regards,
T. Onkar Nath
(Oracle Certified DBA 9i)
(Teradata Certified DBA V2R5)
email : to_onkar@yahoo.com
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 08:23 AM