Search This Blog

Monday, February 4, 2008

Send Email through PL/SQL

The short procedure can be used to send a mail through PL/SQL.
Although there is a limitation to this as the code below does not allow you to send attachments. This can only be used to send text messages.


CREATE OR REPLACE PROCEDURE email_message (
from_name VARCHAR2
, to_name VARCHAR2
, subject VARCHAR2
, MESSAGE VARCHAR2
)
IS
l_mailhost VARCHAR2 (64) := 'server.com';
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, from_name);
UTL_SMTP.rcpt (l_mail_conn, to_name);
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: ' || from_name || CHR (13));
UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || subject || CHR (13));
UTL_SMTP.write_data (l_mail_conn, 'To: ' || to_name || CHR (13));
UTL_SMTP.write_data (l_mail_conn, Message );

UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);
END;
/


Emails with attachment can be done using Java as a wrapper. The metalink Note:120994.1 has a very good explanation on how to create a program that facilates sending emails with attachment.

3 Comments:

Mirza's said...

Hi Suresh Sir,
when I execute thi, i got this following error :-
The following error has occurred:

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at "SYS.EMAIL_MESSAGE", line 11
ORA-06512: at line 11
------

Please check and do the needful...
Rgds,
Mirza
Mirza2k8@gmail.com

Suresh Vaishya said...

What version of Oracle you are on.
Please check if have you entered correct mail server name. The server name you entered does not allow SMTP. Check if default Port 25 is open and there is no firewal. Consult your DBA and he should be able to guide you on this.

Yadu said...

Hi Suresh,

How can we send mails from procedure if only a particluar query yields a result?I would like to see the results sent in the mail as well.If the qurey has no records to show up,then I would not want the mails to be sent.Looking for your help.Thanks in advance!

Copyright (c) All rights reserved. Presented by Suresh Vaishya