Home » SQL & PL/SQL » SQL & PL/SQL » Sending UTL_SMTP mail with jpg picture as header inside an e-mail (11.2.0.3.0)
| Sending UTL_SMTP mail with jpg picture as header inside an e-mail [message #597673] |
Mon, 07 October 2013 09:02  |
sivakumar121
Messages: 29
Registered: April 2009
|
Junior Member |
|
|
Hi -
I have to embed a picture as a header with an e-mail content for my work, I google'd and tested with different options but I failed. (The JPG is stored in a table as a blog object)
When I tried with the below code the JPG file is e-mailed as an attachment with an e-mail content, I want it to be displayed inside an e-mail.
I tried att_inline: TRUE option as well - still not working.
Any help on this is appreciated.
Here is my code I tested and worked as an attachment.
DECLARE
/*LOB operation related varriables */
v_src_loc BLOB :=EMPTY_BLOB;
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
l_blob_len INTEGER;
v_amount INTEGER;
crlf varchar2(2) := chr(13)||chr(10);
/*UTL_SMTP related varriavles. */
v_connection_handle UTL_SMTP.CONNECTION;
v_from_email_address VARCHAR2(50) := 'a.test@abc.com';
v_to_email_address VARCHAR2(50) := 'b.test@abc.com';
v_smtp_host VARCHAR2(50) := 'abc.ab.abc.com'; --My mail server, replace it with yours.
v_subject VARCHAR2(30) := 'Your Test Mail';
l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';
/* This send_header procedure is written in the documentation */
PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection_handle,
pi_name || ': ' || pi_header || crlf);
END;
BEGIN
/*Preparing the LOB from table for attachment. */
SELECT PIC
INTO l_blob
FROM TEMP_MAIL_PIC_BLOB
WHERE PIC_ID = 1;
--DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
--DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
--v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
--DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
l_blob_len := DBMS_LOB.getlength(l_blob);
/*UTL_SMTP related coding. */
v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
UTL_SMTP.OPEN_DATA(v_connection_handle);
send_header('From', '"Sender" <' || v_from_email_address || '>');
send_header('To', '"Recipient" <' || v_to_email_address || '>');
send_header('Subject', v_subject);
--MIME header.
UTL_SMTP.WRITE_DATA(v_connection_handle,
'MIME-Version: 1.0' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: multipart/mixed; ' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
-- Mail Body
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: text/plain;' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
-- Mail Attachment
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: application/octet-stream' ||
crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Disposition: attachment; ' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' filename="' || 'Waterlilies.jpg' || '"' || --My filename
crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: base64' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
/* Writing the BLOL in chunks */
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data(v_connection_handle,
UTL_ENCODE.BASE64_ENCODE(l_buffer));
UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
-- Close Email
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || '--' || crlf);
UTL_SMTP.WRITE_DATA(v_connection_handle,
crlf || '.' || crlf);
UTL_SMTP.CLOSE_DATA(v_connection_handle);
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
--DBMS_LOB.FILECLOSE(v_src_loc);
EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
--DBMS_LOB.FILECLOSE(v_src_loc);
RAISE;
END;
/
|
|
|
|
|
|
|
|
| Re: Sending UTL_SMTP mail with jpg picture as header inside an e-mail [message #598217 is a reply to message #597754] |
Fri, 11 October 2013 09:09  |
sivakumar121
Messages: 29
Registered: April 2009
|
Junior Member |
|
|
Hi -
The link helped me to do some more research on this - I can even see the jpg as an attachment.
Still cannot able to resolve this - In an email still picture is displaying as X mark -
Here is my code - Can some one help me on resolving it -
DECLARE
/*LOB operation related varriables */
v_src_loc BLOB :=EMPTY_BLOB;
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
l_blob_len INTEGER;
v_amount INTEGER;
l_html varchar2(2000);
/*UTL_SMTP related varriavles. */
v_connection_handle UTL_SMTP.CONNECTION;
v_from_email_address VARCHAR2(50) := 'From.test@abc.com';
v_to_email_address VARCHAR2(50) := 'To.test@abc.com';
v_smtp_host VARCHAR2(50) := 'xxx.xxxx.xxxx.xxx'; --My mail server, replace it with yours.
v_subject VARCHAR2(30) := 'Your Test Mail';
l_message VARCHAR2(32767) := '<html>
<body>
Test HTML with Embedded Image-chk latest
<p>And here it is:</p>
<img src="cid:Waterlilies.jpg@01CBB95B.9A1FD110" >
<p>The end.</p>
</body>
</html>
';
/* This send_header procedure is written in the documentation */
PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection_handle,
pi_name || ': ' || pi_header || UTL_TCP.CRLF);
END;
BEGIN
/*Preparing the LOB from table for attachment. */
SELECT PIC
INTO l_blob
FROM TEMP_MAIL_PIC_BLOB
WHERE PIC_ID = 1;
--DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
--DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
--v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
--DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
l_blob_len := DBMS_LOB.getlength(l_blob);
/*UTL_SMTP related coding. */
v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
UTL_SMTP.OPEN_DATA(v_connection_handle);
send_header('From', '"Sender" <' || v_from_email_address || '>');
send_header('To', '"Recipient" <' || v_to_email_address || '>');
send_header('Subject', v_subject);
--MIME header.
UTL_SMTP.WRITE_DATA(v_connection_handle,
'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: multipart/related; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
-- Mail Body
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: text/html;' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
-- Mail Attachment
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Disposition: inline; filename="Waterlilies.jpg"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: image/jpg; name="Waterlilies.jpg"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-ID: <Waterlilies.jpg@01CBB95B.9A1FD110>; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.write_data(v_connection_handle, 'Content-Description: Waterlilies.jpg' || UTL_TCP.CRLF);
UTL_SMTP.write_data(v_connection_handle, 'Content-Location: Waterlilies.jpg' || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
/* Writing the BLOL in chunks */
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data(v_connection_handle,
UTL_ENCODE.BASE64_ENCODE(l_buffer));
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
-- Close Email
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA(v_connection_handle);
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
-- DBMS_LOB.FILECLOSE(v_src_loc);
EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
-- DBMS_LOB.FILECLOSE(v_src_loc);
RAISE;
END;
/
|
|
|
|
| Re: Sending UTL_SMTP mail with jpg picture as header inside an e-mail [message #609215 is a reply to message #598217] |
Tue, 04 March 2014 01:43 |
sivakumar121
Messages: 29
Registered: April 2009
|
Junior Member |
|
|
Back on this topic again.
We could able to send e-mails to huge population thru UNIX/Oracle (with out images) and good so far.
Now the requirement on same topic which I was working earlier and could not able to achieve o/p
Embed image inside the e-mail (instead of attachment)
Was trying different options since two days and no luck.
Can this be doable through Oracle?
Any help on this would be really great.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sending UTL_SMTP mail with jpg picture as header inside an e-mail [message #609359 is a reply to message #609356] |
Thu, 06 March 2014 00:25 |
sivakumar121
Messages: 29
Registered: April 2009
|
Junior Member |
|
|
Thank you - I tried the same and it really worked.
The below code - it is perfectly working in Outlook 2007
Not in the older versions of outlook I have and also not in Black Berry. It again displays Image as attachment, Any idea if I have to change mime types - tried different types - no luck.
DECLARE
conn utl_smtp.connection;
BOUNDARY VARCHAR2 (256) := '-----090303020209010600070908';
i pls_integer;
len pls_integer;
buff_size pls_integer := 57;
l_raw raw(57);
p_image blob;
MailServer VARCHAR2(50) := 'xxxxxx.xxxxx.xxxxxxx.xxxxx';
l_message VARCHAR2(32767) := '<html>
<body>
<img src="cid:image.jpg" alt="This is a banner"/>
<br>
Test HTML with Embedded Image-chk latest
<p>And here it is:</p>
<p>The end.</p>
</body>
</html>
';
begin
SELECT PIC
INTO p_image
FROM TEMP_MAIL_PIC_BLOB
WHERE PIC_ID = 1;
conn := utl_smtp.open_connection(MailServer, 25);
UTL_SMTP.helo (conn, MailServer);
UTL_SMTP.mail (conn, 'from@abc.com');
UTL_SMTP.rcpt (conn, 'to@abc.com');
UTL_SMTP.open_data (conn);
UTL_SMTP.write_data (conn, 'From' || ': ' || 'from@abc.com'|| UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'To' || ': ' || 'to@abc.com'|| UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'Subject: image inline testing' || UTL_TCP.CRLF) ;
UTL_SMTP.write_data (conn, 'Content-Type: multipart/mixed; boundary="' || BOUNDARY || '"' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, '--' || BOUNDARY || UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, 'Content-Type: text/html; charset=US-ASCII'|| UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, l_message);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, '--' || BOUNDARY || UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, 'Content-Type: image/jpeg;'|| UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, 'Content-Disposition: inline; filename="image.jpg"' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'Content-Transfer-Encoding' || ': ' || 'base64' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
i := 1;
len := dbms_lob.getlength(p_image);
while i < len
loop
dbms_lob.read(p_image, buff_size, i, l_raw);
utl_smtp.write_raw_data(conn, utl_encode.base64_encode(l_raw));
utl_smtp.write_data(conn, utl_tcp.crlf);
i := i + buff_size;
end loop;
utl_smtp.write_data(conn, utl_tcp.crlf);
UTL_SMTP.write_data (conn, '--' || BOUNDARY || '--' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.close_data (conn);
UTL_SMTP.quit (conn);
end;
|
|
|
|
|
|
| Re: Sending UTL_SMTP mail with jpg picture as header inside an e-mail [message #609369 is a reply to message #609361] |
Thu, 06 March 2014 01:43 |
sivakumar121
Messages: 29
Registered: April 2009
|
Junior Member |
|
|
It did not work for me - Even what I was seeing before in Outlook 2007 is not working after replacing to 3D or .png.
But It did worked in all environments (including Black Berry) with this code change.
l_message VARCHAR2(32767) := '<html>
<body>
<img src="cid:banner" alt="banner"/>
<br>
Test HTML with Embedded Image-chk latest
<p>And here it is:</p>
<p>The end.</p>
</body>
</html>
';
-----
UTL_SMTP.write_data (conn, 'Content-Type: image/jpg;'|| UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, 'Content-Disposition: inline; filename="banner.jpg"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (conn, 'Content-ID: <banner> ' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'Content-Transfer-Encoding' || ': ' || 'base64' || UTL_TCP.CRLF);
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 08:31:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
