Oracle Scripts - dbapool.com

REM: Script to send mails using UTL_SMTP
REM:
REM: Author: Devesh
REM: Date Submitted: 02-28-2007
REM: Date Posted: 02-28-2007
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

CREATE OR REPLACE PROCEDURE Mailout
(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2
) IS

/* One further thing is that as <CR><LF> is used to terminate lines, it is a good idea to define this early in the code. In the UTL_TCP package there is a constant called UTL_TCP.CRLF already defined. */

crlf VARCHAR2(2):= UTL_TCP.CRLF;
err_num NUMBER;
err_msg VARCHAR2(500);
message VARCHAR2(200);

/*TYPE CONNECTION: A CONNECTION is a record containing port and host information. You will use the connection object in all the other procedures but will never have to look at the contents of the connection object returned by the server.*/

connection utl_smtp.connection;

mailhost VARCHAR2(30) :='000.000.000.00'; ---Your IP of mail server.

header VARCHAR2(1000);

BEGIN

-- Start the connection.

--This is only a function only and returns an record of type CONNECTION. This is always the first thing you will do in an SMTP

connection := utl_smtp.open_connection(mailhost,25);

header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
  'From: '||sender||''||crlf||
  'Subject: '||subject||crlf||
  'To: '||recipient||crlf||
  'CC: '||ccrecipient||crlf;

message :='Hello This is a test mail plz ignore it.. '||''||crlf||'Regards,'||''||crlf||'Devesh...';

-- Handshake with the SMTP server

--This is the Handshake procedure. Pass in the connection object from earlier and a string that identifies the sending host. This is overloaded as a function which returns a REPLY. HELO is always the next thing called after an OPEN_CONNECTION()

utl_smtp.helo(connection,mailhost);

--MAIL initiates the SMTP transaction. Sender is the email address of the sender, parameters is used for extended SMTP as per RFC1869.

utl_smtp.mail(connection, sender);

--RCPT specifies the recipients of the email. Recipient is the email address of the recipient. You can call this procedure for every recipient of the message. You must call MAIL before RCPT so that the SMTP transaction has started. Again, RCPT is overloaded as a function.

utl_smtp.rcpt(connection, recipient);

utl_smtp.rcpt(connection, ccrecipient);

--UTL_SMTP.OPEN_DATA(connection IN): Sends the DATA command. All subsequent calls to UTL_SMTP

utl_smtp.open_data(connection);

-- Write the header

--adds data to the string to be sent and finally UTL_SMTP

utl_smtp.write_data(connection, header);

-- The crlf is required to distinguish that what comes next is not simply part of the header..

utl_smtp.write_data( connection, '' || crlf );

utl_smtp.write_data(connection, crlf || message);

--Close and Quit the connection

utl_smtp.close_data(connection);