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

abjiang wrote:

>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
>
>

You must use JSQL or something other than PL/SQL to accomplish your goal. Go to http://asktom.oracle.com for examples.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)