APPLIES TO:
PL/SQL - Version 10.1.0.2 and later
Information in this document applies to any platform.
"Checked for relevance on 06-Mar-2012"
GOAL
This note shows how to send emails with the UTL_MAIL package provided staring in Oracle 10g.
SOLUTION
How to Send Emails Using UTL_MAIL
1. 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 error stack. It contains the functions UTL_TCP and UTL_SMTP. The UTL_MAIL package is much easier to use than the UTL_SMTP package. To create the package, you have to run utlmail.sql and prvtmail.plb under sys. You can find the two scripts in the ORACLE_HOME/rdbms/admin directory. Prerequisite for using the procedures in the UTL_MAIL package further is the new init.ora parameter "SMTP_OUT_SERVER", which has to be set to your outgoing mailserver.
UTL_MAIL package consists of three procedures:
PROCEDURE SEND
Argument Name Typ In/Out Defaultwert
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
PROCEDURE SEND_ATTACH_RAW
Argument Name Typ In/Out Defaultwert
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
ATTACHMENT RAW IN
ATT_INLINE BOOLEAN IN DEFAULT
ATT_MIME_TYPE VARCHAR2 IN DEFAULT
ATT_FILENAME VARCHAR2 IN DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
Argument Name Typ In/Out Defaultwert
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
ATTACHMENT VARCHAR2 IN
ATT_INLINE BOOLEAN IN DEFAULT
ATT_MIME_TYPE VARCHAR2 IN DEFAULT
ATT_FILENAME VARCHAR2 IN DEFAULT
2. In this section you can find several samples, showing the functionality of UTL_MAIL. Before attempting to run them be sure that you have created the UTL_MAIL package by running the following script under SYS schema.
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:
--or--
GRANT EXECUTE ON UTL_MAIL TO <user>;
In sample 2.2 and 2.3, attachment file cannot exceed the 32k size, because the attachment argument type. 2.1. Simple sample to test the SEND procedure:
BEGIN
UTL_MAIL.SEND(sender => 'xxx@oracle.com', recipients => 'xxx@oracle.com', cc => 'xxx@oracle.com', bcc => 'xxx@oracle.com', subject => 'Testmail', message => 'Hello');
EXCEPTION
WHEN OTHERS THEN
-- dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
/
SHOW ERRORS
exec send_email;
2.2. Sample sending emails with attachments. To run this example the directory object 'MYDIR' must be created.Also there must be a file named attach.txt in that directory.
CREATE OR REPLACE DIRECTORY OBJECT MYDIR as '/tmp'
grant read on directory MYDIR to public;
fHandle utl_file.file_type;
vTextOut varchar2(32000);
text varchar2(32000) := NULL;
BEGIN
fHandle := UTL_FILE.FOPEN('MYDIR','attach.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);
IF text IS NULL THEN
text := text || vTextOut;
ELSE
text := text || UTL_TCP.CRLF || vTextOut;
END IF;
-- 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 => 'Hello', attachment => text, ATT_INLINE => FALSE);
EXCEPTION
WHEN OTHERS THEN
-- dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
/
SHOW ERRORS
exec send_email_attach
With att_inline you can specify, whether the attachment is viewable inline with the message body or not. With the att_filename parameter you can give the attached file name. 2.3. Sample sending emails with RAW attachments.
create or replace directory BFILE_DIR as 'c:eispieleutl_mail';
grant read on directory BFILE_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 => 'Hello', attachment => resultraw, att_filename => 'clouds.jpg');
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
/