title: Email .txt file as an attachment through Oracle Stored Procedure
body: <p>I have a .txt file in which I have stored my data Semicolon-separated.
  The file is stored at backend in a <code>Clob</code>.</p>
  
  <p><strong>File:</strong></p>
  
  <p><code>ABCD;HEAD;       1.0;00204;18.05.2015;00000000;00000000000.00;18.05.2015</code></p>
  
  <p>I need to send this text file as an attachment via Stored Procedure in Oracle. </p>
  
  <p><strong>Problem:</strong></p>
  
  <p>I get this encoded file attached in the Email:</p>
  
  <p><code>4546554C3B484541443B20202020202020312E303B30303634323B31382E30352E323031353B30303030303030313B30303030303030303032302E30303B31382E30352E323031350A4546554C3B444154413B20202020202020312E303B4546554C3B303030543130303030303335303B313B31382E30352E323031353B303030303032302C3030302E30303B504B523B3132363435363135313231313B303235303533313030310D0A</code></p>
  
  <p><strong>Procedure:</strong></p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE SENDMAIL_ATT
  (
  MSG_FROM     VARCHAR2,
  MSG_TO       VARCHAR2,
  MSG_SUBJECT  VARCHAR2,
  MSG_TEXT     VARCHAR2,
  MSG_ATT      CLOB,
  ATT_FILENAME VARCHAR2
  ) IS
  
  V_MAILHOST VARCHAR2(50) := 'mail_server';
  V_PORT     NUMBER(2) := 25;
  V_HELO     VARCHAR2(50) := 'localhost';
  
  C    UTL_TCP.CONNECTION;
  RC   INTEGER;
  CRLF VARCHAR2(2) := CHR(13) || CHR(10);
  MESG VARCHAR2(32767);
  
  V_BUFFER_SIZE CONSTANT PLS_INTEGER := 4000;
  V_BUFFER_NEXT PLS_INTEGER := 4000;
  V_BODY_BUFFER VARCHAR2(8000 CHAR);
  BEGIN
  
  C := UTL_TCP.OPEN_CONNECTION(V_MAILHOST, V_PORT); -- open the SMTP port
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  RC := UTL_TCP.WRITE_LINE(C, 'HELO ' || V_HELO);
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  RC := UTL_TCP.WRITE_LINE(C, 'MAIL FROM: ' || MSG_FROM); ----- MAIL BOX SENDING THE EMAIL
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  RC := UTL_TCP.WRITE_LINE(C, 'RCPT TO: ' || MSG_TO); ----- MAIL BOX RECIEVING   THE EMAIL
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  RC := UTL_TCP.WRITE_LINE(C, 'DATA'); ----- EMAIL MESSAGE BODY START
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  RC := UTL_TCP.WRITE_LINE(C,'Date: ' ||
   TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss'));
    RC := UTL_TCP.WRITE_LINE(C, 'From: ' || MSG_FROM);
  RC := UTL_TCP.WRITE_LINE(C, 'MIME-Version: 1.0');
  RC := UTL_TCP.WRITE_LINE(C, 'To: ' || MSG_TO);
  RC := UTL_TCP.WRITE_LINE(C, 'Subject: ' || MSG_SUBJECT);
  RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
  RC := UTL_TCP.WRITE_LINE(C, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
  RC := UTL_TCP.WRITE_LINE(C); ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
  RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
  RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/html'); ----- 1ST BODY PART.    EMAIL TEXT MESSAGE
  RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer-Encoding: 7bit');
  RC := UTL_TCP.WRITE_LINE(C);
  RC := UTL_TCP.WRITE_LINE(C, MSG_TEXT); ----- TEXT OF EMAIL MESSAGE
  RC := UTL_TCP.WRITE_LINE(C);
  RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
  RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
  RC := UTL_TCP.WRITE_LINE(C, ' name="' || ATT_FILENAME || '"');
  RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer_Encoding: 7bit'); 
  RC := UTL_TCP.WRITE_LINE(C, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
  RC := UTL_TCP.WRITE_LINE(C, ' filename="' || ATT_FILENAME || '"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
  RC := UTL_TCP.WRITE_LINE(C);
  
  --CLOB 
  FOR I IN 0 .. FLOOR(DBMS_LOB.GETLENGTH(MSG_ATT) / V_BUFFER_SIZE)
  LOOP
  DBMS_LOB.READ(MSG_ATT,
  V_BUFFER_NEXT,
  I * V_BUFFER_SIZE + 1,
  V_BODY_BUFFER);
  
  RC := UTL_TCP.WRITE_TEXT(C, V_BODY_BUFFER);
  
  END LOOP;
  
  RC := UTL_TCP.WRITE_LINE(C);
  RC := UTL_TCP.WRITE_LINE(C, '.'); ----- EMAIL MESSAGE BODY END
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  RC := UTL_TCP.WRITE_LINE(C, 'QUIT'); ----- ENDS EMAIL TRANSACTION
  DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
  UTL_TCP.CLOSE_CONNECTION(C); ----- CLOSE SMTP PORT CONNECTION
  EXCEPTION
  WHEN OTHERS THEN
  RAISE;
  END SENDMAIL_ATT;
  </code></pre>
  
  <p><strong>Procedure Call:</strong></p>
  
  <pre><code>declare
   -- Local variables here
  l_clob   clob;
  fileName varchar2(100);
  begin
  
  SELECT file_name, file_content
  INTO fileName, l_clob
  FROM my_table
  WHERE ID = 141;
  
  SENDMAIL_ATT(MSG_FROM     =&gt; 'myemail@myDomain.com',
             MSG_TO       =&gt; 'myemail@myDomain.com',
             MSG_SUBJECT  =&gt; 'Test Email',
             MSG_TEXT     =&gt; 'Some Subject',
             MSG_ATT      =&gt; l_clob,
             ATT_FILENAME =&gt; fileName);
  
  end;
  </code></pre>