Software

Sending blob attachments in e-mail with utl_smtp


Last time, I showed how to send an HTML-formatted e-mail from an Oracle PL/SQL application using utl_smtp. I promised I would extend the package a little with a second method that allows you to send Binary Large OBject (blob) attachments in the e-mails. The most common application where I work is sending PDFs that are stored in the database to various recipients.

First, you have to add the method into the package spec so that other packages can use it:

  procedure send_blob (

p_sender varchar2,

p_recipient varchar2,

p_subject varchar2,

p_filename varchar2,

p_blob blob);

Here is the actual method you need to insert into the package body:

  procedure send_blob (

p_sender varchar2,

p_recipient varchar2,

p_subject varchar2,

p_filename varchar2,

p_blob blob) is

c utl_smtp.connection;

v_raw raw(57);

v_length integer := 0;

v_buffer_size integer := 57;

v_offset integer := 1;

begin

common(p_sender, p_recipient, p_subject, c);

utl_smtp.write_data( c, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);

utl_smtp.write_data( c, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );

utl_smtp.write_data( c, utl_tcp.crlf );

v_length := dbms_lob.getlength(p_blob);

<<while_loop>>

while v_offset < v_length loop

dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );

utl_smtp.write_raw_data( c, utl_encode.base64_encode(v_raw) );

utl_smtp.write_data( c, utl_tcp.crlf );

v_offset := v_offset + v_buffer_size;

end loop while_loop;

utl_smtp.write_data( c, utl_tcp.crlf );

utl_smtp.close_data( c );

utl_smtp.quit( c );

exception

when utl_smtp.transient_error or utl_smtp.permanent_error then

utl_smtp.quit( c );

raise;

when others then

raise;

end send_blob;

Notes about the code

You have to pass a filename along with the actual blob. You also need to make sure it has the right extension so that the e-mail client on the other end will be able to open it up properly. That is, if the blob is a PDF, then send a filename like abc.pdf.

You'll notice that this method doesn't send a body. I'm trying to keep it simple to focus on the actual sending of the blob. If your application requires it, you should be able to incorporate code from the other method to include an e-mail body as well.

The weird while loop in the method

    <<while_loop>>

while v_offset < v_length loop

dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );

utl_smtp.write_raw_data( c, utl_encode.base64_encode(v_raw) );

utl_smtp.write_data( c, utl_tcp.crlf );

v_offset := v_offset + v_buffer_size;

end loop while_loop;

The buffer size must be 57 for utl_encode to work, which is why we loop thru the blob and chunk it into 57-byte pieces. This process converts the blob into a raw binary format and then sends the 57 bytes into the e-mail stream. It also adds an end-of-line after each 57-byte chunk.

Once you have the basics figured out in utl_smtp, it's not a huge deal to include attachments.

20 comments
han_helmy
han_helmy

I see many one have the code run but I have I problem please help while I run the package body I got error in === while v_offset dbms_lob.CALL ); l_bfile := BFILENAME ( l_dbdir, p_file ); dbms_lob.fileopen ( l_bfile, dbms_lob.file_readonly ); dbms_lob.loadfromfile ( l_data , l_bfile , dbms_lob.getlength ( l_bfile )); dbms_lob.fileclose ( l_bfile ); RETURN l_data; EXCEPTION WHEN OTHERS THEN dbms_output.put_line ( 'Error during GET_LOCAL_BINARY_DATA :: ' || SQLCODE || ' - ' || SQLERRM ); dbms_lob.fileclose ( l_bfile ); RAISE; END get_local_binary_data; something is missed in this line while v_offset dbms_lob.CALL ); end CARTIER_MAIL_TOOL; =

madhunaidu4u
madhunaidu4u

Is there any issue with UTL_ENCODE.base64_encode Please help me Thanks in advance

madhunaidu4u
madhunaidu4u

Hi , while iam sending the email its working for some domains and for some domains the attachment going as an inline view

louiskhel
louiskhel

HI, Im able to send mails with utl_smtp, but I need to send emails with attached files from my HD, any help?

Ananthraju
Ananthraju

Hi its realy very helpful... i was not able to send email with ataachment i got so many other post from net but thoses are so complicated and i could not follw.. in this post you have given clearly how to make attachment form blob... thank you very much

socratec
socratec

/* SCRIPT TO EXECUTE PACKAGE */ DECLARE p_smtp_server varchar2(50) := 'mail.cartierkitchens.com'; p_smtp_port pls_integer := 1025; p_from varchar2(50) := 'andreas@socratec.ca'; p_to varchar2(50) := 'andreas@socratec.ca'; p_subject varchar2(512) := 'Purchase Order'; p_message varchar2(512) := 'Please find attached your purchase order'; p_priority number := 3; -- Input Directory as specified in create directory p_dirname CONSTANT VARCHAR2(30) := 'APPS_PUBDIR'; -- Input File which is read word by word p_filename CONSTANT VARCHAR2(30) := 'o535528.pdf'; -- 'o535528.pdf'; -- Attachment Mail ID t_mail_id PLS_INTEGER; BEGIN CARTIER_MAIL_TOOL.load_file_to_db (p_dirname, p_filename, t_mail_id); CARTIER_MAIL_TOOL.sendmail ( p_smtp_server , p_smtp_port , p_from , p_to , p_subject , p_message , NULL , t_mail_id ); END; create or replace package CARTIER_MAIL_TOOL is -- Author : SOCRATEC -- Created : 3/10/2010 2:29:37 PM -- Purpose : sending email with attachments through smtp package PROCEDURE SENDMAIL ( smtp_svr IN varchar2, smtp_port pls_integer, from_name IN varchar2, to_name IN varchar2, subject IN varchar2, message IN varchar2, cc_name IN varchar2, mail_id_in IN NUMBER ); PROCEDURE load_file_to_db ( p_dir_name IN varchar2, p_file_name IN varchar2, p_recid out number); FUNCTION get_local_binary_data ( p_dir IN VARCHAR2, p_file IN VARCHAR2 ) RETURN BLOB; end CARTIER_MAIL_TOOL; create or replace package body CARTIER_MAIL_TOOL is ----------------------- Customizable Section ----------------------- -- Customize the SMTP host, port and your domain name below. -- Arguments are passed to procedure smtp_host VARCHAR2(256) := 'mail.socratec.ca'; smtp_port PLS_INTEGER := 1025; smtp_domain VARCHAR2(256) := 'socratec.ca'; -- Customize the signature that will appear in the email's MIME header. -- Useful for versioning. MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP'; --------------------- End Customizable Section --------------------- PROCEDURE SENDMAIL ( smtp_svr varchar2, smtp_port pls_integer, from_name varchar2, to_name varchar2, subject varchar2, message varchar2, cc_name varchar2, mail_id_in NUMBER ) as conn utl_smtp.connection; v_smtp_server_port smtp_port; -- PLS_INTEGER := 1025; msg varchar2(32767); v_reply utl_smtp.reply; v_reply_code varchar2(100); v_temp VARCHAR2(4000) := ''; v_name VARCHAR2(4000) := ''; v_pos NUMBER := 1; v_raw raw(57); v_length integer := 0; v_buffer_size integer := 57; v_offset integer := 1; --mail_attachments table is --mail_id NUMBER not null Primary Key --seq_no NUMBER not null Primary Key --filename varchar2(255) not null --filedata blob null allowed -- Cursor attachments (mail_id_in mail_attachment.mail_id%type) IS Cursor attachments (mail_id in xxck_mail_blob_attachment.blob_recid%type) IS SELECT filename, temp_file filedata FROM xxck_mail_blob_attachment -- mail_attachment WHERE blob_recid = mail_id; -- mail_id = mail_id_in; begin v_reply := utl_smtp.open_connection( smtp_svr, smtp_port, conn ); v_reply := utl_smtp.helo( conn, smtp_svr ); v_reply := utl_smtp.mail( conn, from_name ); -- logic to send e-mail to multiple To'd users separated by ';' v_temp := replace(to_name,' ',''); if(instr(v_temp,';') = 0) or (instr(v_temp,';') < length(v_temp)) then v_temp := v_temp||';'; end if; v_pos := 1; while(instr(v_temp,';',v_pos) > 0) loop v_name := substr(v_temp, v_pos, instr(substr(v_temp, v_pos),';')-1); v_pos := v_pos + instr(substr(v_temp, v_pos),';'); v_reply := utl_smtp.rcpt(conn, v_name); end loop; -- logic to send e-mail to Cc'd users separated by ';' v_temp := replace(cc_name,' ',''); if(instr(v_temp,';') = 0) or (instr(v_temp,';') < length(v_temp)) then v_temp := v_temp||';'; end if; v_pos := 1; while(instr(v_temp,';',v_pos) > 0) loop v_name := substr(v_temp, v_pos, instr(substr(v_temp, v_pos),';')-1); v_pos := v_pos + instr(substr(v_temp, v_pos),';'); v_reply := utl_smtp.rcpt(conn, v_name); end loop; v_reply_code := to_char(v_reply.code); if v_reply.code 250 then utl_smtp.quit( conn ); return; end if; msg := 'Return-Path: '||from_name|| utl_tcp.CRLF || 'Sent: '||TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' )|| utl_tcp.CRLF || 'From: '||from_name|| utl_tcp.CRLF || 'Subject: '|| subject || utl_tcp.CRLF || 'To: '|| to_name || utl_tcp.CRLF || 'Cc: '|| cc_name || utl_tcp.CRLF || 'MIME-Version: 1.0'|| utl_tcp.CRLF || -- Use MIME mail standard 'Content-Type: multipart/mixed; boundary="MIME.Bound"'|| utl_tcp.CRLF || --MIME.Bound really should be a randomly generated string utl_tcp.CRLF || '--MIME.Bound' || utl_tcp.CRLF || 'Content-Type: text/plain; '|| utl_tcp.CRLF || 'Content-Disposition: inline;' || utl_tcp.CRLF || 'Content-Transfer_Encoding: 7bit'|| utl_tcp.CRLF || utl_tcp.CRLF || message || utl_tcp.CRLF || utl_tcp.CRLF; utl_smtp.open_data(conn); utl_smtp.write_data( conn, msg ); if mail_id_in > 0 then For curs_rec in attachments(mail_id_in) Loop BEGIN utl_smtp.write_data( conn, '--MIME.Bound' || utl_tcp.CRLF); utl_smtp.write_data( conn, 'Content-Type: application/octet-stream; name="' || curs_rec.filename || '"' || utl_tcp.crlf); utl_smtp.write_data( conn, 'Content-Disposition: attachment; filename="' || curs_rec.filename || '"' || utl_tcp.crlf); utl_smtp.write_data( conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf ); utl_smtp.write_data( conn, utl_tcp.crlf ); v_length := dbms_lob.getlength(curs_rec.filedata); --reset the offset v_offset := 1; v_buffer_size := 57; while v_offset < v_length loop dbms_lob.read( curs_rec.filedata, v_buffer_size, v_offset, v_raw ); utl_smtp.write_raw_data( conn, utl_encode.base64_encode(v_raw) ); utl_smtp.write_data( conn, utl_tcp.crlf ); v_offset := v_offset + v_buffer_size; end loop; utl_smtp.write_data( conn, utl_tcp.crlf ); exception when utl_smtp.transient_error or utl_smtp.permanent_error then utl_smtp.quit( conn ); raise; when others then raise; END; End Loop; /* delete from mail_attachment where mail_id = mail_id_in; commit; */ end if; utl_smtp.write_data( conn, '--MIME.Bound--'); -- End MIME mail utl_smtp.write_data( conn, utl_tcp.crlf ); utl_smtp.close_data( conn ); utl_smtp.quit( conn ); end sendmail; PROCEDURE load_file_to_db (p_dir_name varchar2, p_file_name varchar2, p_recid out number) IS LOBD BLOB; FILS BFILE; AMT INT; v_Recid number(16); PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- get the record id for the blob select xxck_mail_blob_id_s.nextval into v_Recid from dual; -- load html file to database INSERT INTO XXCK_MAIL_BLOB_ATTACHMENT(temp_file, filename, blob_recid) -- this your table that you will save the blob file to use it after that VALUES(EMPTY_BLOB(), p_file_name, v_Recid) RETURNING Temp_FILE INTO LOBD; FILS:=BFILENAME(p_dir_name, p_file_name); DBMS_LOB.FILEOPEN(FILS, DBMS_LOB.FILE_READONLY); AMT:= DBMS_LOB.GETLENGTH(FILS); DBMS_LOB.LOADFROMFILE(LOBD, FILS, AMT); -- LOAD THE FILE directy to the data base DBMS_LOB.FILECLOSE(FILS); COMMIT; p_Recid:=v_recid ; end load_file_to_db; FUNCTION get_local_binary_data ( p_dir IN VARCHAR2 , p_file IN VARCHAR2 ) RETURN BLOB IS -- -------------------------------------------------------------------------- l_bfile BFILE; l_data BLOB; l_dbdir VARCHAR2 ( 100 ) := p_dir; BEGIN dbms_lob.createtemporary ( lob_loc => l_data , CACHE => TRUE , dur => dbms_lob.CALL ); l_bfile := BFILENAME ( l_dbdir, p_file ); dbms_lob.fileopen ( l_bfile, dbms_lob.file_readonly ); dbms_lob.loadfromfile ( l_data , l_bfile , dbms_lob.getlength ( l_bfile )); dbms_lob.fileclose ( l_bfile ); RETURN l_data; EXCEPTION WHEN OTHERS THEN dbms_output.put_line ( 'Error during GET_LOCAL_BINARY_DATA :: ' || SQLCODE || ' - ' || SQLERRM ); dbms_lob.fileclose ( l_bfile ); RAISE; END get_local_binary_data; end CARTIER_MAIL_TOOL; For a couple of days, I checked many programs to implement utl_smtp and I compiled this program together, it really works. The blob or blobs are loaded into a temporary table and the fetch from it to passed it to the mail utl_smtp.write_data I generated pdf files from Oracle Applications modules and are now able to send them as attachments to customers. P.S. I thank everybody for all written codes regards to make this possible. Andreas Mueller

harrisnunna
harrisnunna

Hi I tried including this code to attachment of BLOB data, but I am getting the below error... Cause: FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06512: at "APPS.XXGOPNG_PGAS_SITE_STATUS", line 200 ORA-06512: at line 1 Could you please help to rectify this error. Thanks in advance. Harish.

saikat.sinha
saikat.sinha

Hi i am having a lot of problems in sending the body of the email along with the blog attachment, the body is not getting displayed in the email, please provide the code for adding body to the above email....without body i can send the attachment properly....no problem in that.....please reply soon i require it very urgently thanks saikat

luisivars
luisivars

Hi, i'm using a package based on yours but i have 2 questions. How do i integrate body text in msg allong with the attach and how can i send more than one file in attach? Thank you in advance and congratulations for this nice and usefull package. Regards.

jsanju
jsanju

thanks for the code. it was very useful . got it running within a few minutes. had a question however. I have to run a report and get the pdf file from the apps server and then i've to retrieve the file and attach it to an email. How do i retrieve the pdf from the apps server ? thanks in advance

Factorizador
Factorizador

Hi, We were seeing your code, but we don't know where the FUNCTION get_local_binary_data is been called. Where do I have to call it? Can you guide us?

sasiram
sasiram

Hello, Thanks much. Works like a charm. Sasi

RexWorld
RexWorld

Sorry, I've never had to deal with multiple attachments. THe only solution I would think of is maybe ZIPping up all your files into one blob and then attach that single blob.

RexWorld
RexWorld

Man this is seriously going to date how old I am. When I was a kid the local public library had a summer reading program. I think you had to read five books or something like that. Anyway, one summer the prize was that you got a free pass to go see an old movie at the local discount theater. Yeah, not exactly a big deal but when you're a poor kid it was kinda cool. The movie they showed: The Blob. I'm not old enough that this was the first run of the movie, it was already an oldies classic by the time I saw it. But this was back before VCR's and cable TV, so none of us kids had ever seen it before. I could not eat strawberry jelly for years after seeing that film.

Justin James
Justin James

In other words, a chunk of data that should not be treated as text (that is a CLOB). :) J.Ja

jerrykimmel
jerrykimmel

Multiple Attachments with Body text If you get the multipart/mixed MIME CRLFs just right (this is probably spelled out in the MIME specs which I DID NOT read fully) then you can send a message body as well as multiple attachments. This code does not do any error checking - use at your own risk. Better yet, beef up the error checking and repost better code. I'd also like to see an implementation where the MIME.Bound is set randomly. Also the multi address handling could be better (different separators would be nice) and BCC is not used here. FYI - the calling code first inserts the attachments into the mail_attachments table and passes the mail_id (just a sequence number) to this SENDMAIL proc. This proc uses the cursor to loop through the attachments and add them to the SMTP message. CREATE OR REPLACE PROCEDURE SENDMAIL ( smtp_svr varchar2, from_name varchar2, to_name varchar2, subject varchar2, message varchar2, cc_name varchar2, mail_id_in NUMBER ) as conn utl_smtp.connection; v_smtp_server_port PLS_INTEGER := 25; msg varchar2(32767); v_reply utl_smtp.reply; v_reply_code varchar2(100); v_temp VARCHAR2(4000) := ''; v_name VARCHAR2(4000) := ''; v_pos NUMBER := 1; v_raw raw(57); v_length integer := 0; v_buffer_size integer := 57; v_offset integer := 1; --mail_attachments table is --mail_id NUMBER not null Primary Key --seq_no NUMBER not null Primary Key --filename varchar2(255) not null --filedata blob null allowed Cursor attachments (mail_id_in mail_attachment.mail_id%type) IS SELECT filename, filedata FROM mail_attachment WHERE mail_id = mail_id_in; begin v_reply := utl_smtp.open_connection( smtp_svr, 25, conn ); v_reply := utl_smtp.helo( conn, smtp_svr ); v_reply := utl_smtp.mail( conn, from_name ); -- logic to send e-mail to multiple To'd users separated by ';' v_temp := replace(to_name,' ',''); if(instr(v_temp,';') = 0) or (instr(v_temp,';') < length(v_temp)) then v_temp := v_temp||';'; end if; v_pos := 1; while(instr(v_temp,';',v_pos) > 0) loop v_name := substr(v_temp, v_pos, instr(substr(v_temp, v_pos),';')-1); v_pos := v_pos + instr(substr(v_temp, v_pos),';'); v_reply := utl_smtp.rcpt(conn, v_name); end loop; -- logic to send e-mail to Cc'd users separated by ';' v_temp := replace(cc_name,' ',''); if(instr(v_temp,';') = 0) or (instr(v_temp,';') < length(v_temp)) then v_temp := v_temp||';'; end if; v_pos := 1; while(instr(v_temp,';',v_pos) > 0) loop v_name := substr(v_temp, v_pos, instr(substr(v_temp, v_pos),';')-1); v_pos := v_pos + instr(substr(v_temp, v_pos),';'); v_reply := utl_smtp.rcpt(conn, v_name); end loop; v_reply_code := to_char(v_reply.code); if v_reply.code 250 then utl_smtp.quit( conn ); return; end if; msg := 'Return-Path: '||from_name|| utl_tcp.CRLF || 'Sent: '||TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' )|| utl_tcp.CRLF || 'From: '||from_name|| utl_tcp.CRLF || 'Subject: '|| subject || utl_tcp.CRLF || 'To: '|| to_name || utl_tcp.CRLF || 'Cc: '|| cc_name || utl_tcp.CRLF || 'MIME-Version: 1.0'|| utl_tcp.CRLF || -- Use MIME mail standard 'Content-Type: multipart/mixed; boundary="MIME.Bound"'|| utl_tcp.CRLF || --MIME.Bound really should be a randomly generated string utl_tcp.CRLF || '--MIME.Bound' || utl_tcp.CRLF || 'Content-Type: text/plain; '|| utl_tcp.CRLF || 'Content-Disposition: inline;' || utl_tcp.CRLF || 'Content-Transfer_Encoding: 7bit'|| utl_tcp.CRLF || utl_tcp.CRLF || message || utl_tcp.CRLF || utl_tcp.CRLF; utl_smtp.open_data(conn); utl_smtp.write_data( conn, msg ); if mail_id_in > 0 then For curs_rec in attachments(mail_id_in) Loop BEGIN utl_smtp.write_data( conn, '--MIME.Bound' || utl_tcp.CRLF); utl_smtp.write_data( conn, 'Content-Type: application/octet-stream; name="' || curs_rec.filename || '"' || utl_tcp.crlf); utl_smtp.write_data( conn, 'Content-Disposition: attachment; filename="' || curs_rec.filename || '"' || utl_tcp.crlf); utl_smtp.write_data( conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf ); utl_smtp.write_data( conn, utl_tcp.crlf ); v_length := dbms_lob.getlength(curs_rec.filedata); --reset the offset v_offset := 1; while v_offset < v_length loop dbms_lob.read( curs_rec.filedata, v_buffer_size, v_offset, v_raw ); utl_smtp.write_raw_data( conn, utl_encode.base64_encode(v_raw) ); utl_smtp.write_data( conn, utl_tcp.crlf ); v_offset := v_offset + v_buffer_size; end loop; utl_smtp.write_data( conn, utl_tcp.crlf ); exception when utl_smtp.transient_error or utl_smtp.permanent_error then utl_smtp.quit( conn ); raise; when others then raise; END; End Loop; delete from mail_attachment where mail_id = mail_id_in; commit; end if; utl_smtp.write_data( conn, '--MIME.Bound--'); -- End MIME mail utl_smtp.write_data( conn, utl_tcp.crlf ); utl_smtp.close_data( conn ); utl_smtp.quit( conn ); end; /

bushrodthomas
bushrodthomas

Thanks for posting this very useful code Jerry! I found one small problem-- v_buffer_size apparently gets set in dbms_lob.read to the actual # (of bytes?) read, so on the last read for an blob, the buffer size can be shrunk for subsequent attachments. In my testing, this was causing all but the first attachment to be mangled. If you set v_buffer_size back to 57 when reseting v_offset to 1, the problem disappears.