title: Alternate of Varchar2?
body: <p>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 &quot;numeric or value error: character string buffer too small&quot;. I have tried CLOB but still not get any success.</p>
  <p>Error :-</p>
  <pre><code>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  ORA-06512: at &quot;XXCOMM.XX_EMS_MAIL_PRC_MAINTENANCE&quot;, line 113
  ORA-06512: at line 6
  </code></pre>
  <p>Below id my procedure in which I am trying to send email.</p>
  <pre><code>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 &quot;Instrument_Number&quot;,
              d.instrument_name &quot;Instrument_Name&quot;,
              d.eqp_srl_no &quot;EQP_Serial_Number&quot;,
              d.ownership &quot;Ownership&quot;,
              d.mfg_name &quot;Mfg_Name&quot;,
              d.model_no &quot;Model_Number&quot;,
              b.plan &quot;Plan&quot;,
              a.detail &quot;Plan_Detail&quot;,
              c.activity_id &quot;Activity_Id&quot;,
              c.maintenance_date &quot;Maintenance_Date&quot;,
              c.maintenance_due_date &quot;Maintenance_Due_Date&quot;
              
          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 &lt;&gt; '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 :='&lt;html&gt;&lt;head&gt;&lt;/head&gt;&lt;body&gt;&lt;p&gt;Dear All,&lt;br/&gt;&lt;br/&gt;
    Please find the details of Maintenance due list for this week in Equipment Management System&lt;/p&gt;&lt;br/&gt;
    
    
              
    &lt;table border=1&gt;
    &lt;tr&gt;
    &lt;th&gt; I/S Number &lt;/th&gt;
    &lt;th&gt; Instrument Name &lt;/th&gt;
    &lt;th&gt; EQP Serial Number &lt;/th&gt;
    &lt;th&gt; Ownership &lt;/th&gt;
    &lt;th&gt; Make &lt;/th&gt;
    &lt;th&gt; Model Number &lt;/th&gt;  
    &lt;th&gt; Plan &lt;/th&gt;
    &lt;th&gt; Plan Detail &lt;/th&gt;
    &lt;th&gt; Activity Id &lt;/th&gt;
    &lt;th&gt; Maintenance Date &lt;/th&gt;  
    &lt;th&gt; Maintenance Due Date &lt;/th&gt;
    &lt;/tr&gt;';
  
   FOR i IN cur_maintenance_list (p_site_id)
           LOOP
            --SELECT (trunc(to_date(i.&quot;maintenance_date&quot;, 'DD-MON-RRRR') - 15)) into l_in_date from dua
            
              v_html_msg :=
                    v_html_msg
                 || '&lt;tr align=&quot;left&quot;&gt;&lt;td&gt;'
                 || i.&quot;Instrument_Number&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Instrument_Name&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;EQP_Serial_Number&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Ownership&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Mfg_Name&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Model_Number&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Plan&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Plan_Detail&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Activity_Id&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Maintenance_Date&quot;
                 || '&lt;/td&gt;&lt;td&gt;'
                 || i.&quot;Maintenance_Due_Date&quot;
                 || '&lt;/td&gt;
                &lt;/Tr&gt;';
           END LOOP;
           
        v_html_msg :=
                 v_html_msg
              || '&lt;/table&gt;
        &lt;p&gt;This is a system generated mail. Please do not reply on this mail.
         &lt;br/&gt;
         &lt;br/&gt;
         Regards,
         &lt;br/&gt;
         EQP.
        &lt;/p&gt;
      &lt;/body&gt;
    &lt;/html&gt;';
    
     
    
    
    send_mail_recipient(p_to  =&gt; v_to,
              p_cc        =&gt; v_cc,
              p_bcc       =&gt; v_bcc,
              p_from      =&gt; v_from,
              p_subject   =&gt;  'Mail notification about Maintenance Due for this week Equipment Management System',
              --p_text_msg  =&gt; v_txt_msg,
              p_html_msg  =&gt; v_html_msg,
              p_smtp_host =&gt; 'smtprelay.com'); 
              
               
  
  END;
  null;
  END XX_EMS_MAIL_PRC_maintenance;
  </code></pre>