Random exclamation marks in email body generated using UTL_SMTP in PL/SQL
When i am generating a table of values and writing the output as an email body using UTL_SMTP in pl-sql.
the table generated consistos of 5 columns and 29 rows.
Problem is some random exclamatory marks ! are being generated in the email body.
Code is as follows....
CREATE OR REPLACE PROCEDURE APPS.xxjdsu_crm_jobs_monitoring(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_subject IN VARCHAR2,
P_recipient_to_list IN VARCHAR2,
P_recipient_cc_list IN VARCHAR2,
p_signature1 IN VARCHAR2,
p_signature2 IN VARCHAR2,
p_signature3 IN VARCHAR2,
p_signature4 IN VARCHAR2
)
AS
l_recipient_to_list VARCHAR2(32767) :=lower(replace(P_recipient_to_list,';',','));
l_recipient_cc_list VARCHAR2(32767) :=lower(replace(P_recipient_cc_list,';',','));
l_Mail_Conn utl_smtp.Connection;
l_Mail_Host VARCHAR2(100) := 'localhost';
l_From VARCHAR2(80) ;
l_Recipient VARCHAR2(3000);
l_run_date DATE := sysdate;
l_Subject VARCHAR2(1000) ;
l_priority VARCHAR2(10) := 'NORMAL';
l_instance VARCHAR2(80);
l_crlf VARCHAR2(2) := chr(13)||chr(10);
l_msg_body clob := empty_clob; --This LOB will be the email message
l_msg_data VARCHAR2(32767) :=null;
l_msg_tail VARCHAR2(32767);
l_signature VARCHAR2(32767);
l_job_status VARCHAR2(30) := null;
l_bg_colour VARCHAR2(80):= null;
l_schedule NUMBER :=0;
l_rp NUMBER :=1;
l_rp1 NUMBER :=1;
j NUMBER :=0;
CURSOR cur_req_list_data IS
(SELECT rownum,
c.PROGRAM_NAME,
c.LAST_RUN_REQUEST_ID,
c.LAST_RUNDATE,
c.RESULT
FROM ((SELECT fcv.user_concurrent_program_name "PROGRAM_NAME",
fcr.request_id "LAST_RUN_REQUEST_ID",
TO_CHAR(fcr.actual_completion_date,' DD/MON/YYYY HH24:MI:SS') "LAST_RUNDATE" ,
DECODE(fcr.phase_code,
'C','Completed',fcr.phase_code)||'/'||DECODE(fcr.status_code,
'C', 'Normal',
'D', 'Cancelled',
开发者_如何学Go 'E','Errored',
'F','Scheduled',
'G', 'Warning',fcr.status_code) "RESULT"
FROM apps.fnd_concurrent_programs_vl fcv,apps.fnd_concurrent_requests fcr
WHERE user_concurrent_program_name IN ( SELECT fvv.description
FROM apps.fnd_lookup_types_vl ftv,
apps.fnd_lookup_values_vl fvv
WHERE ftv.lookup_type = fvv.lookup_type
AND ftv.lookup_type LIKE 'CRM_JOBS'
AND fvv.tag = 10)
AND fcv.concurrent_program_id = fcr.concurrent_program_id
AND fcr.actual_completion_date IS NOT NULL
AND fcr.actual_completion_date in ( SELECT max(fcr1.actual_completion_date)
FROM apps.fnd_concurrent_programs_vl fcv1,apps.fnd_concurrent_requests fcr1
WHERE user_concurrent_program_name IN ( SELECT fvv.description
FROM apps.fnd_lookup_types_vl ftv,
apps.fnd_lookup_values_vl fvv
WHERE ftv.lookup_type = fvv.lookup_type
AND ftv.lookup_type LIKE 'CRM_JOBS'
AND fvv.tag = 10)
AND fcv1.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.PHASE_CODE='C'
AND fcr1.actual_completion_date IS NOT NULL
GROUP BY fcr1.concurrent_program_id)
)
UNION
(SELECT fcv.user_concurrent_program_name "PROGRAM_NAME",
fcr.request_id "LAST_RUN_REQUEST_ID",
TO_CHAR(fcr.actual_completion_date,' DD/MON/YYYY HH24:MI:SS') "LAST_RUNDATE" ,
DECODE(fcr.phase_code,
'C','Completed',fcr.phase_code)||'/'||DECODE(fcr.status_code,
'C', 'Normal',
'D', 'Cancelled',
'E','Errored',
'F','Scheduled',
'G', 'Warning',fcr.status_code) "RESULT"
FROM apps.fnd_concurrent_programs_vl fcv,
apps.fnd_concurrent_requests fcr
WHERE fcv.concurrent_program_id = fcr.concurrent_program_id
AND request_id IN (SELECT request_id
FROM apps.fnd_concurrent_programs_vl fcv,
apps.fnd_concurrent_requests fcr
WHERE fcv.USER_CONCURRENT_PROGRAM_NAME IN (SELECT fvv.description
FROM apps.fnd_lookup_types_vl ftv,
apps.fnd_lookup_values_vl fvv
WHERE ftv.lookup_type = fvv.lookup_type
AND ftv.lookup_type LIKE 'CRM_JOBS'
AND fvv.tag = 20)
AND fcv.concurrent_program_id = fcr.concurrent_program_id
AND fcr.actual_completion_date IS NOT NULL
AND 2>=(SELECT count(fcr1.request_id)
FROM apps.fnd_concurrent_programs_vl fcv1,
apps.fnd_concurrent_requests fcr1
WHERE fcv1.USER_CONCURRENT_PROGRAM_NAME=fcv.USER_CONCURRENT_PROGRAM_NAME
AND fcr1.actual_completion_date>=fcr.actual_completion_date
AND fcv1.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.PHASE_CODE='C'
AND fcr1.actual_completion_date IS NOT NULL)
UNION
(SELECT request_id
FROM (SELECT request_id
FROM apps.fnd_concurrent_programs_vl fcv,
apps.fnd_concurrent_requests fcr
WHERE USER_CONCURRENT_PROGRAM_NAME IN (SELECT fvv.description
FROM apps.fnd_lookup_types_vl ftv,
apps.fnd_lookup_values_vl fvv
WHERE ftv.lookup_type = fvv.lookup_type
AND ftv.lookup_type LIKE 'CRM_JOBS'
AND fvv.tag = 40)
AND fcv.concurrent_program_id = fcr.concurrent_program_id
AND fcr.PHASE_CODE='C'
AND fcr.actual_completion_date IS NOT NULL
ORDER BY request_id desc
)
WHERE rownum<=4
)
)
)
UNION
(
SELECT fcr.description, fcr.request_id "LAST_RUN_REQUEST_ID",
TO_CHAR (fcr.actual_completion_date,'DD/MON/YYYY HH24:MI:SS') "LAST_RUNDATE",
DECODE (fcr.phase_code,
'C', 'Completed', fcr.phase_code)|| '/'|| DECODE (fcr.status_code,
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
fcr.status_code) "RESULT"
FROM apps.fnd_concurrent_programs_vl fcv,
apps.fnd_concurrent_requests fcr
WHERE fcr.description LIKE 'JDSU SNT Customer Portal Extract Set'
AND fcv.concurrent_program_id = fcr.concurrent_program_id
AND fcr.actual_completion_date IS NOT NULL
AND fcr.actual_completion_date IN (
SELECT MAX (fcr2.actual_completion_date)
FROM apps.fnd_concurrent_programs_vl fcv2,
apps.fnd_concurrent_requests fcr2
WHERE fcr2.description IN (SELECT fvv.description
FROM apps.fnd_lookup_types_vl ftv,
apps.fnd_lookup_values_vl fvv
WHERE ftv.lookup_type = fvv.lookup_type
AND ftv.lookup_type LIKE 'CRM_JOBS'
AND fvv.tag = 5)
AND fcv2.concurrent_program_id = fcr2.concurrent_program_id
AND fcr2.PHASE_CODE='C'
AND fcr2.actual_completion_date IS NOT NULL
GROUP BY fcr2.concurrent_program_id)
)
ORDER BY 1,2 DESC )
c);
BEGIN
SELECT name
INTO l_instance
FROM v$database;
l_From := 'wf'||lower(l_instance)||'@AtOracle.com';
l_Subject := p_subject|| ' Status Card on '||to_char(l_run_date,'DD-Mon-YYYY')||'{'||l_instance||'}';
l_Mail_Conn := utl_smtp.Open_Connection(l_Mail_Host, 25);
utl_smtp.Helo(l_Mail_Conn, l_Mail_Host);
utl_smtp.Mail(l_Mail_Conn, l_From);
IF l_recipient_to_list IS NOT NULL THEN
WHILE l_rp1 <> 0
LOOP
l_rp := instr(l_recipient_to_list,',',l_rp1);
exit when l_rp=0;
l_Recipient := substr(l_recipient_to_list,l_rp1,l_rp-l_rp1);
utl_smtp.Rcpt(l_Mail_Conn,l_Recipient);
l_rp1 :=l_rp+1;
END LOOP;
IF L_RP=0 THEN
l_Recipient := substr(l_recipient_to_list,l_rp1);
utl_smtp.Rcpt(l_Mail_Conn,l_Recipient);
END IF;
END IF;
l_rp :=1;
l_rp1 :=1;
IF l_recipient_cc_list IS NOT NULL THEN
WHILE l_rp1 <> 0
LOOP
l_rp := instr(l_recipient_cc_list,',',l_rp1);
exit when l_rp=0;
l_Recipient := substr(l_recipient_cc_list,l_rp1,l_rp-l_rp1);
utl_smtp.Rcpt(l_Mail_Conn,l_Recipient);
l_rp1 :=l_rp+1;
END LOOP;
IF L_RP=0 THEN
l_Recipient := substr(l_recipient_cc_list,l_rp1);
utl_smtp.Rcpt(l_Mail_Conn,l_Recipient);
END IF;
END IF;
l_msg_body := l_msg_body
|| '<html>'
|| '<body>'
|| '<pre><font face="TimesNewRoman" size="2"><b><font color="#0000FF"></b></font></pre>'
|| '</body>'|| '</html>';
l_msg_body := l_msg_body
||'<table BORDER WIDTH="100%" BGCOLOR="#66FFFF" >
<tr>
<td WIDTH=600 HEIGHT =100 ALIGN="CENTER"><b><font color="#000000">'||p_subject||'</strong></font></b></td>
<td>
<table BORDER=0.5 WIDTH="100%" BGCOLOR="#CCCCCC" >
<tr> <td> </td>
<td>
<tr> <td BGCOLOR="#FFFF00">'
||'Yellow = Completed Warning'
||'</td></tr>
<tr> <td> </td> <td><tr> <td BGCOLOR="#FF0000">'
||'Red = Completed Error'
||'</td><tr> <td BGCOLOR="#FFFFFF">'
||'White = Completed Successfully '
||'</td></tr><tr> <td> </td></tr></table><td></td></tr></table>'
;
l_msg_body := l_msg_body
|| '<table BORDER COLS=5 WIDTH="100%" BGCOLOR="#CCCCCC" ><tr><td WIDTH="10%" ALIGN="CENTER"><b><font color="#000000">S.no</strong></font></b></td>
<td WIDTH="40%" ALIGN="CENTER"><b><font color="#000000"><strong>Program Name</strong></font></b></td>
<td WIDTH="15%" ALIGN="CENTER"><b><font color="#000000"><strong>Last Run Request ID</strong></font></b></td>
<td WIDTH="20%" ALIGN="CENTER"><b><font color="#000000"><strong>Last Run Date</strong></font></b></td>
<td WIDTH="15%" ALIGN="CENTER"><b><font color="#000000"><strong>Result</strong></font></b></td> </tr>'
;
-- Daily --
FOR i in cur_req_list_data
LOOP
IF i.result = 'Completed/Normal' then
l_bg_colour := 'BGCOLOR="#FFFFFF"';
ELSIF i.result = 'Completed/Warning' then
j:=j+1;
l_bg_colour := 'BGCOLOR="#FFFF00"';
ELSIF i.result ='Completed/Errored' then
j:=j+1;
l_bg_colour := 'BGCOLOR="#FF0000"';
END IF ;
l_msg_body := l_msg_body
|| '<tr BGCOLOR="#FFFFFF" ><td WIDTH="10%" ALIGN="LEFT"><font color="#000000">'
||replace(i.rownum,'!','')
||'</font></td><td '
||l_bg_colour
||' WIDTH="40%" ALIGN="LEFT"><font color="#000000"><strong>'
||replace(i.program_name,'!','')
||'</strong></font></td> <td '
||l_bg_colour
||' WIDTH="15%" ALIGN="LEFT"><font color="#000000"><strong>'
||replace(i.LAST_RUN_REQUEST_ID,'!','')
||'</strong></font></td><td '
||l_bg_colour
||' WIDTH="20%" ALIGN="LEFT"><font color="#000000"><strong>'
||replace(i.LAST_RUNDATE,'!','')
||'</strong></font></td><td '
||l_bg_colour
||' WIDTH="15%" ALIGN="LEFT"><font color="#000000"><strong>'
||replace(i.RESULT,'!','')
||'</strong></font></td></tr>'
;
l_bg_colour := null;
END LOOP;
l_msg_body := l_msg_body||'</table>';
IF j!=0 THEN
l_msg_body := l_msg_body
||'<br><table BORDER=0 COLS=1 WIDTH="100%" > <tr ><td WIDTH=400 ALIGN="LEFT"><b>NOTE : <font color="#ff0000"> Few jobs Completed Error or Completed Warning </font></b></td></tr> <br></br>';
END IF;
l_signature := '<br><table BORDER=0 COLS=1 WIDTH="50%" > <tr ><td WIDTH=400 ALIGN="LEFT"><font color="#555658"> Best Regards </strong></font></td></tr><tr ><td WIDTH=400 ALIGN="LEFT"><b><font color="#555658">'
|| p_signature1
||'</strong></font></b></td></tr>';
IF p_signature2 IS NOT NULL THEN
l_signature := l_signature
||'<tr ><td WIDTH="50%" ALIGN="LEFT"><font color="#555658">'
|| p_signature2
||' </strong></font></td></tr>';
END IF;
IF p_signature3 IS NOT NULL THEN
l_signature := l_signature
||'<tr ><td WIDTH="50%" ALIGN="LEFT"><font color="#555658">'
|| p_signature3
||' </strong></font></td></tr>';
END IF;
IF P_SIGNATURE4 IS NOT NULL THEN
l_signature := l_signature
|| '<tr ><td WIDTH="50%" ALIGN="LEFT"><font color="#555658"> Team PDL: <u> <font color="#0000CC"> '
|| p_signature4
||' </u> </strong></font></td></tr>';
END IF;
l_msg_body := 'From: ' || l_From || l_crlf ||
'Subject: ' || l_Subject|| l_crlf ||
'To: ' || l_recipient_to_list || l_crlf ||
'CC: ' || l_recipient_cc_list || l_crlf ||
'Importance: '|| l_priority || l_crlf || l_msg_body||l_signature|| '';
utl_smtp.data(l_Mail_Conn, 'MIME-Version: 1.0' || l_crlf ||'Content-type: text/html' || l_crlf || l_msg_body);
utl_smtp.Quit(l_Mail_Conn);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG,'This is the exception caught during data AND quit');
END;
RFC 821 which specifies the SMTP protocol specifies that the maximum length of a line of text is 1000 characters including the CRLF.
text line
The maximum total length of a text line including the is 1000 characters (but not counting the leading dot duplicated for transparency).
I don't see anywhere in your code that you are adding CRLF's to your text so it appears that you are trying to generate a single line of text that is well in excess of 1000 characters. The exclamation points you seeing are likely the result of your SMTP server adding CRLF's every 1000 characters or so in order to make your message valid. If you added CRLF's yourself instead so that no line was more than 1000 characters, the exclamation points should go away.
Adding the CRLF character reduced the length of each line, regardless of the length of the body and <br/> tag.
For my particular case I have a variable to hold the <br/> tag and appending the UTL_TCP.CRLF, i.e., v_endline VARCHAR2 (100) := '<br/>' || UTL_TCP.CRLF; solved my issue.
Thank you!
I think adding new empty lines in the html tags will resolve the issue. just add the appropriate tags to get a new space such that the message you are printing will not be a 1000 character one as a whole but will be reduced in length
加载中,请稍侯......
精彩评论