Home » SQL & PL/SQL » SQL & PL/SQL » Sending email MS Word attachment containing Arabic letters (11.2.0.1.0)
Sending email MS Word attachment containing Arabic letters [message #681890] |
Sun, 06 September 2020 04:24 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I hope all of you are safe.
I am trying to send an email with an MS Word attachment that contains both English and Arabic letters. The actual data that is going to be send is larger than the size of VARCHAR2, which is why I am using CLOB datatype.
I have two stored procedures:
1- SEND_MAIL: to handing the sending function
2- MYSMTP_MAIL: to test the stored procedure by sending the attachment body.
Issue:
Both stored procedures work fine and the attachment is sent but with "?"s instead of the Arabic letters. However, this happens only when I am trying to send the text in MS Word attachment, when I try to modify the SP and send the text in the mail body, it's send correctly with the correct Arabic letters.
I tired:
using different charsets (windows-1256, AL16UTF16, UTF8, and even AR8ISO8859P6 which is the DB NLS_CHARACTERSET)with the same result.
Appreciate your advice.
Following are my the stored procedures:
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_clob IN CLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000;
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/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
-- UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset=AL16UTF16; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html;charset="windows-1256" name="' || p_attach_name || '"' || UTL_TCP.crlf );
--UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html;charset="AR8ISO8859P6" name="' || p_attach_name || '"' || UTL_TCP.crlf );
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC(( DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
END LOOP;
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
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;
CREATE OR REPLACE PROCEDURE MySMTP_mail
(
S_APP_USER IN VARCHAR2,
S_MODULE_NAME IN VARCHAR2
)
/**/
IS
T_SENDER VARCHAR2(30) := 'IT_SYSTEM_ALERT3@123.org';
T_CC VARCHAR2(30) := 'abc@a123.org';
T_MESG VARCHAR2 (10); --CLOB;
T_ATTACH CLOB;--VARCHAR2(1000);
T_MTYPE VARCHAR2(40) := 'text/html; charset=AL16UTF16';
BEGIN
T_MESG:= 'My message';
T_ATTACH :=
'<table width="100%" border="0">
<tr>
<td style="text-align: center;"><span style="font-size: 16pt;">
<p style="text-align: center; text-decoration:underline;"><span style="font-size: 16pt;"><strong>جدول السداد المعدل طبقاً لقرار مجلس الادارة رقم (26) لسنة 2020</strong></span></p>
</td>
</tr>
</table>';
send_mail('mfarouk@arabfund.org', T_SENDER,'Title: SMTP send', NULL, 'Myat.doc', T_MTYPE, T_ATTACH, '178.44.44.44', 25);
END;
Thanks,
ferro
|
|
|
Goto Forum:
Current Time: Sun Sep 29 01:11:43 CDT 2024
|