I am getting data in Refcursor and binding in HTML table using for loop to send an email. When I am executive my procedure then it is giving me error of "numeric or value error: character string buffer too small". I have tried CLOB but still not get any success.
Error :-
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "XXCOMM.XX_EMS_MAIL_PRC_MAINTENANCE", line 113
ORA-06512: at line 6
Below id my procedure in which I am trying to send email.
create or replace PROCEDURE XX_EMS_MAIL_PRC_maintenance
(
p_site_id IN VARCHAR2
)
AS
l_in_date VARCHAR2(200) := NULL;
CURSOR cur_maintenance_list (p_site_id VARCHAR2)
IS
SELECT
c.instrument_no "Instrument_Number",
d.instrument_name "Instrument_Name",
d.eqp_srl_no "EQP_Serial_Number",
d.ownership "Ownership",
d.mfg_name "Mfg_Name",
d.model_no "Model_Number",
b.plan "Plan",
a.detail "Plan_Detail",
c.activity_id "Activity_Id",
c.maintenance_date "Maintenance_Date",
c.maintenance_due_date "Maintenance_Due_Date"
FROM
activity_detail a,
activity_master b,
maintenance_schedule c,
instrument_master d,
emp_master m
WHERE
a.plan_id = b.plan_id
AND d.instrument_no = c.instrument_no
AND c.plan_id = b.plan_id
AND c.activity_id = a.activity_id
AND ( c.instrument_no,
c.plan_id,
c.activity_id,
c.maintenance_due_date ) = (
SELECT
e.instrument_no,
e.plan_id,
e.activity_id,
MAX(e.maintenance_due_date)
FROM
maintenance_schedule e
WHERE
c.plan_id = e.plan_id
AND c.instrument_no = e.instrument_no
AND c.activity_id = e.activity_id
GROUP BY
e.instrument_no,
e.plan_id,
e.activity_id
)
AND trunc(c.maintenance_due_date) BETWEEN to_date('01-JUL-2022', 'DD-MON-RRRR') AND to_date('01-JUL-2022', 'DD-MON-RRRR')
AND d.p_manitenance_req <> 'N'
AND ( m.emp_initial = d.prepared_by
OR to_char(m.emp_no) = d.prepared_by )
AND ( b.site_id = d.site_id
OR d.site_id = '001' );
p_to sys_refcursor;
p_cc sys_refcursor;
p_bcc sys_refcursor;
v_html_msg VARCHAR2(32672) := NULL;
--v_txt_msg varchar2(250) := 'WelCome ....'||chr(10)||'this is a test mail';
v_to VARCHAR2 (1000) := NULL;
v_cc VARCHAR2 (1000) := NULL;
v_bcc VARCHAR2 (2000) := NULL;
v_from VARCHAR2 (150) := 'abc.com';
v_db_name varchar2(25) := null;
BEGIN
BEGIN
v_db_name := null;
SELECT listagg(EMAIL, ',') WITHIN GROUP (order by Email) INTO v_to FROM sml.xx_lsp_email_master WHERE email_type = 'TO' AND function_name = 'EMS_App_maintenance' AND isactive='Y';
SELECT listagg(EMAIL, ',') WITHIN GROUP (order by Email) INTO v_cc FROM sml.xx_lsp_email_master WHERE email_type = 'CC' AND function_name = 'EMS_App_maintenance' AND isactive='Y';
--SELECT listagg(EMAIL, ',') WITHIN GROUP (order by Email) INTO v_bcc FROM sml.xx_lsp_email_master WHERE email_type = 'BCC' AND function_name = 'EMS_App';
v_html_msg :='<html><head></head><body><p>Dear All,<br/><br/>
Please find the details of Maintenance due list for this week in Equipment Management System</p><br/>
<table border=1>
<tr>
<th> I/S Number </th>
<th> Instrument Name </th>
<th> EQP Serial Number </th>
<th> Ownership </th>
<th> Make </th>
<th> Model Number </th>
<th> Plan </th>
<th> Plan Detail </th>
<th> Activity Id </th>
<th> Maintenance Date </th>
<th> Maintenance Due Date </th>
</tr>';
FOR i IN cur_maintenance_list (p_site_id)
LOOP
--SELECT (trunc(to_date(i."maintenance_date", 'DD-MON-RRRR') - 15)) into l_in_date from dua
v_html_msg :=
v_html_msg
|| '<tr align="left"><td>'
|| i."Instrument_Number"
|| '</td><td>'
|| i."Instrument_Name"
|| '</td><td>'
|| i."EQP_Serial_Number"
|| '</td><td>'
|| i."Ownership"
|| '</td><td>'
|| i."Mfg_Name"
|| '</td><td>'
|| i."Model_Number"
|| '</td><td>'
|| i."Plan"
|| '</td><td>'
|| i."Plan_Detail"
|| '</td><td>'
|| i."Activity_Id"
|| '</td><td>'
|| i."Maintenance_Date"
|| '</td><td>'
|| i."Maintenance_Due_Date"
|| '</td>
</Tr>';
END LOOP;
v_html_msg :=
v_html_msg
|| '</table>
<p>This is a system generated mail. Please do not reply on this mail.
<br/>
<br/>
Regards,
<br/>
EQP.
</p>
</body>
</html>';
send_mail_recipient(p_to => v_to,
p_cc => v_cc,
p_bcc => v_bcc,
p_from => v_from,
p_subject => 'Mail notification about Maintenance Due for this week Equipment Management System',
--p_text_msg => v_txt_msg,
p_html_msg => v_html_msg,
p_smtp_host => 'smtprelay.com');
END;
null;
END XX_EMS_MAIL_PRC_maintenance;
CodePudding user response：
Line 113 is
v_html_msg :=
v_html_msg
|| '<tr align="left"><td>'
...
That variable is declared as
v_html_msg VARCHAR2(32672)
which means that - as you're processing data in a loop - its length exceeds 32672 characters in length.
What to do? Use CLOB instead, or check whether there's really that much data (i.e. maybe cursor returned more rows than you'd expect - check its SELECT statement).
CodePudding user response：
The main problem is that v_html_msg is defined as VARCHAR2(32672) and the loop is trying to add more than 32672 characters there.
The full solution is to change v_html_msg datatype to CLOB. However, it will require you to refactor this procedure XX_EMS_MAIL_PRC_maintenance and other dependent one, like send_mail_recipient.
The workaround may be to send mail with only partial data (of course if it is OK with your business requirements). You can add an "if" in the loop so once the length of v_html_msg is larger than 32000 - stop adding more items there. For example:
FOR i IN cur_maintenance_list (p_site_id)
LOOP
if length(v_html_msg) <30000 then --<<< NEW CODE
v_html_msg :=
v_html_msg
|| '<tr align="left"><td>'
|| i."Instrument_Number"
|| '</td><td>'
|| i."Instrument_Name"
|| '</td><td>'
|| i."EQP_Serial_Number"
|| '</td><td>'
|| i."Ownership"
|| '</td><td>'
|| i."Mfg_Name"
|| '</td><td>'
|| i."Model_Number"
|| '</td><td>'
|| i."Plan"
|| '</td><td>'
|| i."Plan_Detail"
|| '</td><td>'
|| i."Activity_Id"
|| '</td><td>'
|| i."Maintenance_Date"
|| '</td><td>'
|| i."Maintenance_Due_Date"
|| '</td>
</Tr>';
else --<<< NEW CODE
v_html_msg :=
v_html_msg ||'... and more rows exist...';
end if;
END LOOP;
CodePudding user response：
You can refer to below link if you want your VARCHAR2 converted into CLOB.