473,324 Members | 2,456 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

send a mail if insert operation fails

I have a dbms job, which will insert data from source to target table.
after insertion, a mail has to be sent to admin.

1) if it failed, fail message has to be sent.
2) if succeeded ,success message has to be sent to admin.

Can anybody please let me know which code i have to write and where i have to append this in the dbms job?

Thanks in advance.
Archana
Nov 7 '06 #1
3 2902
milonov
32
Hi, Archana.

You can use UTL_SMTP oracle package:

To enable UTL_SMTP in the database java must be enabled run

$ORACLE_HOME/javavm/install/initjvm.sql
$ORACLE_HOME/javavm/install/init_jis.sql
$ORACLE_HOME/rdbms/admin/initplsj.sql

then use function below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE PROCEDURE send_mail (
  3. pSender    VARCHAR2,
  4. pRecipient VARCHAR2,
  5. pSubject   VARCHAR2,
  6. pMessage   VARCHAR2) IS
  7.  
  8. mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
  9. crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
  10. mesg      VARCHAR2(1000);
  11. mail_conn utl_smtp.connection;
  12.  
  13. BEGIN
  14.    mail_conn := utl_smtp.open_connection(mailhost, 25);
  15.  
  16.    mesg := 'Date: ' ||
  17.         TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
  18.            'From: <'|| pSender ||'>' || crlf ||
  19.            'Subject: '|| pSubject || crlf ||
  20.            'To: '||pRecipient || crlf || '' || crlf || pMessage;
  21.  
  22.    utl_smtp.helo(mail_conn, mailhost);
  23.    utl_smtp.mail(mail_conn, pSender);
  24.    utl_smtp.rcpt(mail_conn, pRecipient);
  25.    utl_smtp.data(mail_conn, mesg);
  26.    utl_smtp.quit(mail_conn); 
  27. EXCEPTION
  28.   WHEN INVALID_OPERATION THEN
  29.     NULL;
  30.   WHEN TRANSIENT_ERROR THEN
  31.     NULL;
  32.   WHEN PERMANENT_ERROR THEN
  33.     NULL;
  34.   WHEN OTHERS THEN
  35.     NULL; 
  36. END send_mail;
  37. /
  38.  
Best Regards,
Michael Milonov
http://www.snotratech.com

I have a dbms job, which will insert data from source to target table.
after insertion, a mail has to be sent to admin.

1) if it failed, fail message has to be sent.
2) if succeeded ,success message has to be sent to admin.

Can anybody please let me know which code i have to write and where i have to append this in the dbms job?

Thanks in advance.
Archana
Nov 7 '06 #2
Hi, Archana.

You can use UTL_SMTP oracle package:

To enable UTL_SMTP in the database java must be enabled run

$ORACLE_HOME/javavm/install/initjvm.sql
$ORACLE_HOME/javavm/install/init_jis.sql
$ORACLE_HOME/rdbms/admin/initplsj.sql

then use function below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE PROCEDURE send_mail (
  3. pSender    VARCHAR2,
  4. pRecipient VARCHAR2,
  5. pSubject   VARCHAR2,
  6. pMessage   VARCHAR2) IS
  7.  
  8. mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
  9. crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
  10. mesg      VARCHAR2(1000);
  11. mail_conn utl_smtp.connection;
  12.  
  13. BEGIN
  14.    mail_conn := utl_smtp.open_connection(mailhost, 25);
  15.  
  16.    mesg := 'Date: ' ||
  17.         TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
  18.            'From: <'|| pSender ||'>' || crlf ||
  19.            'Subject: '|| pSubject || crlf ||
  20.            'To: '||pRecipient || crlf || '' || crlf || pMessage;
  21.  
  22.    utl_smtp.helo(mail_conn, mailhost);
  23.    utl_smtp.mail(mail_conn, pSender);
  24.    utl_smtp.rcpt(mail_conn, pRecipient);
  25.    utl_smtp.data(mail_conn, mesg);
  26.    utl_smtp.quit(mail_conn); 
  27. EXCEPTION
  28.   WHEN INVALID_OPERATION THEN
  29.     NULL;
  30.   WHEN TRANSIENT_ERROR THEN
  31.     NULL;
  32.   WHEN PERMANENT_ERROR THEN
  33.     NULL;
  34.   WHEN OTHERS THEN
  35.     NULL; 
  36. END send_mail;
  37. /
  38.  
Best Regards,
Michael Milonov
http://www.snotratech.com

Thanks Michael.
I tried the same. But got this error,
ORA-29278: SMTP transient error:
Nov 8 '06 #3
milonov
32
Perhaps this thread helps you:
http://forums.oracle.com/forums/thre...887996&#887996

Best Regards,
Michael Milonov
http://www.snotratech.com
Nov 9 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: James Hong | last post by:
Help please, I try to sending an email from my html page using the java applet. but it give error on most of the PC only very few work, what is the error i make the java applet show as below ...
40
by: ian | last post by:
Hi, I'm a newbie (oh no I can here you say.... another one...) How can I get Python to send emails using the default windows email client (eg outlook express)? I thought I could just do the...
4
by: herbert | last post by:
sendmail in .NET 2.0 is as useless as it was in .NET 1.x I can't find out how to make it work. Not to mention how to write a setup project that tests/installs its requirements correctly. the...
2
by: David Lozzi | last post by:
Howdy, I'm new to .Net 2.0... Getting "System.Net.Mail.SmtpException: The operation has timed out" on this script at the smtpClient.Send(msg) line Dim msg As New MailMessage
7
by: oopsbabies | last post by:
Hello everyone, I am using Apache 1.3.33 as the web server and PHP version 4.3.10. My machine is using Windows XP 2002 professional edition which comes with a Windows firewall. I am using McAfee...
3
by: YMPN | last post by:
Hi Everyone, I'm deen from Riyadh. Please do help me with some problem i have. I have this formview control setup to recieved inputs from user (textbox, dropdownlist, others). After...
0
by: NowGeorge | last post by:
We execute dts packages to generate reports. We have an error using the send mail task. This is not a profile issue. It fails for the 3 users using this. Can open enterprise manager and execute...
7
by: Rob Dob | last post by:
The following code is giving me a timeout problem., no matter what I do I can't send a piece of mail using .net2.0 System.Net.Mail.SmtpClient via port 465 and using ssl, if however I try using...
4
by: gurufordy | last post by:
Hello. Trying to use the ASP.net user functionality but it keeps failing on me. I have created a login and registration page for my site. When you fill in the registration form it should send a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.