Discussion on:

Sending blob attachments in e-mail with utl_smtp

J.Ja

Besides Steve McQueen's first starring role?

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

J.Ja

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.

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.

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,';') 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,';') 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 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;
/

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

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.

/* 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,';') 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,';') 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 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 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

Hello,

Thanks much. Works like a charm.

Sasi

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?

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

Got it .Thanks

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

Hi ,

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

Is there any issue with UTL_ENCODE.base64_encode

Please help me

Thanks in advance

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