Como resolver problema de acentuação usando utl_smtp?

[slbaggio]

Pessoal,

Usamos a seguinte procedure para enviar email:

Selecionar tudo

CREATE OR REPLACE PROCEDURE Send_Mail
( sender IN VARCHAR2, recipient IN VARCHAR2, subject IN VARCHAR2, message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := '10.76.1.16'; -- > NOME OU IP DO SERVIDOR DE SMTP
mail_conn utl_smtp.connection;
crlf VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(1000);

BEGIN

mail_conn := utl_smtp.open_connection(mailhost, 25);

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
       'From: <'||sender||'>' || crlf ||
       'Subject: '||subject || crlf ||
       'To: '||recipient || crlf ||
       message;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.DATA(mail_conn, mesg);
utl_smtp.quit(mail_conn);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'unable to send the mail.'||SQLERRM);

END;
/

Pergunta: com fazer na procedure acima para alterar o content-type do envio para: "text/html" (send_header('Content-Type','text/html; charset=iso-8859-1'); ) ???

Grato,

[dr_gori]

Veja se esse tópico te ajuda:
http://glufke.net/oracle/viewtopic.php?t=3871
http://glufke.net/oracle/viewtopic.php?t=2437

[stohlirck]

Slbaggio, segue um exemplo, acho que pode te ajudar..

Selecionar tudo

Procedure Prc_Envia_Email(Pp_Smtp_Server    In Varchar2,
                            Pp_Smtp_Port      In Number,
                            Pp_Username_Auth  In Varchar2,
                            Pp_Password_Auth  In Varchar2,
                            Pp_Remetente      In Varchar2,
                            Pp_Nome_Remetente In Varchar2,
                            Pp_Destinatario   In Varchar2,
                            Pp_Assunto        In Varchar2,
                            Pp_Mensagem       In Long) Is
    Mail_Conn Utl_Smtp.Connection;
  
  Begin
  
    Mail_Conn := Utl_Smtp.Open_Connection(Pp_Smtp_Server, Pp_Smtp_Port);
    Utl_Smtp.Helo(Mail_Conn, Pp_Smtp_Server);
    Utl_Smtp.Command(Mail_Conn, 'AUTH LOGIN');
    Utl_Smtp.Command(Mail_Conn,
                     Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_Raw.Cast_To_Raw((Pp_Username_Auth)))));
    Utl_Smtp.Command(Mail_Conn,
                     Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_Raw.Cast_To_Raw((Pp_Password_Auth)))));
  
    Utl_Smtp.Mail(Mail_Conn, ('<' || Pp_Remetente || '>'));
    Utl_Smtp.Rcpt(Mail_Conn, ('<' || Pp_Destinatario || '>'));
  
    Utl_Smtp.Open_Data(Mail_Conn);
    Utl_Smtp.Write_Raw_Data(Mail_Conn,
                            Utl_Raw.Cast_To_Raw('From:' ||
                                                Pp_Nome_Remetente || '<' ||
                                                Pp_Remetente || '>' ||
                                                Utl_Tcp.Crlf)); -- Utl_Tcp.Crlf => CHR(13)||CHR(10);
    Utl_Smtp.Write_Raw_Data(Mail_Conn,
                            Utl_Raw.Cast_To_Raw('To:' || Pp_Destinatario ||
                                                Utl_Tcp.Crlf));
    Utl_Smtp.Write_Raw_Data(Mail_Conn,
                            Utl_Raw.Cast_To_Raw('Subject:' || Pp_Assunto ||
                                                Utl_Tcp.Crlf));
    Utl_Smtp.Write_Raw_Data(Mail_Conn,
                            Utl_Raw.Cast_To_Raw('Content-Type: text/plain; charset=iso-8859-1' ||
                                                Utl_Tcp.Crlf));
  
    Utl_Smtp.Write_Data(Mail_Conn, ' ' || Utl_Tcp.Crlf);
  
    Utl_Smtp.Write_Raw_Data(Mail_Conn,
                            Utl_Raw.Cast_To_Raw(Utl_Tcp.Crlf || Pp_Mensagem));
    --
    Utl_Smtp.Close_Data(Mail_Conn);
    Utl_Smtp.Quit(Mail_Conn);
  Exception
    When Others Then
      Raise_Application_Error(-20001, 'Erro ao enviar e-mail! ' || Sqlerrm);
      --
  End;

[dr_gori]

Eu já vi a utilização dessa função que converte os acentos:

Selecionar tudo

create or replace function fnc_encode_acentuacao(pi_string   varchar2)
return varchar2
is
  v_retorno  varchar2(500) := '';
begin
  for i in 1..length(pi_string)
  loop
    if ascii(substr(pi_string, i, 1)) between 192 and 255
      or ascii(substr(pi_string, i, 1)) = 38
    then
      v_retorno := v_retorno || '&#'||ascii(substr(pi_string, i, 1))||';';
    else
      v_retorno := v_retorno || substr(pi_string, i, 1);
    end if;
  end loop;
  return v_retorno;
end fnc_encode_acentuacao;