Hi All,
I am using,
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
I want to send mail (HTML type) from databse and attach a company logo in mail body not a attached file.
The logo file (GIF File) resides in oracle directory ( I have created Oracle directory and I gave related read/write access to the user)
There is no problem in mail and other part, Only problem is the attached image not visible in Outlook. showing red cross mark.
But the same picture visible in browser( Right click mail and view source, then i copy the source and i created a HTML File, when i open that HTML in browser its showing the picture perfectly )
I have place my code below, (2 procedures )
PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER )
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_clob CLOB;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
l_clob := '<html> <head><title>Test HTML with Embedded Image</title> </head>
<body
<img src="data:image/gif;base64,';
*get_enc_img_from_fs* (p_clob => l_clob);
l_clob := l_clob || '" alt="Site Logo" />
body></html>';
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, l_clob);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE get_enc_img_from_fs (p_clob IN OUT NOCOPY CLOB)
AS
l_bfile BFILE;
l_step PLS_INTEGER := 24573;
BEGIN
l_bfile := BFILENAME('WORKDIR','comp_logo.gif');
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_bfile) - 1 )/l_step) LOOP
p_clob := p_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_bfile, l_step, i * l_step + 1)));
END LOOP;
DBMS_LOB.fileclose(l_bfile);
END;
--------------------------------------------------------------------------------------
please help me and where i want to change the code,
Thanks in advance.