How to attach an existing file to the email in sql script?

From: abjiang <member35506_at_dbforums.com>
Date: Fri, 05 Sep 2003 16:52:47 -0400
Message-ID: <3336658.1062795167@dbforums.com>

Hi all,

I've been working on a sql script that sends out an email with an attachment. My script can send out email successfully. But I can't attach an existing file to it. I specified the file name in my sceript. But when I open the attached file aaa.txt in the email, it's blank. Does anyone know how to attach an existing file to the email? The following is my script.

Thanks!

CREATE OR REPLACE PROCEDURE SEND_MAIL (   msg_from varchar2 := 'name_at_host.com', ----- MAIL BOX   SENDING THE EMAIL   msg_to varchar2 := 'name_at_host.com', ----- MAIL BOX   RECIEVING THE EMAIL   msg_subject varchar2 := 'Output file TEST1', -----   EMAIL SUBJECT   msg_text varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',

  v_output1 varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS   TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.') IS

  c utl_tcp.connection;

  rc integer;

  crlf VARCHAR2(2):= CHR(13)||CHR(10);

  mesg VARCHAR2( 32767 );

BEGIN

  c := utl_tcp.open_connection(mailhost.com', 25);       ----- OPEN SMTP
     PORT CONNECTION

  rc := utl_tcp.write_line(c, 'HELO mailhost.com');       ----- PERFORMS

  HANDSHAKING WITH SMTP SERVER   dbms_output.put_line(utl_tcp.get_line(c, TRUE));

  rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18'); -----   PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION   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||' <'||msg_from||'>');

  rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');

  rc := utl_tcp.write_line(c, 'To: '||msg_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, '');                                   -----

  INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD   BE REMOVED.   rc := utl_tcp.write_line(c, '-------SECBOUND');

  rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); -----   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="Test.txt"');

  rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');

  rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); -----   INDICATES THAT THIS IS AN ATTACHMENT   rc := utl_tcp.write_line(c, ' filename="aaa.txt"'); -----   SUGGESTED FILE NAME FOR ATTACHMENT   rc := utl_tcp.write_line(c, '-------SECBOUND--');

  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_application_error(-20000, SQLERRM);

END; /

--
Posted via http://dbforums.com

Received on Fri Sep 05 2003 - 15:52:47 CDT