Formatting carriage return, line feed in Oracle PL/SQL

Question

I have problems regarding a mail body formatting I'm sending:

Here is the KO version (no new lines):

declare
  crlf                     VARCHAR2(2) := chr(13)||chr(10); 
  msg_body                 VARCHAR2(2000);
begin
  msg_body := msg_body || ('Blablah  : '|| SYSTIMESTAMP ) || crlf;
  msg_body := msg_body || ('Blablah  : '|| SYSTIMESTAMP ) || crlf;
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'xx@xx.com', recipients => 'yy@yy.com', subject => 'Blah', MESSAGE => msg_body);
end;

Here is the OK version (nice new lines):

declare
  crlf                     VARCHAR2(2) := chr(13)||chr(10);
  msg_body                 VARCHAR2(2000);
begin
  msg_body := msg_body || ('Blablah  : ') || crlf;
  msg_body := msg_body || ('Blablah  : ') || crlf;
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'xx@xx.com', recipients => 'yy@yy.com', subject => 'Blah', MESSAGE => msg_body);
end;

Best regards

Answer 1

The only difference between your two versions is the exclusion of SYSTIMESTAMP in the one that works.

You're not explicitly converting your SYSTIMESTAMP to a character using TO_CHAR(). It will be being implicitly converted according to your NLS_DATE_FORMAT instead.

Convert it to a character correctly, using whatever format model you wish; for instance

to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss:ff3')

_{ff is fractional seconds.}

To quote

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• SQL statements are easier to understand when you use explicit data type conversion functions.

• Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.

• Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

---

I would recommend investigating using UTL_SMTP instead of UTL_MAIL. You don't need to alter the session. A really simple send procedure might look like this:

declare

   l_to_list  long;
   l_crlf varchar2(2) := chr(13) || chr(10);
   l_conn utl_smtp.connection;
   l_date     varchar2(255) default to_char(sysdate, 'dd Mon yyyy hh24:mi:ss');

begin

   l_conn := utl_smtp.open_connection(<mailhost>, 25);
   utl_smtp.helo(l_conn, <mailhost>);
   utl_smtp.mail(l_conn, <sender>);

   l_to_list := address_email('To: ', <recipients>);

   utl_smtp.open_data(l_conn);

   utl_smtp.write_data('Date: ' || l_date);
   utl_smtp.write_data('From: ' || <sender>);
   utl_smtp.write_data('Subject: ' || nvl(<subject>, '(No Subject)'));
   utl_smtp.write_data('X-Mailer: ' || <mailer_id>);

   utl_smtp.write_data(l_to_list);

   utl_smtp.write_data(l_conn, '' || l_crlf);
   utl_smtp.write_data(l_conn, <msg>);
   utl_smtp.close_data(l_conn);
   utl_smtp.quit(l_conn);

end;