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
3 2902
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: -
-
CREATE OR REPLACE PROCEDURE send_mail (
-
pSender VARCHAR2,
-
pRecipient VARCHAR2,
-
pSubject VARCHAR2,
-
pMessage VARCHAR2) IS
-
-
mailhost CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
-
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
-
mesg VARCHAR2(1000);
-
mail_conn utl_smtp.connection;
-
-
BEGIN
-
mail_conn := utl_smtp.open_connection(mailhost, 25);
-
-
mesg := 'Date: ' ||
-
TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
-
'From: <'|| pSender ||'>' || crlf ||
-
'Subject: '|| pSubject || crlf ||
-
'To: '||pRecipient || crlf || '' || crlf || pMessage;
-
-
utl_smtp.helo(mail_conn, mailhost);
-
utl_smtp.mail(mail_conn, pSender);
-
utl_smtp.rcpt(mail_conn, pRecipient);
-
utl_smtp.data(mail_conn, mesg);
-
utl_smtp.quit(mail_conn);
-
EXCEPTION
-
WHEN INVALID_OPERATION THEN
-
NULL;
-
WHEN TRANSIENT_ERROR THEN
-
NULL;
-
WHEN PERMANENT_ERROR THEN
-
NULL;
-
WHEN OTHERS THEN
-
NULL;
-
END send_mail;
-
/
-
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
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: -
-
CREATE OR REPLACE PROCEDURE send_mail (
-
pSender VARCHAR2,
-
pRecipient VARCHAR2,
-
pSubject VARCHAR2,
-
pMessage VARCHAR2) IS
-
-
mailhost CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
-
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
-
mesg VARCHAR2(1000);
-
mail_conn utl_smtp.connection;
-
-
BEGIN
-
mail_conn := utl_smtp.open_connection(mailhost, 25);
-
-
mesg := 'Date: ' ||
-
TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
-
'From: <'|| pSender ||'>' || crlf ||
-
'Subject: '|| pSubject || crlf ||
-
'To: '||pRecipient || crlf || '' || crlf || pMessage;
-
-
utl_smtp.helo(mail_conn, mailhost);
-
utl_smtp.mail(mail_conn, pSender);
-
utl_smtp.rcpt(mail_conn, pRecipient);
-
utl_smtp.data(mail_conn, mesg);
-
utl_smtp.quit(mail_conn);
-
EXCEPTION
-
WHEN INVALID_OPERATION THEN
-
NULL;
-
WHEN TRANSIENT_ERROR THEN
-
NULL;
-
WHEN PERMANENT_ERROR THEN
-
NULL;
-
WHEN OTHERS THEN
-
NULL;
-
END send_mail;
-
/
-
Best Regards,
Michael Milonov http://www.snotratech.com
Thanks Michael.
I tried the same. But got this error,
ORA-29278: SMTP transient error:
Sign in to post your reply or Sign up for a free account.
Similar topics
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
...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
|
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...
| |