CODEWITHSUNDEEP
oracle-databaseemailplsql
Famy
Famy

Reputation: 113

body of the mail getting printed inside attachment file while sending mail through PL/SQL

My Requirement is to send data coming from oracle tables as an excel sheet attachment through mail to the customers. I am able to get the attachment but whatever I write as the body of mail, it goes inside the excel sheet.

Code:

CREATE OR REPLACE PROCEDURE trackekr(cursor1 IN OUT SYS_REFCURSOR)
AS
    v_connection  UTL_SMTP.connection;
     v_smtp           VARCHAR2(255):='mail.bbc.com';
    v_clob        CLOB := EMPTY_CLOB();
    v_len         INTEGER;
    v_index       INTEGER;
 c_mime_boundary CONSTANT VARCHAR2(256) := 'the boundary can be almost anything';
 headerLines                 CLOB := EMPTY_CLOB();



BEGIN
    OPEN cursor1 FOR
        SELECT COUNTRY_ID, START_DATE
   FROM Table WHERE OBJECT_NAME = 'XYZ';

   DBMS_LOB.CreateTemporary( v_clob, true );

   headerLines := 'COUNTRY_ID,START_DATE'|| UTL_TCP.crlf; --// create CSV header line
  DBMS_LOB.WriteAppend( v_clob, length(headerLines),  headerLines ); --// write it to CLOB
    --// start loop to add data lines to CSV

 FOR cursor1 in
        ( SELECT COUNTRY_ID, START_DATE
   FROM Table WHERE OBJECT_NAME = 'XYZ')

LOOP
v_clob :=
               v_clob
            || cursor1.COUNTRY_ID
            || ','
            || cursor1.START_DATE
            || UTL_TCP.crlf;
END LOOP;

    -- UTL

    v_connection := UTL_SMTP.open_connection(v_smtp, 25);  

    UTL_SMTP.helo(v_connection, v_smtp);
    UTL_SMTP.mail(v_connection, '[email protected]');
    UTL_SMTP.rcpt(v_connection, '[email protected]'); 
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection, 'From: ' || '[email protected]' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: ' || '[email protected]' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Subject: test subject' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);


    UTL_SMTP.write_data(
        v_connection,
        'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        'This is a multi-part message in MIME format.' || UTL_TCP.crlf
    );

    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

    -- Set up attachment header
    UTL_SMTP.write_data(
        v_connection,
        'Content-Disposition: attachment; filename="' || 'FIRSTFILE.csv' || '"' || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

    -- Write attachment contents

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index <= v_len
    LOOP
        UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

    -- End attachment
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

if DBMS_LOB.IsOpen( v_clob ) = 1 then
                DBMS_LOB.FreeTemporary( v_clob );
        end if;
    UTL_SMTP.close_data(v_connection);
    UTL_SMTP.quit(v_connection);
END;

Upvotes: 1

Views: 1593

Answers (1)

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

In a multi-part MIME message, body is a "part" by itself and needs it's own boundary.

In your original code, you find these lines:

UTL_SMTP.write_data(
    v_connection,
    'This is a multi-part message in MIME format.' || UTL_TCP.crlf
);

Just after those lines, you add this bit of code:

-- Body >>>
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Hello, this is the body.'||UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
-- <<< Body

Don't remove anything from your original code.

That way the body is within it's own boundary part, just like the csv file, but without a Content-Disposition: attachment header.

Upvotes: 2

Related Questions