Sending Mail From ORACLE or C#

Hi all, a few days ago I have experienced sending e-mail with ORACLE. First of all to send mail with code you have to know about mail servers.  In daily life you are sending mails via hotmail, gmail or your companies mail server and you do not need to know details about how these servers send your mails. You are writing your mail body, give receipient address and click to send button. This is all you need to do.

However, when you need to send mail from your code you need to know about mail servers and how they are sending mails. SMTP server can be used for this purpose. For example, if you want to send mail from your C# code, you have to define some specific mail configuration data about SMTP in your code. WebMail class will help you doing these configurations as seen below.

WebMail.SmtpServer = “mail.example.com”; //Smtp server address
WebMail.EnableSsl = false; //It must be true if you use Ssl
WebMail.UserName = “help@example.com”; //Address that will send the mail
WebMail.Password = “xxxx”; //Password of the Address
WebMail.SmtpPort = 587; // SMTP send mail Port

On the other hand, if you want to send mail from ORACLE again you must use to SMTP server. In this point there is a small trick about the functions that ORACLE supplies for the mail sent operation. I have tried UTL_MAIL and UTL_SMTP packages for sending e-mail from ORACLE. I have created procedures for both two as seen below.

 

DECLARE Cursor cursorx IS Select DISTINCT EMAIL from VW_SEND_MAIL;
BEGIN
FOR f1 in cursorx LOOP
EXECUTE IMMEDIATE ‘ALTER SESSION SET smtp_out_server = ”127.0.0.1”’;
UTL_MAIL.send(sender => ‘help@example.com’,
recipients => ‘irem@example.com’,
subject => ‘MAIL SUBJECT’,
message => ‘Mail Body’ ,
mime_type => ‘text; charset=us-ascii’);
END LOOP;
END;
END;
/

This code above uses the UTL_MAIL package and in order to make it run you must configure SMTP server in your localhost. IIS supplies it for you. After you finish your configuration, this procedure sends mail, it runs. However, it has problems. For example, your procedure will not run on a scheduled job and you will not be able to send mail from different mail servers such as gmail, hotmail.

For this reason, using UTL_SMTP is more efficient. To use it you UTL_TCP package must be loaded and you need a configured SMTP server apart from your localhost. In your code/procedure you must give the SMTP IP and Port. Additionally, system admin of this SMTP server must configure your IP address to use the SMTP server. Then the procedure seen below will send mail and also your scheduled job that holds your procedure will work successfully.

 

CREATE OR REPLACE PROCEDURE SEND_MAIL2
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2 (50) := ‘—-=*#abc1234321cba#*=’;
l_mesaj CLOB;
l_mesaj_body CLOB;
vstart NUMBER := 1;
vlength NUMBER := 3999;
l_table CLOB;
BEGIN
FOR s IN (SELECT gonderen_mail, sifre, host_adresi, kullanici_adi, gonderen_isim
FROM tbl_mail_ayarlari
WHERE ROWNUM = 1)
LOOP
FOR r IN (SELECT EMAIL,COUNT(EMAIL) AS TALEP_SAYISI FROM VW_HATIRLATMA_MAILI GROUP BY EMAIL)
LOOP
l_mail_conn := UTL_SMTP.open_connection (s.host_adresi, 587);
UTL_SMTP.ehlo (l_mail_conn, s.host_adresi);
UTL_SMTP.command (l_mail_conn, ‘AUTH LOGIN’);
UTL_SMTP.command
(l_mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode
(UTL_RAW.cast_to_raw (s.kullanici_adi)
)
)
);
UTL_SMTP.command
(l_mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (s.sifre)
)
)
);
UTL_SMTP.mail (l_mail_conn, s.kullanici_adi);
UTL_SMTP.rcpt (l_mail_conn, r.EMAIL);
UTL_SMTP.open_data (l_mail_conn);
UTL_SMTP.write_data (l_mail_conn,
‘Subject: ‘ || ‘HATIRLATMA’ || UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn,
‘Date: ‘
|| TO_CHAR (SYSDATE, ‘dd.mm.yyyy hh24:mi:ss’)
|| ‘ +0300 (UTC)’
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn,
‘From: ‘ || s.gonderen_isim || UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn, ‘To: ‘ || r.EMAIL || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn,
‘MIME-Version: 1.0’ || UTL_TCP.crlf);
UTL_SMTP.write_data
(l_mail_conn,
‘Content-Type: multipart/alternative; boundary=”‘
|| l_boundary
|| ‘”‘
|| UTL_TCP.crlf
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn, ‘–‘ || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn,
‘Content-Type: text/html; charset=”utf-8″‘
|| UTL_TCP.crlf
|| UTL_TCP.crlf
);

l_mesaj := ‘Islem yapmaniz gereken ‘ || r.TALEP_SAYISI|| ‘ adet talep bulunmakta. Lutfen kontrol ediniz.</br></br> ‘;
l_table :=”;
FOR t IN (SELECT * FROM VW_HATIRLATMA_MAILI WHERE EMAIL = r.EMAIL)
LOOP
l_mesaj_body :='<tr><td style=”width:100px;text-align:center;”>’||t.IDN_TALEP || ‘</td><td style=”text-align:center;width:100px;”>’|| t.KAYIT_TARIHI || ‘</td></tr>’;
l_table :=l_table || l_mesaj_body;
END LOOP;
UTL_SMTP.write_data (l_mail_conn, l_mesaj || ‘<table style=”border-collapse: collapse;”><tr><th style=”width:100px;border-bottom: 1px solid #ddd;”>Talep No</th><th style=”width:100px;border-bottom: 1px solid #ddd;”>Talep Tarih</th></tr>’||l_table || ‘</table></br></br>Doganata Elektronik A.S.</br></br>help.doganata.com’);

UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn,
‘–‘ || l_boundary || ‘–‘ || UTL_TCP.crlf
);
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);

END LOOP;
END LOOP;
END;
/

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s