send mail Procedure

set serveroutput on size 100000

CREATE OR REPLACE PROCEDURE send_mail_halim (
msg_from VARCHAR2 := 'oracle',
msg_to VARCHAR2,
msg_subject VARCHAR2 := 'E-Mail message from your database',
msg_text VARCHAR2 := ''
) IS
c UTL_TCP.connection;
rc INTEGER;
BEGIN ----SMTP server running and port must be correct
c := UTL_TCP.open_connection ('10.11.1.40', 25); -- open the SMTP port 25 on local machine
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'HELO localhost');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || msg_from);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'RCPT TO: ' || msg_to);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'DATA'); -- Start message body
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'Subject: ' || msg_subject);
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, msg_text);
rc := UTL_TCP.write_line (c, '.'); -- End of message body
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'QUIT');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
UTL_TCP.close_connection (c); -- Close the connection
EXCEPTION
WHEN OTHERS THEN
raise_application_error
(-20000,
'Unable to send e-mail message from pl/sql because of: '
|| SQLERRM
);
END;


execute SEND_MAIL_halim(msg_to =>'halim@bank.com.bd');


=================================================================================


UTL_SMTP and sending mail

UTL_SMTP, introduced for the first time in Oracle 8.1.6, is an nterface to the Simple Mail Transfer Protocol. It requires that you have an SMTP server in your network somewhere ? most sites I have been to have at least one SMTP server running as it is the most popular method for sending mail.
The UTL_SMTP package is best suited for sending small, text only emails from the database. While its API supports the sending of attachments and everything else ? it is left to you to actually encode the multi-part document ? for example turning binary attachments into mime-encoded documents.

Use UTL_SMTP that provides somewhat much more functionality ? including the ability to easily send attachments with the email. Since SMTP is a very low level protocol, we'll reuse existing public domain code to get an SMTP interface at much higher level ? and we'll get it with very little code.

UTL_SMTP ?

In the DBMS_JOB section, we explored how to make sending email using UTL_SMTP appear to execute faster. We also made email transactional in nature in that section ? if you rollback the email does not get sent, if you commit ? out it goes. I highly recommend the use of DBMS_JOB as a layer on your emails routines for these reasons. In that section, the example UTL_SMTP routine we used was:

create or replace
2 PROCEDURE send_mail (p_sender IN VARCHAR2,
3 p_recipient IN VARCHAR2,
4 p_message IN VARCHAR2)
5 as
6 l_mailhost VARCHAR2(255) := 'boss.oracle.com';
7 l_mail_conn utl_smtp.connection;
8 BEGIN
9 l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
10 utl_smtp.helo(l_mail_conn, l_mailhost);
11 utl_smtp.mail(l_mail_conn, p_sender);
12 utl_smtp.rcpt(l_mail_conn, p_recipient);
13 utl_smtp.open_data(l_mail_conn );
14 utl_smtp.write_data(l_mail_conn, p_message);
15 utl_smtp.close_data(l_mail_conn );
16 utl_smtp.quit(l_mail_conn);
17 end;
18 /
Procedure created.


begin
2 send_mail( 'halim@bank.com.bd',
3 'halim@yahoo.com',
4 'Hello' );
5 end;
6 /

PL/SQL procedure successfully completed.


That works OK but is very limited in nature. It sends email to exactly one recipient, you cannot CC or BCC anyone, you cannot setup a subject -- the email always arrives with a ?blank? subject line. We would like to support more options with this package.


A specification for a PLSQL package that supports this might look like the following. In here, we define an array type to allow for a caller to easily send a list of recipients as well as provide the external specification of the PLSQL routine we will be implementing:


create or replace package mail_pkg
2 as
3 type array is table of varchar2(255);
4
5 procedure send( p_sender_email in varchar2,
6 p_from in varchar2,
7 p_to in array default array(),
8 p_cc in array default array(),
9 p_bcc in array default array(),
10 p_subject in varchar2,
11 p_body in long );
12 end;
13 /
Package created.

The package body for this implementation is relatively straightforward ? if understand just enough of the SMTP protocol and what an email looks like (how email clients get the From, To, CC and so on). Before we look at the code, we?ll look at what an email might actually look like. Consider the following ASCII text: