UTL_SMTP: Sending accents

Question

UTL_SMTP: Sending accents

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

Hello,

I'm using Oracle XE 11.2 to send emails using the UTL_SMTP package, but whenever there are accents in the subject or message, it is replaced by a "?". What I have is the following:

I have a procedure that contains the parameters for sending email:

v_Mail_Conn := utl_smtp.Open_Connection(v_smtp, 25);
 --autenticacao
 utl_smtp.command( v_Mail_Conn, 'AUTH LOGIN'); 
 utl_smtp.command( v_Mail_Conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( v_username ))) ); 
 utl_smtp.command( v_Mail_Conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( v_pwd ))) ); 


 --Conexao
 utl_smtp.Helo(v_Mail_Conn, v_smtp);
 utl_smtp.Mail(v_Mail_Conn, v_from);
 utl_smtp.Rcpt(v_Mail_Conn, v_to);

--MENSAGEM SEM ANEXO TEXTO PLANO
 /*utl_smtp.Data(v_Mail_Conn,
   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
   'From: '   || v_from || crlf ||
   'Subject: '|| v_assunto || crlf ||
   'To: '     || v_to || crlf ||
   crlf || v_message || ''
 );*/

--MENSAGEM SEM ANEXO HTML
utl_smtp.Data(v_Mail_Conn,
    'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
    'From: '   || v_from || crlf ||
    'Subject: '|| v_assunto || crlf ||
    'To: '     || v_to || crlf ||

    'MIME-Version: 1.0'|| crlf ||    -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/html; charset="UTF-8"'|| crlf ||
    'Content-Transfer-Encoding: 8bit'|| crlf ||
    crlf ||
        v_message ||
    crlf);

From my research, I noticed that the problem may be in this part:

utl_smtp.Data(v_Mail_Conn,
        'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
        'From: '   || v_from || crlf ||
        'Subject: '|| v_assunto || crlf ||
        'To: '     || v_to || crlf ||

        'MIME-Version: 1.0'|| crlf ||    -- Use MIME mail standard
        'Content-Type: multipart/mixed;'|| crlf ||
        ' boundary="-----SECBOUND"'|| crlf ||
        crlf ||

        '-------SECBOUND'|| crlf ||
        'Content-Type: text/html; charset="UTF-8"'|| crlf ||
        'Content-Transfer-Encoding: 8bit'|| crlf ||
        crlf ||
            v_message ||
        crlf);

I'm setting 8bit and UTF-8, but even changing the charset it still sends the "?" as a message.

I searched for UTL_ENCODE.QUOTED_PRINTABLE_ENCODE and set content-transfer-encoding to be quoted-printable, but I still continue with the error.

These are the NLS language parameters:

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  . 
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_CHARACTERSET    AL32UTF8
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

sql oracle email

asked by anonymous 17.11.2014 / 14:45

2 answers

(JavaScript-jQuery) Variables within variables giving "undefined" How to use jsPDF addHTML?

score 2 · Answer 1

You can create a function that you pass a string and the function returns you the string with the conversion in the default Ascii code> 127.

Example:

create or replace function converte(p_texto in varchar2) return varchar2 is
  RESULT LONG := text;   
begin
   RESULT := REPLACE(RESULT, 'À', '&Agrave');
   RESULT := REPLACE(RESULT, 'à', '&agrave;');
   RESULT := REPLACE(RESULT, 'Ã', '&Atilde;');
   RESULT := REPLACE(RESULT, 'ã', '&atilde;');
   RESULT := REPLACE(RESULT, 'Õ', '&Otilde;');
   RESULT := REPLACE(RESULT, 'õ', '&otilde;');
   RETURN(RESULT);
end;

Using:

DECLARE
  v_teste long;
BEGIN

  v_teste := converte('BALÃO');      

END;

Send so by UTL_SMPT that it will work. Some sites have the conversion table.
Link: [ link
Link: [ link

score 2 · Answer 2

I had the same problem and managed to solve the same way as directed by David Melo, but I needed some adjustments and so I decided to post this answer to help someone else get through it.

Here's how my procedure for sending email was:

create or replace PROCEDURE SEND_EMAIL
( v_From    in varchar2,
  v_Recipient in varchar2,
  v_Subject   in varchar2,
  v_Mail_Message   in varchar2)
is
  v_Mail_Host VARCHAR2(30) := 'mail.server.com.br';
  v_Mail_Conn utl_smtp.Connection;
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
 utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
 utl_smtp.Mail(v_Mail_Conn, v_From);
 utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
 utl_smtp.Data(v_Mail_Conn,
   'Date: '   || to_char(sysdate, 'Dy, DD/MM/YYYY HH24:mi:ss') || crlf ||
   'From: '   || v_From || crlf ||
   'Subject: '|| v_Subject || crlf ||
   'To: '     || v_Recipient || crlf ||

   'MIME-Version: 1.0'|| crlf ||    -- Use MIME mail standard
   'Content-Type: text/html;charset=UTF-8'|| crlf ||
    crlf ||
        CONVERT_SPECIAL_CHAR(v_Mail_Message) ||
    crlf);
 utl_smtp.Quit(v_Mail_Conn);
EXCEPTION
 WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
  raise_application_error(-20000, 'Não foi possível enviar o e-mail.', TRUE);
 WHEN OTHERS THEN
  raise_application_error(-20001,
      'Ocorreu o seguinte erro: ' || sqlerrm);
END;

In the above procedure I called the CONVERT_SPECIAL_CHAR function to convert the special characters, which was the same as suggested by @DavidMelo, with a simple correction. Here is the function:

create or replace function CONVERT_SPECIAL_CHAR(p_texto in varchar2) RETURN VARCHAR2
AS
  v_result VARCHAR2(32767);   
begin
   v_result := p_texto;
   v_result := REPLACE(v_result, 'À', chr(38)||'Agrave;');
   v_result := REPLACE(v_result, 'à', chr(38)||'agrave;');
   v_result := REPLACE(v_result, 'Ã', chr(38)||'Atilde;');
   v_result := REPLACE(v_result, 'ã', chr(38)||'atilde;');
   v_result := REPLACE(v_result, 'Õ', chr(38)||'Otilde;');
   v_result := REPLACE(v_result, 'õ', chr(38)||'otilde;');
   v_result := REPLACE(v_result, 'á', chr(38)||'aacute;');
   v_result := REPLACE(v_result, 'Á', chr(38)||'Aacute;');
   v_result := REPLACE(v_result, 'é', chr(38)||'eacute;');
   v_result := REPLACE(v_result, 'É', chr(38)||'Eacute;');
   v_result := REPLACE(v_result, 'í', chr(38)||'iacute;');
   v_result := REPLACE(v_result, 'Í', chr(38)||'Iacute;');
   v_result := REPLACE(v_result, 'ó', chr(38)||'oacute;');
   v_result := REPLACE(v_result, 'Ó', chr(38)||'Oacute;');
   v_result := REPLACE(v_result, 'ú', chr(38)||'uacute;');
   v_result := REPLACE(v_result, 'Ú', chr(38)||'Uacute;');
   v_result := REPLACE(v_result, 'â', chr(38)||'acirc;');
   v_result := REPLACE(v_result, 'Â', chr(38)||'Acirc;');
   v_result := REPLACE(v_result, 'ê', chr(38)||'ecirc;');
   v_result := REPLACE(v_result, 'Ê', chr(38)||'Ecirc;');
   v_result := REPLACE(v_result, 'ô', chr(38)||'ocirc;');
   v_result := REPLACE(v_result, 'Ô', chr(38)||'Ocirc;');
   v_result := REPLACE(v_result, 'ç', chr(38)||'ccedil;');
   v_result := REPLACE(v_result, 'Ç', chr(38)||'Ccedil;');
   v_result := REPLACE(v_result, 'ü', chr(38)||'uuml;');
   v_result := REPLACE(v_result, 'Ü', chr(38)||'Uuml;');
   RETURN(v_result);
end;

Note that texts should always be configured as HTML since we are using:

'Content-Type: text / html; charset = UTF-8'

Finally an example to test the function and procedure:

Declare
  text varchar2(32000) := '<html><head></head><title>Título</title><body>' ||
  '<p>Olá... <b>Teste e-mail HTML</b>,</p>' ||
  '<p>Testes acentos: áÁ ãÃ õ á ó é úÚ Àà òÒ...</p> </body></html>';
BEGIN
  SEND_EMAIL('[email protected]', '[email protected]', 'Assunto do e-mail', text); 
END;