Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, shivanshu.

Asked: June 27, 2016 - 9:05 am UTC

Last updated: June 30, 2016 - 1:33 am UTC

Version: Version 4.0.2.15

Viewed 1000+ times

You Asked

Hi Tom,

I have issue with my UTL Email, when i am sendint the email the text matter comming in same line:
please check comments in the code where the issue:


create or replace PROCEDURE SA_EMAIL AS
p_from VARCHAR2 (2000) := 'abc@homecredit.co.in';
--p_to VARCHAR2 (2000) := 'abc@homecredit.co.in';
p_subject VARCHAR2 (2000) := 'Test Mail';
p_text_msg VARCHAR2 (2000);
p_smtp_host VARCHAR2 (2000) := '101.6.4.8';
p_smtp_port NUMBER := 25;
p_html_msg VARCHAR2 (4000);
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2 (100) := '----=*#abc1234321cba#*=';


CURSOR SA_CUR IS
SELECT EMPLOYEE_NAME,EMPLOYEE_EMAIL FROM HCIN_SINGHS.RISK_TEAM where EMPLOYEE_NAME IN ('SHIVANSHU SINGH','NAME_TEST2');

BEGIN

FOR I IN SA_CUR
LOOP


p_html_msg :=
'<html>
<head>
<title>Test HTML message</title>
</head>
<body>
<p>This is a HTML version of the test message.</p>
<p>Please consider this as a warning, since you have very high probability of moving into HARD*Trigger and you may be BLOCKED* for doing new business very soon. </p>
<p></p>
<p>Therefore you are directed to use prudent judgment in identifying the right customer, in order to prevent fraud loss, by ensuring the following: </p>
<p><img src="C:\Users\shivanshu.singh\Pictures\HCG.jpg" alt="Smiley face" height="800" width="1000">
</body>
</html>';




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, I.EMPLOYEE_EMAIL);
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: ' || I.EMPLOYEE_EMAIL || 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/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf
|| UTL_TCP.crlf
);

IF p_text_msg 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/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn, p_text_msg);
UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_html_msg 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="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);


UTL_SMTP.write_data (l_mail_conn, 'Dear' || ' ' || I.EMPLOYEE_NAME || UTL_TCP.crlf || UTL_TCP.CRLF);

--## I have issue with below lines which is coming in a single line and this should be in 3 different line.
UTL_SMTP.write_data (l_mail_conn, 'You have been triggered on delinquency metrics............' || UTL_TCP.crlf || UTL_TCP.CRLF);
UTL_SMTP.write_data (l_mail_conn, ' DATE TRIGGER ' || ' ' || current_date || UTL_TCP.crlf );
UTL_SMTP.write_data (l_mail_conn, ' TRIGGER_NAME ' || ' ' || 'abc' || UTL_TCP.crlf );
UTL_SMTP.write_data (l_mail_conn, ' PERCENTAGE_IMPACT ' || ' ' || 'def' ||chr(13)|| UTL_TCP.crlf );
--UTL_SMTP.write_data (l_mail_conn, 'Please consider this as a warning, since you have very high probability of moving into HARD*Trigger and you may be BLOCKED* for doing new business very soon. ' ||chr(13)|| UTL_TCP.crlf );
--UTL_SMTP.write_data (l_mail_conn, 'Therefore you are directed to use prudent judgment in identifying the right customer, in order to prevent fraud loss, by ensuring the following: ' ||chr(13)|| UTL_TCP.crlf );
UTL_SMTP.write_data (l_mail_conn, p_html_msg);
UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);


UTL_SMTP.write_data (l_mail_conn, 'Best Regards,'|| UTL_TCP.crlf );
UTL_SMTP.write_data (l_mail_conn, 'Team Analytics' || 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 LOOP;
END;

and Connor said...

If you are sending a HTML email, then line breaks are done with html.

So you would output something like:

UTL_SMTP.write_data (l_mail_conn, '<br>You have been....
UTL_SMTP.write_data (l_mail_conn, '<br>DATE_TRIGGER
UTL_SMTP.write_data (l_mail_conn, '<br>TRIGGER_NAME

etc etc

Notice the "<br>"

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

shivanshu singh, June 29, 2016 - 9:16 am UTC

Thanks, it's working now.
Connor McDonald
June 30, 2016 - 1:33 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library