2010-10-01

utl_mail how to send emails from oracle

How to send emails with or without attachments using PL/SQL.



How to Send Emails Using UTL_MAIL[ID 269375.1]
----------------------------------
 
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 errorstack. 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 tu run them be sure that you have created the UTL_MAIL
   package by running the following script under SYS schema

     @?/rdbms/admin/utlmail.sql
     @?/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>;

   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:

===========================================================
   CREATE OR REPLACE PROCEDURE send_email AS
   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 UTL_FILE_DIR database parameter must points to the directory
'c:\beispiele\utl_mail', and there must be a file named attach.txt
in that directory.
      
===========================================================
   CREATE OR REPLACE PROCEDURE send_email_attach AS

   fHandle utl_file.file_type; 
   vTextOut varchar2(32000); 
   text varchar2(32000) := NULL;
  
   BEGIN  

   fHandle := UTL_FILE.FOPEN('c:\beispiele\utl_mail','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. 
   
===========================================================
   
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(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;
/

===========================================================


Please note:
It is not possible to connect to authenticating mail server(which require 
username and password) using the UTL_MAIL package.
Note 201639.1 shows the only way to connect to authenticating mail server 
using UTL_SMTP.

Niciun comentariu:

Trimiteți un comentariu