UTL_SMTP: Send an Email to Multiple Recipients with Attachments from Oracle PL/SQL

Here i am giving an example to send an email to multiple recipients with attachments from Oracle PL/SQL. I took this as challenge and done this for my client.

Note: Blob (file) Stored in Oracle Directory.

Explanation:

The UTL_SMTP package was introduced in Oracle 8i and can be used to send emails from PL/SQL.

Email with Attachments:

Attaching a BLOB requires the binary data to be encoded and converted to text so it can be sent using UTL_SMTP.

Multiple Recipients:

When dealing with multiple recipients, the UTL_SMTP.RCPT procedure needs to be called for each recipient, whether they are a "TO", "CC" or "BCC".

The distinction between the types of recipient is made in the descriptions in the WRITE_DATA calls.

The following procedure accepts comma separated "TO", "CC" and "BCC" parameters. The "TO" is mandatory, but the others are optional. If present, they are processed appropriately by splitting the strings up using the string_api package.

Step 1: Create stored procedure as below,

CREATE OR REPLACE
 PROCEDURE send_mail ( 
        p_sender    VARCHAR2, 
        p_recipient VARCHAR2, 
        p_cc        VARCHAR2, 
        p_bcc       VARCHAR2, 
        p_subject   VARCHAR2, 
        p_filename  VARCHAR2, 
        p_text_msg CLOB, 
        p_attach_mime VARCHAR2 ) 
    IS 
      v_raw RAW(57); 
      v_length      INTEGER           := 0; 
      v_buffer_size INTEGER           := 57; 
      v_offset      INTEGER           := 1; 
      mailhost      VARCHAR2(64)      := '10.1.4.45';  -- Ask your SYSTEM ADMIN
      port          constant NUMBER(2):=25; 
      TIMEOUT       NUMBER            :=180; 
      mail_conn utl_smtp.connection; 
      p_blob BLOB; 
      temp_os_file BFILE; 
      ex         NUMBER; 
      l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*='; 
      l_step pls_integer      := 12000; -- make sure you set a multiple of 3 not higher than 24573 
    TYPE t_split_array 
  IS 
    TABLE OF VARCHAR2(4000); 

    FUNCTION split_text( 
        p_text      IN CLOB, 
        p_delimeter IN VARCHAR2 DEFAULT ',') 
      RETURN t_split_array 
    IS 
      l_array t_split_array := t_split_array(); 
      l_text CLOB           := p_text; 
      l_idx NUMBER; 
    BEGIN 
      l_array.DELETE; 
      IF l_text IS NULL THEN 
        raise_application_error(-20000, 'P_TEXT parameter cannot be NULL'); 
      END IF; 
      while l_text IS NOT NULL 
      loop 
        l_idx := instr(l_text, p_delimeter); 
        l_array.EXTEND; 
        IF l_idx                 > 0 THEN 
          l_array(l_array.LAST) := substr(l_text, 1, l_idx - 1); 
          l_text                := substr(l_text, l_idx    + 1); 
        ELSE 
          l_array(l_array.LAST) := l_text; 
          l_text                := NULL; 
        END IF; 
      END loop; 
      RETURN l_array; 
    END split_text; 

  PROCEDURE process_recipients( 
      mail_conn IN out utl_smtp.connection, 
      p_list    IN VARCHAR2) 
  AS 
    l_tab t_split_array; 
  BEGIN 
    IF trim(p_list) IS NOT NULL THEN 
      l_tab         := split_text(p_list); 
      FOR i IN 1 .. l_tab.count 
      loop 
        utl_smtp.rcpt(mail_conn, trim(l_tab(i))); 
      END loop; 
    END IF; 
  END; 
BEGIN 
  dbms_lob.createtemporary(p_blob,TRUE); 
  temp_os_file := bfilename ('APEXUAT',p_filename);  -- Get file from Oracle Directory
  ex           := dbms_lob.fileexists(temp_os_file); 
  IF ex         = 1 THEN 
    dbms_lob.fileopen(temp_os_file,dbms_lob.file_readonly); 
    dbms_lob.loadfromfile(p_blob,temp_os_file,dbms_lob.getlength(temp_os_file)); 
    dbms_lob.fileclose(temp_os_file); 
  END IF; 
  mail_conn := utl_smtp.open_connection(mailhost, port,TIMEOUT); 
  utl_smtp.helo(mail_conn, mailhost); 
  utl_smtp.mail(mail_conn, p_sender); 
  process_recipients(mail_conn, p_recipient); 
  process_recipients(mail_conn, p_cc); 
  process_recipients(mail_conn, p_bcc); 
  utl_smtp.open_data(mail_conn);
  utl_smtp.write_data(mail_conn,'From'||':'|| p_sender || utl_tcp.crlf);
  utl_smtp.write_data(mail_conn,'To'||':'|| p_recipient || utl_tcp.crlf); 
  IF trim(p_cc) IS NOT NULL THEN 
    utl_smtp.write_data(mail_conn, 'CC: ' || REPLACE(p_cc, ',', ';') || utl_tcp.crlf); 
  END IF; 
  IF trim(p_bcc) IS NOT NULL THEN 
    utl_smtp.write_data(mail_conn, 'Bcc: ' || REPLACE(p_bcc, ',', ';') || utl_tcp.crlf); 
  END IF; 
  utl_smtp.write_data(mail_conn,'Subject' ||':'|| p_subject || utl_tcp.crlf); 
  utl_smtp.write_data(mail_conn, 'Reply-To: ' || p_sender || utl_tcp.crlf); 
  utl_smtp.write_data(mail_conn, 'MIME-Version: 1.0' || utl_tcp.crlf); 
  utl_smtp.write_data(mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || utl_tcp.crlf || utl_tcp.crlf); 
  IF p_text_msg IS NOT NULL THEN 
    utl_smtp.write_data(mail_conn, '--' || l_boundary || utl_tcp.crlf); 
    utl_smtp.write_data(mail_conn, 'Content-type: text/html; charset="iso-8859-1"' || utl_tcp.crlf || utl_tcp.crlf); 
    utl_smtp.write_data(mail_conn, p_text_msg); 
    utl_smtp.write_data(mail_conn, utl_tcp.crlf || utl_tcp.crlf); 
  END IF; 
  utl_smtp.write_data( mail_conn, utl_tcp.crlf ); 
  v_length      := dbms_lob.getlength(p_blob); 
  IF p_filename IS NOT NULL THEN 
    utl_smtp.write_data(mail_conn, '--' || l_boundary || utl_tcp.crlf); 
    utl_smtp.write_data(mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_filename || '"' || utl_tcp.crlf); 
    utl_smtp.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf); 
    utl_smtp.write_data(mail_conn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf || utl_tcp.crlf); 
    while v_offset < v_length 
    loop 
      dbms_lob.READ( p_blob, v_buffer_size, v_offset, v_raw ); 
      utl_smtp.write_raw_data( mail_conn, utl_encode.base64_encode(v_raw) ); 
      utl_smtp.write_data( mail_conn, utl_tcp.crlf ); 
      v_offset := v_offset + v_buffer_size; 
    END loop while_loop; 
  utl_smtp.write_data(mail_conn, utl_tcp.crlf || utl_tcp.crlf); 
END IF; 
utl_smtp.write_data(mail_conn, '--' || l_boundary || '--' || utl_tcp.crlf); 
utl_smtp.write_data( mail_conn, utl_tcp.crlf ); 
utl_smtp.close_data(mail_conn); 
utl_smtp.quit(mail_conn); 
exception 
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN 
  utl_smtp.quit(mail_conn); 
  raise; 
WHEN others THEN 
  raise; 
END send_mail;

Step 2: Call the procedure send_mail. The code below shows how the procedure is called.

DECLARE
  l_from      VARCHAR2(100)  := 'xxx@yyy.com'; -- Required
  l_to        VARCHAR2(2000) := 'yyy@xxx.com,zzz@yyy.com'; -- Required
  l_cc        VARCHAR2(2000) := 'yyy@xxx.com,zzz@yyy.com'; -- Optional
  l_bcc       VARCHAR2(2000) := 'yyy@xxx.com,zzz@yyy.com'; -- Optional
  l_mime_type VARCHAR2 (100) := 'text/plain';
  l_body_html CLOB;
BEGIN
  l_body_html:= 'Please find attached file.';
 
  send_mail ( p_sender       => l_from,
                      p_recipient    => l_to,
                      p_cc              => l_cc,
                      p_bcc             => l_bcc,
                      p_subject        => 'Test Mail',
                      p_filename       => 'employees_report.xlsx', --  File must be there in Oracle Directory
                      p_text_msg        => l_body_html,
                      p_attach_mime  => l_mime_type
                     );
END;   

Output:

Reference:

• Email from Oracle PL/SQL (UTL_SMTP)