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;
/

Posted in Uncategorized | Leave a comment

params keyword in C#

Hi all,

Have you ever used params keyword in your programs? It is a keyword used to pass various numbers of arguments to a method. You can pass the arguments as comma separated list to the methods that accepts its parameters with params keyword. Methods with params keyword and without params keyword are called in the program differently. For example:

static int sum1(params int [] values)
{
int total = 0;
foreach (int value in values)
{  total += value; }
return total;
}

static int sum 2(params int [] values)
{
int total = 0;
foreach (int value in values)
{  total += value; }
return total;
}

static void main()
{
int result1 = sum1(); // can be used with no parameters
int result2 = sum1(1);
int result3 = sum1(1, 3, 4);
int result4 = sum2(new int [] {1});
int result4 = sum2(new int [] {1, 2, 5, 6});
}

 

Posted in Uncategorized | Leave a comment

Value Type & Reference Type in C#

These two data types are important for the performance of the applications. Built in data types like int, float, double and user defined structure are value types. Value type is stored in stack of the memory.

String data type is the execption that it is reference type. Additionally instance of a class or an array are the reference types. Reference types are stored in heap of the memory.

For the performance issues using stack is more effient. However, value types have limited lifetime, so using stack may not be convenient for data sharing between different classes.  Using heap has advantage for sharing the data between classes.

 

Posted in Uncategorized | Leave a comment

Debug & Release

When you develop a project in Visual Studio, there are two options to run your project. These are Debug & Release mode. If your development processes are continuing it would be better to use Debug mode. On the other hand, if you complete your development, you should use Release mode. Thus when you publish your project, it will run with a better performance.

Posted in Uncategorized | 2 Comments

Takvim ve Yazılım

Genel kültür ve kod bilgimizi artıracak güzel bir yazı… “Takvim ve Yazılım”

Link | Posted on by | 1 Comment

Triplestores

RDF data types are stored in triplestores and they are queried using SPARQL. Key feature of triplestore is the ability to do inference. Triplestores can be classified in three types of categories: Native triplestores, RDBMS-backed triplestores, NoSql triplestore.

Posted in Uncategorized | Leave a comment

MD5 Value for File Safety

Hi all,

We are often download files from internet and sometimes we see MD5 values near this downloadable files. MD5 value is a unique, special value belonging to data. Thus, after we download a file and get its MD5 value, we must compare it with the given one. If they are exactly the same we can be sure that this file is not damaged and we can be inconfort to use file.

On the other hand, what do you think about the MD5 values of 2 files that have same content and have exactly the same name? Will these MD5 values differ or not?

The answer is yes, they will be different because fisrt of all they have been created certainly at different times. This will even affect MD5 value, so copied files MD5 values will be different.

Posted in Uncategorized | Leave a comment