Send
Mail through database (10g) using UTL_MAIL
from
http://www.psoug.org/reference/utl_mail.html
http://www.e-ammar.com/Oracle_TIPS/oramail.htm
http://www.dizwell.com/prod/node/135
This note shows how to send emails with the new package provided in
10g, UTL_MAIL.
UTL_MAIL is a wrapper, which internally uses UTL_TCP and UTL_SMTP, the
"old" packages to send emails.
You can see that, when you receive an errorstack. It contains the
functions UTL_TCP and UTL_SMTP. The UTL_MAIL package is much easier to
use than the UTL_SMTP package.
The package is loaded by running the following scripts:
CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
In addition the SMTP_OUT_SERVER parameter must be set to identify the
SMTP server:
CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server =
'smpt.server.com:25' SCOPE=SPFILE;
Grants the execute on UTL_MAIL privilege either to PUBLIC or to the
user which will use the package, running one of this
statement from SYS:
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
With the configuration complete we can now send a mails:
Simple sample to test the
SEND procedure:
declare
begin
utl_mail.send(
sender =>
me@domain.com',
recipients =>
'person1@domain.com,person2@domain.com',
cc =>
'person3@domain.com',
bcc =>
'myboss@domain.com',
subject => 'Testing utl_mail',
message => 'The
receipt of this email means'||
' that shutting down the database'||
' works for UTL_MAIL '
);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'The following
error has occured: ' || sqlerrm);
END;
/
In the samples with attachments, the file cannot exceed the 32k size,
because the attachment argument type.
Sample
sending emails with attachments.
To run this example the UTL_FILE_DIR database parameter must points to
the directory 'D:\DBA\Script', and there must be a file named
Report_DB_Info.txt in that directory.
The attached file can not be
CREATE OR REPLACE DIRECTORY
UTL_FILE_DIR as 'D:\DBA\Script';
CREATE OR REPLACE PROCEDURE send_email_attach AS
fHandle
utl_file.file_type;
vTextOut
varchar2(32000);
text varchar2(32000);
BEGIN
fHandle :=
UTL_FILE.FOPEN('UTL_FILE_DIR','Report_DB_Info.txt','r');
IF
UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;
loop
begin
UTL_FILE.GET_LINE(fHandle,vTextOut);
text:=text||vTextOut;
--
dbms_output.put_line(length(text));
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
end;
END
LOOP;
--dbms_output.put_line(length(text));
UTL_FILE.FCLOSE(fHandle);
UTL_MAIL.SEND_ATTACH_VARCHAR2(sender =>
'xxx@oracle.com', recipients
=> 'xxx@oracle.com', subject => 'Testmail', message =>
'Hallo', attachment => text, ATT_INLINE => FALSE);
EXCEPTION
WHEN
OTHERS THEN
--
dbms_output.put_line('Error');
--
raise_application_error(-20001,'The following error has occured: ' ||
sqlerrm);
END;
/
Sample
sending emails with RAW attachments.
set serveroutput on;
CREATE OR REPLACE DIRECTORY
UTL_FILE_DIR as 'D:\DBA\Script';
grant read on directory UTL_FILE_DIR to
public;
DECLARE
fil
BFILE;
file_len
PLS_INTEGER;
MAX_LINE_WIDTH PLS_INTEGER := 54;
buf
RAW(2100);
amt
BINARY_INTEGER := 2000;
pos
PLS_INTEGER := 1; /* pointer
for each piece */
filepos
PLS_INTEGER := 1; /* pointer
for the file */
filenm
VARCHAR2(50) := 'clouds.jpg'; /* binary file attachment */
data
RAW(2100);
chunks
PLS_INTEGER;
len
PLS_INTEGER;
modulo
PLS_INTEGER;
pieces
PLS_INTEGER;
err_num
NUMBER;
err_msg
VARCHAR2(100);
resultraw RAW(32000);
BEGIN
/* Assign the file a handle */
fil := BFILENAME('BFILE_DIR', filenm);
/* Get the length of the file in bytes */
file_len := dbms_lob.getlength(fil);
/* Get the remainer when we divide by amt */
modulo := mod(file_len, amt);
/* How many pieces? */
pieces := trunc(file_len / amt);
if (modulo <> 0) then
pieces := pieces + 1;
end if;
/* Open the file */
dbms_lob.fileopen(fil, dbms_lob.file_readonly);
/* Read the first amt into the buffer */
dbms_lob.read(fil, amt, filepos, buf);
/* For each piece of the file . . . */
FOR i IN 1..pieces LOOP
/* Position file pointer for next read */
filepos := i * amt + 1;
/* Calculate remaining file length */
file_len := file_len - amt;
/* Stick the buffer contents into data */
data := utl_raw.concat(data, buf);
/* Calculate the number of chunks in this piece */
chunks := trunc(utl_raw.length(data) / MAX_LINE_WIDTH);
/* Don't want too many chunks */
IF (i <> pieces) THEN
chunks := chunks - 1;
END IF;
/* For each chunk in this piece . . . */
FOR j IN 0..chunks LOOP
/* Position ourselves in this piece */
pos := j * MAX_LINE_WIDTH + 1;
/* Is this the last chunk in this piece? */
IF (j <> chunks) THEN
len := MAX_LINE_WIDTH;
ELSE
len := utl_raw.length(data) - pos + 1;
IF (len > MAX_LINE_width) THEN
len := MAX_LINE_WIDTH;
END IF;
END IF;
/* If we got something, let's write it */
IF (len > 0 ) THEN
resultraw := resultraw || utl_raw.substr(data, pos, len);
END IF;
END LOOP;
/* Point at the rest of the data buffer */
IF (pos + len <= utl_raw.length(data)) THEN
data := utl_raw.substr(data, pos + len);
ELSE
data := NULL;
END IF;
/* We're running out of file, only get the rest of it */
if (file_len < amt and file_len > 0) then
amt := file_len;
end if;
/* Read the next amount into the buffer */
dbms_lob.read(fil, amt, filepos, buf);
END LOOP;
/* Don't
forget to close the file */
dbms_lob.fileclose(fil);
UTL_MAIL.SEND_ATTACH_RAW(sender =>
'xxx@oracle.com', recipients => 'xxx@oracle.com', subject =>
'Testmail', message => 'Hallo', attachment => resultraw,
att_filename => 'clouds.jpg');
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line('Error');
raise_application_error(-20001,'The following error has occured: ' ||
sqlerrm);
END;
/
Send
Mail through database using UTL_SMTP
We can send mail thorough database by using the UTL_SMTP package.
To make it work check the following configuration before
proceeding
Ø
java option must be installed in the database.
Ø
TCPconnection class (plsql.jar) must be loaded.
Ø
Init parameters
SHARED_POOL_SIZE
> = 65M
JAVA_POOL_SIZE >= 50M
50M free in the SYSTEM tablespace
250M of rollback segment space
STEP
1.
Run
initjvm.sql through svrmgrl or by connecting as SYS
ON
NT AT SQL/SVRMGRL :
$ORACLE_HOME\javavm\install\initjvm.sql
ON
UNIX AT
SQL/SVRMGRL : $ORACLE_HOME/javavm/install/initjvm.sql
STEP
2 :
ON NT
AT
DOS PROMPT :
$ORACLE_HOME\plsql\jlib>loadjava
-f -v -r -u sys/**** plsql.jar
ON
UNIX AT
UNIX PROMPT :
$ORACLE_HOME/plsql/jlib>loadjava
-f -v -r -u sys/**** plsql.jar
The
following output will be expected after running above command.
initialization
complete
loading
: oracle/plsql/net/TCPConnection
creating
:
oracle/plsql/net/TCPConnection
loading
: oracle/plsql/net/InternetAddress
creating
:
oracle/plsql/net/InternetAddress
loading
: META-INF/MANIFEST.MF
creating
:
META-INF/MANIFEST.MF
resolver
:
resolving:
oracle/plsql/net/InternetAddress
resolving:
oracle/plsql/net/TCPConnection
STEP
3:
Run
initplsj.sql through SVRMGRL or SYS
ON
NT AT SQL/SVRMGRL
: @$ORACLE_HOME\rdbms\admin\initplsj.sql
ON
UNIX AT
SQL/SVRMGRL
:
@$ORACLE_HOME/rdbms/admin/initplsj.sql
The
following output is expected
Call
completed.
Call
completed.
Setup
has
completed . Let us see the code for sending mail.
Sample PL/SQL Code to send a mail through Oracle
Database.
In
the
following plsql I am declaring variables for sender address which
is the email address of the person who is sending email , Receiver
address which is the email address of the recipient , Email server
which is the address of your email exchange server, Port number which
is dedicated for email services. Also you
have to declare a variable conn having a declaration type
UTL_SMTP.CONNECTION which establish a connection with the SMTP
server. The other
variable are:
Ø UTL_SMTP.HELO
which does handshake with SMTP server.
Ø UTL_SMTP.MAIL
which contains the mail id of sender ( FROM).
Ø UTL_SMTP.RCPT
which contains the mail id of the receiver.
Ø UTL_SMTP.DATA
which grab the message buffer and send it.
Ø UTL_SMTP.QUIT
which closes the connection.
Declare
SenderAddress
Varchar2(30)
:= 'sender@whatever.com';
ReceiverAddress
varchar2(30) := 'destination@whatever.com';
EmailServer
varchar2(30) := 'mail.Test.com';
Port
number
:=
25;
conn
UTL_SMTP.CONNECTION;
crlf
VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg
VARCHAR2( 4000 );
mesg_body varchar2(4000);
BEGIN
conn:= utl_smtp.open_connection(
EmailServer, Port );
utl_smtp.helo( conn,
EmailServer );
utl_smtp.mail( conn,
SenderAddress);
utl_smtp.rcpt( conn,
ReceiverAddress );
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 ||
' This is Mail from Oracle Database By Using UTL_SMTP Package' || crlf
||
'It is very easy to configure
Tell me if you face any problems' ;
utl_smtp.data( conn,
mesg );
utl_smtp.quit( conn );
EXCEPTION
WHEN
UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
WHEN
UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN
UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
/
Now let's try to send
a mail to multiple recipients .
I created a table mailed and insert sender and receivers mail ids
SQL>desc
mailid
Name
Null? Type
-----------------
-------- ------------
SEND_RECPT
VARCHAR2(30)
MAILIDS
VARCHAR2(50)
SQL>
select * from mailid;
SEND_RECPT
MAILIDS
------------------------------
----------------------------
SENDER
swadhwa@Test.com
RECPT
UnixAdmin@Test.com
RECPT
DBA@Test.com
RECPT
DBA2@Test.com
CREATE
or
replace PROCEDURE MAILFROMDB ( MESSAGE IN VARCHAR) AS
cursor
c1 is select send_recpt,mailids
from
mailid;
conn
UTL_SMTP.CONNECTION;
crlf
VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg
VARCHAR2( 4000 );
mesg_body varchar2(4000);
AdminMailid varchar2(30);
BEGIN
/*
Open connection */
conn:=
utl_smtp.open_connection( 'mail.Test.com', 25 );
/*
Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com' );
/* Loop for configure sender and recipient to UTL_SMTP */
for
c1rec in c1 loop
if c1rec.send_recpt = 'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
else
utl_smtp.rcpt( conn,c1rec.mailids );
end if;
end
loop;
/*
making a message buffer */
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From: Oracle Database'|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: All the Recipients '|| crlf
|| crlf ||crlf||
' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||
'It is very easy to configure Tell me if you face any
problems'||crlf||message ;
/*
Configure sending message */
utl_smtp.data( conn, mesg );
/*
closing connection */
utl_smtp.quit( conn );
END;
/
SQL>
execute mailfromdb('Send Feedback at myname@hotmail.com');
Now send mail
to
many people via To ,CC or BCC.
To
configure this and to make it more simpler I am created two tables
MAILID and MAILID_ORDER.
MAILID
Table will contain the properties of send_recpt as TO,CC or BCC. There
would be one property named as SENDER, responsible for sending mail.
MAILID_ORDER
Table will contains the corresponding order number of Send_recpt
property. This table will contains the distinct properties and order
number. Order number is very important here as my procedure will
configure recipient according to order number.
Configure
Mailid and Mailid_order table as follows :-
select
* from mailid order by 1;
SEND_RECPT
EMAIL_ADDRESS
------------------------------
-------------------------------------
BCC
shastrid@Test.com
BCC
Tony@Test.com
CC
Rohit@Test.com
CC
UNIXADMIN@Test.com
CC
John@Test.com
SENDER
DBAADMIN@Test.com
TO
swadhwa@Test.com
TO
Manager@Test.com
8 rows selected.
select
* from mailid_order;
SEND_RECPT
ORDER_NO
--------------------
----------
SENDER
0
TO
1
CC
2
BCC
3
CREATE
or
replace PROCEDURE MAILFROMDB ( MESSAGE IN VARCHAR) AS
cursor c1 is select mailid.send_recpt,email_address,order_no from
mailid,mailid_order
where Mailid.SEND_RECPT =
Mailid_order.SEND_RECPT order by order_no;
conn
UTL_SMTP.CONNECTION;
crlf
VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg
VARCHAR2( 4000 );
mesg_body varchar2(4000);
AdminMailid varchar2(30);
vToList varchar2(2000);
vCcList varchar2(2000);
vBccList varchar2(2000);
vSenderEmail varchar2(2000);
BEGIN
/*
Open connection */
conn:= utl_smtp.open_connection( 'mail.Test.com', 25 );
/*
Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com' );
/*
Loop for configure sender and recipient to UTL_SMTP */
for c1rec in c1 loop
if c1rec.send_recpt = 'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
vSenderEmail := c1rec.mailids;
else
utl_smtp.rcpt( conn,c1rec.mailids );
end if;
/* Making a TO list */
if upper(c1rec.send_recpt) = 'TO' then
vTolist := vToList || c1rec.mailids||';';
end if;
/* Making a CC list */
if upper(c1rec.send_recpt) = 'CC' then
vCclist := vCcList || c1rec.mailids||';';
end if;
/* Making a BCC list */
if upper(c1rec.send_recpt) = 'BCC' then
vBcclist := vBccList || c1rec.mailids||';';
end if;
end
loop;
/*
making a message buffer */
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From: '||vSenderEmail|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: '||VToList|| crlf||
'Cc: '||VCcList||crlf ||
'Bcc: '||VBccList||crlf||crlf||
' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||
'It is very easy to configure Tell me if you face any
problems'||crlf||message ;
/* Configure sending message */
utl_smtp.data( conn, mesg );
/* closing connection */
utl_smtp.quit( conn );
END;
/
If the
sender email is configured in Microsoft exchange server. Then you will
also get a failure notification in case of invalid email addresses.
If you
want to send mail with attachment , you can send it by using java
procedure . There is no method available as yet for it by
UTL_SMTP package. Refer to DOC 120994.1 on metalink.
Tip : How to Send
Email through Oracle Database in HTML format
Here is
a simple PLSQL to send email from Oracle Database (8.x) in HTML
format.
Declare
/* Address of the person who is sending Email */
SendorAddress Varchar2(50) := 'sender@whatever.com';
/* Address of the person who is receiving Email */
ReceiverAddress varchar2(50) := 'Receiver@whatever.com;
/* Address of your Email Server Configured for sending emails */
EmailServer varchar2(30) := 'server';
/* Port Number responsible for sending email */
Port number := 25;
/* UTL_SMTP package establish a connection with the SMTP server
*/
conn UTL_SMTP.CONNECTION;
/* crlf used for carriage return */
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
/* Variable for storing message contents */
mesg VARCHAR2( 4000 );
/* Variable for storing HTML code */
mesg_body varchar2(4000)
:= ' <html>
<head>
<title>Oracle Techniques By Diego Pafumi</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="members.fortunecity.com/dpafumi/oracle.htm">Oracle Techniques
is for DBAs </li>
<l><b>
by Diego Pafumi </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;
/