Applies:
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.
@ $ ORACLE_HOME/rdbms/admin/utlmail. SQL @ $ ORACLE_HOME/rdbms/admin/prvtmail. PLB
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;
-- Or --
Grant execute on utl_mail to <user>;
Tip: With Database releases 11.1 or later, you will need to setup an ACL for any users that need access to the utl_mail package. See document 1209644.1 for additional information on the creation and setup.
In sample 2.2 and 2.3, attachment file cannot exceed the 32 K size, because the attachment argument type. 2.1. Simple sample to test the send procedure:
Create or replace procedure send_email
Begin
Utl_mail.send (sender => '[email protected]', recipients => '[email protected]', Cc => '[email protected]', BCC => '[email protected]', 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;
Create or replace procedure send_email_attach
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 => '[email protected]', recipients => '[email protected]', 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.
Set serveroutput on;
Create or replace directory bfile_dir as 'C: \ beispiele \ utl_mail ';
Grant read on directory bfile_dir to public;
Declare
FIL bfile;
File_len pls_integer;
Max_line_width pls_integer: = 54;
Buf raw (1, 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 unzip 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 => '[email protected]', recipients => '[email protected]', 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;
/
