Oracle Procedure to send html email with multiple attachment [ blob]

create or replace TYPE attach_info IS OBJECT (
        value         VARCHAR2(100),
        mimetype      VARCHAR2(100),
        content       BLOB
    );
/

create or replace TYPE array_attachments IS TABLE OF attach_info;
/

PROCEDURE HTML_EMAIL_MUlTI_BLOBS (
/* TO SEND HTML OR PLAN TEXT EMAIL WITH ATTACHMENTS (BLOB)
-- TWO TYPES SHOULD BE IN DATABASE FOR THIS PROCEDURE WHICH ARE FOLLOWING:
--   create or replace TYPE attach_info IS OBJECT (
--        value         VARCHAR2(100),
--        mimetype      VARCHAR2(100),
--        content       BLOB
--    );
-- create or replace TYPE array_attachments IS TABLE OF attach_info;
*/
                                       p_to          IN VARCHAR2,
                                       p_cc          IN VARCHAR2 DEFAULT NULL,
                                       p_from        IN VARCHAR2,
                                       p_subject     IN VARCHAR2,
                                       p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                       p_html_msg    IN VARCHAR2 DEFAULT NULL,
                                       attachments   IN OUT array_attachments,
                                       p_smtp_host   IN VARCHAR2,
                                       p_smtp_port   IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  IF TRIM(p_cc) IS NOT NULL THEN
    UTL_SMTP.rcpt(l_mail_conn, p_cc);
  END IF;
  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  IF TRIM(p_cc) IS NOT NULL THEN    
      UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('CC: ' || REPLACE(p_cc, ',', ';') || UTL_TCP.crlf));
  END IF;
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  -- check plain text message and set
  IF p_text_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

 -- check html message and set
 IF p_html_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_html_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  -- Attachment Part
  IF attachments IS NOT NULL THEN
    FOR i IN attachments.FIRST .. attachments.LAST
    LOOP
    -- Attach info
        UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('--' || l_boundary || UTL_TCP.crlf));
        UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).mimetype
                            || ' name="'|| attachments(i).value || '"' || UTL_TCP.crlf));
        UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
        UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
                            || attachments(i).value || '"' || UTL_TCP.crlf || UTL_TCP.crlf));

    -- Attach body
      FOR j IN 0 .. TRUNC((DBMS_LOB.getlength(attachments(i).content) - 1 )/l_step) LOOP
        UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(attachments(i).content, l_step, j * l_step + 1))));
      END LOOP;

         UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
    END LOOP;
  END IF;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

/


How to initializing attachments:

declare:
attachments         array_attachments;

assign:
attachments := array_attachments();



attachments.extend(2);

attachments(1):= attach_info(null,null,null); /*extend it and initialize attach_info object and only then assign attachments(1).value, attachments(1).mimetype, attachments(1).content https://community.oracle.com/message/10085562*/


      SELECT oa0.VALUE||'.'||oa19.value,
            mty.mimetype,
            o.CONTENT
        INTO attachments(1).value,
             attachments(1).mimetype,
             attachments(1).content
        FROM sdms.OBJECTS o
        JOIN sdms.OBJECTATTRIBUTES oa0 ON (o.ID = oa0.OBJECT_ID AND oa0.ATTRIBUTEDESCRIPTION_ID = 0) -- FileName
        JOIN sdms.OBJECTATTRIBUTES oa19 ON (o.ID = oa19.OBJECT_ID AND oa19.ATTRIBUTEDESCRIPTION_ID = 19), -- FileExtension
        sdms.t_mime_types mty
        WHERE o.mime_id=mty.id AND o.id=9124;


Calling procedure:

HTML_EMAIL_MUlTI_BLOBS(v_to, v_cc, v_from, v_subject, null, cMessage, attachments, v_smtp_host);