Home » SQL & PL/SQL » SQL & PL/SQL » ult_smtp and message format (Oracle 10.2.0.4 Linux Suse 9 ES)
ult_smtp and message format [message #399452] Wed, 22 April 2009 05:18 Go to next message
Malakay79
Messages: 41
Registered: September 2007
Member
I have this procedure for sending mail:
SIA_SEND_MAIL( mittente varchar2,
destinatario varchar2,
soggetto varchar2,
messaggio varchar2,
macchina varchar2,
porta number,
display_name varchar2
) is

subj raw(2000) := utl_raw.cast_to_raw('Subject: ' || soggetto ||
UTL_TCP.CRLF);
body raw(32000) := utl_raw.cast_to_raw(messaggio);
c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION(macchina,porta);
UTL_SMTP.HELO(c, macchina);
UTL_SMTP.MAIL(c, mittente);
UTL_SMTP.RCPT(c, destinatario);
UTL_SMTP.OPEN_DATA(c);
send_header('From', display_name);
send_header('To', destinatario);
UTL_SMTP.write_raw_data(c, subj);
send_header('Content-Type', 'text/html;charset=AL32UTF8'|| utl_tcp.CRLF);

UTL_SMTP.write_raw_data(c, body);
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' ||
sqlerrm);
end ;



Another procedure call the first one and pass parameters. One of this is the message. This message has got a specific format, like this:

v_message := v_message || 'Numero importazione: ' || ID_JOB || CHR(13) || CHR(10) ||
'Inizio importazione: ' || DT_INIZIO_JOB || CHR(13) || CHR(10) ||
'Fine importazione: ' || DT_FINE_JOB || CHR(13) || CHR(10) ||
'Descrizione job importazione: ' || DS_JOB || CHR(13) ||CHR(10) ||
'Entità lette: ' || MACRO_ENTITA_LETTE ||CHR(13) || CHR(10) ||
'Entità inserite: ' || MACRO_ENTITA_INSERITE || CHR(13) || CHR(10) ||
'Entità modificate: ' || MACRO_ENTITA_MODIFICATE ||CHR(13) || CHR(10) ||
'Entità scartate: ' || MACRO_ENTITA_SCARTATE || CHR(13) || CHR(10) || CHR(10);

But when I send mail Oracle lost the format of mail and all is written in only one row.

Is there a way to not lose it?
Re: ult_smtp and message format [message #399463 is a reply to message #399452] Wed, 22 April 2009 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that this is the mail client that you're using to mail the mail with.

Does the text of the email have the CHR(10) and chr(13) characters in it?
Re: ult_smtp and message format [message #399467 is a reply to message #399463] Wed, 22 April 2009 06:41 Go to previous messageGo to next message
Malakay79
Messages: 41
Registered: September 2007
Member
This is the body:

v_message := 'Numero importazione: ' || ID_JOB || CHR(13) || CHR(10) ||
'Inizio importazione: ' || DT_INIZIO_JOB || CHR(13) || CHR(10) ||
'Fine importazione: ' || DT_FINE_JOB || CHR(13) || CHR(10) ||
'Descrizione job importazione: ' || DS_JOB || CHR(13) ||CHR(10) ||
'Entità lette: ' || MACRO_ENTITA_LETTE ||CHR(13) || CHR(10) ||
'Entità inserite: ' || MACRO_ENTITA_INSERITE || CHR(13) || CHR(10) ||
'Entità modificate: ' || MACRO_ENTITA_MODIFICATE ||CHR(13) || CHR(10) ||
'Entità scartate: ' || MACRO_ENTITA_SCARTATE || CHR(13) || CHR(10) || CHR(10);


where:
- ID_JOB , MACRO_ENTITA_LETTE,MACRO_ENTITA_MODIFICATE and MACRO_ENTITA_SCARTATE are numbers.
-DT_INIZIO_JOB and DT_FINE_JOB are date information
Re: ult_smtp and message format [message #399473 is a reply to message #399467] Wed, 22 April 2009 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, I can see that that is the code to generate the body of the email.

Does the email, as it arrives at the mail client, have those CHR(10) and CHR(13) character in it still?
Re: ult_smtp and message format [message #399474 is a reply to message #399473] Wed, 22 April 2009 06:50 Go to previous messageGo to next message
Malakay79
Messages: 41
Registered: September 2007
Member
No
Re: ult_smtp and message format [message #399477 is a reply to message #399474] Wed, 22 April 2009 07:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Add some debug code, and just before the message added to the email, dump the text of the message to a table, or dbms_output.

If the CR/LFs are still in there at that point, then it's the mail server or the mail client that's removing them.
Re: ult_smtp and message format [message #399481 is a reply to message #399477] Wed, 22 April 2009 07:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Out of curiosity, why are you converting the message body to RAW, rather than just keeping it as Varchar2?
Re: ult_smtp and message format [message #399482 is a reply to message #399477] Wed, 22 April 2009 07:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You set the "content type" to text/html.

CR/LF are ignored when HTML is displayed in the mail client.

Either replace the CR/LF with <br>CR/LF tag, or maybe wrap the whole thing in a <pre>

Or just set the content type to text/plain.
Re: ult_smtp and message format [message #399487 is a reply to message #399481] Wed, 22 April 2009 07:42 Go to previous messageGo to next message
Malakay79
Messages: 41
Registered: September 2007
Member
This is the first procedure I create.
With this one I lost accent but I don't lost format:

procedure sia_send_mail1 (
                         mittente     IN VARCHAR2,
                         destinatario IN VARCHAR2,
                         macchina     IN VARCHAR2,
                         porta        IN NUMBER,
                         soggetto     IN VARCHAR2,
                         messaggio    IN VARCHAR2,
                         display_name IN VARCHAR2
                        )
IS
 /* $Id: sia_send_mail.sql,v 1.4 2009/04/21 09:40:37 mgamberi Exp $ */
l_mail_conn          UTL_SMTP.connection;
v_array              mytabletype;

BEGIN

      v_array := sia_util.split_string (destinatario,';');


      l_mail_conn := UTL_SMTP.open_connection(macchina, porta);
      UTL_SMTP.helo(l_mail_conn, macchina);
      UTL_SMTP.mail(l_mail_conn, MITTENTE);
      FOR i IN 1 .. v_array.COUNT LOOP
    	   DBMS_OUTPUT.PUT_LINE (v_array(i));
         UTL_SMTP.rcpt(l_mail_conn, v_array(i));
      END LOOP;

      UTL_SMTP.open_data(l_mail_conn);

      UTL_SMTP.write_data(l_mail_conn,
                          'Date: ' ||
                          TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||
                          CHR(13) || CHR(10));
      UTL_SMTP.write_data(l_mail_conn,
                          'From: ' || display_name || CHR(13) || CHR(10)); 
      UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || soggetto || CHR(10));
      UTL_SMTP.write_data(l_mail_conn,
                          'To: ' || DESTINATARIO || CHR(13) || CHR(10));
      UTL_SMTP.write_data(l_mail_conn, '' || CHR(13) || CHR(10));
      UTL_SMTP.write_data(l_mail_conn, messaggio);

      UTL_SMTP.close_data(l_mail_conn);

      UTL_SMTP.quit(l_mail_conn);
      EXCEPTION
      WHEN OTHERS THEN
      UTL_SMTP.quit(l_mail_conn);
      DBMS_OUTPUT.PUT_LINE(sqlerrm);
END sia_send_mail1;
Re: ult_smtp and message format [message #399488 is a reply to message #399487] Wed, 22 April 2009 07:49 Go to previous message
Malakay79
Messages: 41
Registered: September 2007
Member
I change the "content type" to text only.

Now it works.

Text message is formatted and accent are ok.
Previous Topic: How to remove NOT NULL constraint
Next Topic: fetch Last child using connect by
Goto Forum:
  


Current Time: Mon Dec 05 11:09:23 CST 2016

Total time taken to generate the page: 0.09883 seconds