Oracle UTL_SMTP: Send Mail with Attachment Example Using Oracle Wallet Authentication

Send a mail with Attachment using Oracle UTL_SMTP and Oracle Wallet Authentication example.

The following is the example of Oracle Wallet configuration information:

Wallet Path: 'file:/usr1/oracle/wallets/mywallet'
Wallet Password: YourPasword

Send Mail with Attachment using Oracle UTL_SMTP and Oracle Wallet

PL/SQL Stored Procedure:

CREATE OR REPLACE PROCEDURE Send_EMail(i_empno IN emp.empno%type,
                                                      i_Recipient        IN VARCHAR2) IS

  v_Host            CONSTANT VARCHAR2(100) := 'smtp.yourhost.com';
  v_Port            CONSTANT INTEGER := 587;
  v_Wallet_Path     CONSTANT VARCHAR2(100) := 'file:/usr1/oracle/wallets/mywallet';
  v_Wallet_Password CONSTANT VARCHAR2(100) := 'YourWalletPassword';
  v_Domain          CONSTANT VARCHAR2(100) := 'smtp.yourhost.com';
  v_Username        CONSTANT VARCHAR2(100) := 'UserName@example.com';
  v_Password        CONSTANT VARCHAR2(100) := 'YourPassword';
  v_Sender          CONSTANT VARCHAR2(100) := 'ExampleSender@example.com';
  v_Recipient       CONSTANT VARCHAR2(100) := i_Recipient;
  v_Subject          VARCHAR2(1000) := 'This is subject.';
  v_Body             VARCHAR2(1000) := 'This is the bodyt text.';
  l_Conn             Utl_Smtp.Connection;
  l_Reply            Utl_Smtp.Reply;
  l_Replies          Utl_Smtp.Replies;
  v_Len              NUMBER;
  v_Index            NUMBER;
  l_Boundary         VARCHAR2(50) := '----=*#foxinfotech#*=';
  v_Error_Message    VARCHAR2(1000);
  v_Filename         VARCHAR2(100);

/* Fetch the PDF (BLOB) from EMP_DOCS table */
  CURSOR Cur_empPdf IS
    SELECT pdf_blob Report
      FROM emp_docs
     WHERE empno = i_empno;

  l_Blob        BLOB;
 
BEGIN

  l_Reply := Utl_Smtp.Open_Connection(Host                          => v_Host,
                                      Port                          => v_Port,
                                      c                             => l_Conn,
                                      Wallet_Path                   => v_Wallet_Path,
                                      Wallet_Password               => v_Wallet_Password,
                                      Secure_Connection_Before_Smtp => FALSE);


  l_Replies := Utl_Smtp.Ehlo(l_Conn,
                             v_Domain);

  Utl_Smtp.Starttls(l_Conn);


  l_Replies := Utl_Smtp.Ehlo(l_Conn,
                             v_Domain);

  l_Reply := Utl_Smtp.Auth(l_Conn,
                           v_Username,
                           v_Password,
                           Utl_Smtp.All_Schemes);

  l_Reply := Utl_Smtp.Mail(l_Conn,
                           v_Sender);


  l_Reply := Utl_Smtp.Rcpt(l_Conn,
                           v_Recipient);

  l_Reply := Utl_Smtp.Open_Data(l_Conn);


  v_Filename := To_Char(SYSDATE,
                        'YYYYMMDDHH24MISS') || '.pdf';

  Utl_Smtp.Write_Data(l_Conn,
                      'From: ' || v_Sender || Utl_Tcp.Crlf);
  Utl_Smtp.Write_Data(l_Conn,
                      'To: ' || v_Recipient || Utl_Tcp.Crlf);
  Utl_Smtp.Write_Data(l_Conn,
                      'Subject: ' || v_Subject || Utl_Tcp.Crlf);
  Utl_Smtp.Write_Data(l_Conn,
                      'MIME-Version: 1.0' || Utl_Tcp.Crlf);
  Utl_Smtp.Write_Data(l_Conn,
                      'Content-Type: multipart/mixed; boundary="' ||
                      l_Boundary || '"' || Utl_Tcp.Crlf || Utl_Tcp.Crlf);

  IF v_Body IS NOT NULL THEN
    Utl_Smtp.Write_Data(l_Conn,
                        '--' || l_Boundary || Utl_Tcp.Crlf);
    Utl_Smtp.Write_Data(l_Conn,
                        'Content-Type: text/plain; charset="iso-8859-1"' ||
                        Utl_Tcp.Crlf || Utl_Tcp.Crlf);
  
    Utl_Smtp.Write_Data(l_Conn,
                        Utl_Tcp.Crlf || v_Body);
    Utl_Smtp.Write_Data(l_Conn,
                        Utl_Tcp.Crlf || Utl_Tcp.Crlf);
  END IF;

  Dbms_Lob.Createtemporary(Lob_Loc => l_Blob,
                           Cache   => TRUE,
                           Dur     => Dbms_Lob.Call);

  FOR c IN Cur_empPdf LOOP
  
    l_Blob := c.Report;

    Utl_Smtp.Write_Data(l_Conn,
                        '--' || l_Boundary || Utl_Tcp.Crlf);
    Utl_Smtp.Write_Data(l_Conn,
                        'Content-Type: ' || 'application/pdf' || '; name="' ||
                        c.Filename || '"' || Utl_Tcp.Crlf);
    Utl_Smtp.Write_Data(l_Conn,
                        'Content-Transfer-Encoding: base64' || Utl_Tcp.Crlf);
    Utl_Smtp.Write_Data(l_Conn,
                        'Content-Disposition: attachment; filename="' ||
                        v_Filename || '"' || Utl_Tcp.Crlf || Utl_Tcp.Crlf);
  
    FOR i IN 0 .. Trunc((Dbms_Lob.Getlength(l_Blob) - 1) / 12000) LOOP
      Utl_Smtp.Write_Data(l_Conn,
                          Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Dbms_Lob.Substr(l_Blob,
                                                                                            12000,
                                                                                            i *
                                                                                            12000 + 1))));
    END LOOP;
  
    Utl_Smtp.Write_Data(l_Conn,
                        Utl_Tcp.Crlf || Utl_Tcp.Crlf);
    Utl_Smtp.Write_Data(l_Conn,
                        '--' || l_Boundary || '--' || Utl_Tcp.Crlf);
    EXIT;
  END LOOP;

  l_Reply := Utl_Smtp.Close_Data(l_Conn);

  l_Reply := Utl_Smtp.Quit(l_Conn);

EXCEPTION
  WHEN Utl_Smtp.Transient_Error OR Utl_Smtp.Permanent_Error THEN
  
    BEGIN
      Utl_Smtp.Quit(l_Conn);
    EXCEPTION
      WHEN Utl_Smtp.Transient_Error OR Utl_Smtp.Permanent_Error THEN
        NULL;
    END;
  
    Raise_Application_Error(-20000,
                            'Failed to send mail due to the following error: ' ||
                            SQLERRM);
    
  WHEN OTHERS THEN
    RAISE;
END;
/