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
Previous Topic: outer query returning more than one row
Next Topic: How to retrieve data only those which are in decimals
Goto Forum:
  


Current Time: Thu Mar 28 08:14:06 CDT 2024