asked on

UTL_SMTP

I need a real life example on how to use UTL_SMTP package to send e-mail from oracle procedure

Here is one:

I used this for my database before Java stored procedure comes along. I got this originally from the web post too.

Here is a simple PLSQL to send email from Oracle Database (8.x) and higher in HTML format (so plain text is easier). all you need is to change the sendorAddress, receiverAddress and smtp host.

----- Cut it from Here---------------------

Declare

SendorAddress Varchar2(30) := 'swadhwa@TEST.com';

/* Address of the person who is sending Email */

ReceiverAddress varchar2(30) := 'DBA@Test.com';

/* Address of the person who is receiving Email */

EmailServer varchar2(30) := 'mail.Test.com';

/* Address of your Email Server Configured for sending emails */

Port number := 25;

/* Port Number responsible for sending email */

conn UTL_SMTP.CONNECTION;

/* UTL_SMTP package establish a connection with the SMTP server */

crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );

/* crlf used for carriage return */

mesg VARCHAR2( 4000 );

/* Variable for storing message contents */

mesg_body varchar2(4000)

/* Variable for storing HTML code */

:= ' <html>

<head>

<title>Oracle Techniques By Sameer Wadhwa</title>

</head>

<body bgcolor="#FFFFFF" link="#000080">

<table cellspacing="0" cellpadding="0" width="100%">

<tr align="LEFT" valign="BASELINE">

<td width="100%" valign="middle"><h1><font color="#00008B"><b>Send Mail in HTML Format</b></font></h1>

</td>

</table>

<ul>

<li><b><a href="www.geocities.com/samoracle">Oracle Techniques is for DBAs </li>

<l><b> by Sameer Wadhwa </b> </l>

</ul>

</body>

</html>';

BEGIN

/* Open Connection */

conn:= utl_smtp.open_connection( EmailServer, Port );

/* Hand Shake */

utl_smtp.helo( conn, EmailServer );

/* Configure Sender and Recipient with UTL_SMTP */

utl_smtp.mail( conn, SendorAddress);

utl_smtp.rcpt( conn, ReceiverAddress );

/* Making Message buffer */

mesg:=

'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||

'From:'||SendorAddress|| crlf ||

'Subject: Mail Through ORACLE Database' || crlf ||

'To: '||ReceiverAddress || crlf ||

'' || crlf ||mesg_body||'';

/* Configure Sending Message */

/*You need to put 'MIME-Verion: 1.0' (this is case-sensitive!) */

/*Content-Type-Encoding is actually Content-Transfer-Encoding. */

/*The MIME-Version, Content-Type, Content-Transfer-Encoding should */

/* be the first 3 data items in your message */

utl_smtp.data(conn, 'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||mesg);

/* Closing Connection */

utl_smtp.quit( conn );

/* End of logic */

END;

/

schwertner

PROCEDURE SEND_EMAIL (sender IN VARCHAR2,
recipient IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp.aaaaa.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
-- Handle the error
END;

You need version >=8.1.6, and Java installed for this to work.
Create a job that checks what it needs to check and when needed, runs this:

PROCEDURE SEND_MAIL
(V_MAILTO IN VARCHAR2
,V_MAILFROM IN VARCHAR2
,V_SUBJECT IN VARCHAR2
,V_BODY IN LONG
,V_CC IN VARCHAR2 := null
,V_BCC IN VARCHAR2 := null
)
IS
-- PL/SQL Specification
CONN UTL_SMTP.CONNECTION;
dato varchar2(4000);

-- PL/SQL Block
BEGIN
DECLARE
CONN UTL_SMTP.CONNECTION;
dato VARCHAR2(4000);
nSqlCode NUMBER(8);
vSqlErrm VARCHAR2(4000);
vSrv VARCHAR2(100);

BEGIN

vSrv := 'smtp.server.com;
CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);
UTL_SMTP.HELO(CONN,'Origin.server.com');
UTL_SMTP.MAIL(CONN,v_mailfrom);
UTL_SMTP.RCPT(CONN,v_mailto);
UTL_SMTP.OPEN_DATA(CONN);

UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);

-- Splits body (long) into varchar2s.

FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP

DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);

END LOOP;

UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);

EXCEPTION
WHEN utl_smtp.transient_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;

BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20100, vSqlErrm);

WHEN utl_smtp.permanent_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20200, vSqlErrm);
END;
END SEND_MAIL;

Danielzt

Please read this.
This is good paper for how to set up and use UTL_SMTP.

http://www.geocities.com/samoracle/swHowtoSendMail.htm

ASKER CERTIFIED SOLUTION

konektor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial