Oracle PL/SQL邮件存储过程TO收件人无法收信问题修复求助

API 探路者
2026-9-26
问题根因
代码核心错误为收件人列表处理逻辑存在覆盖问题：
你先将TO列表转换为分号分隔的字符串存入v_temp_str，下一行直接用CC列表的转换结果覆盖了v_temp_str，导致TO列表完全丢失。后续调用utl_smtp.rcpt（SMTP协议用于指定收件人的指令）时仅传入了CC列表的收件人，因此只有抄送用户能收到邮件，收件人列表用户无法接收。
修复后完整代码
create or replace PROCEDURE        check_ap_reject_list (errbuf VARCHAR2, retcode NUMBER) IS
     CURSOR c1 IS
    SELECT distinct b.NAME AP_ORG, a.ORG_ID ORG_ID, a.source source, 
                    a.INVOICE_ID INVOICE_ID,reject_lookup_code Reject_Reason, 
                    a.INVOICE_NUM Invoice_Num, a.INVOICE_DATE Invoice_Date, a.GL_DATE GL_Date, 
                    a.VENDOR_NAME Vendor_Name, a.VENDOR_SITE_CODE Vendor_Site_Code, 
                    a.INVOICE_AMOUNT INVOICE_AMOUNT, a.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, a.EXCHANGE_RATE EXCHANGE_RATE
          
       FROM apps.ap_interface_rejections, 
            apps.ap_invoices_interface a, 
            apps.hr_all_organization_units b, 
            apps.ap_invoice_lines_interface d
          WHERE (((parent_id = a.invoice_id AND parent_table = 'AP_INVOICES_INTERFACE') OR (parent_id = d.invoice_line_id AND parent_table = 'AP_INVOICE_LINES_INTERFACE')))
           AND a.INVOICE_ID = d.INVOICE_ID
           AND a.ORG_ID = b.ORGANIZATION_ID
         AND a.source = 'RMS'
         ;
     r1 c1%rowtype;
       c utl_smtp.connection;
       v_instance varchar2(9);
       x_email_to varchar2(4000);
       x_email_cc varchar2(4000);
       v_temp_str varchar2(4000);
       
       v_temp     number;
       v_r1_row_num number;
       v_check number;
     PROCEDURE send_data(p_data in varchar2) as
       BEGIN
          utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(p_data || utl_tcp.crlf));
       --fnd_file.put_line(fnd_file.output, p_data);
       END;
       BEGIN
      SELECT count(distinct a.INVOICE_NUM)  
       into v_check 
       FROM apps.ap_interface_rejections, apps.ap_invoices_interface a, apps.hr_all_organization_units b, apps.ap_invoice_lines_interface d
          WHERE (((parent_id = a.invoice_id AND parent_table = 'AP_INVOICES_INTERFACE') OR (parent_id = d.invoice_line_id AND parent_table = 'AP_INVOICE_LINES_INTERFACE')))
           AND a.INVOICE_ID = d.INVOICE_ID
           AND a.ORG_ID = b.ORGANIZATION_ID
         AND a.source = 'RMS';
     IF v_check > 0   THEN
    select name into v_instance from V$DATABASE;
     x_email_to := 'danielliu@abc.com, vincentyuan@abc.com, christinewong@abc.com';
     x_email_cc := 'andycheng@abc.com, tonyfok@abc.com';
     c := utl_smtp.open_connection('LCEX7',25);  
     --utl_smtp.open_connection('smtp.office365.com',587);
     --utl_smtp.helo(c, 'LCEX7');
     utl_smtp.helo(c, 'LCEX7');
     utl_smtp.mail(c, 'lc_ofin@abc.com');
    -- utl_smtp.rcpt( c, x_email_to);
    -- utl_smtp.rcpt( c, x_email_cc);
    -- 修复：合并TO和CC列表到v_temp_str，不再覆盖TO内容
     v_temp_str := replace(x_email_to, ',', ';') || ';' || replace(x_email_cc, ',', ';');
     while length(v_temp_str) > 0 loop
    v_temp := instr(v_temp_str, ';', 1, 1);
    if v_temp > 0 then
      utl_smtp.rcpt(c, trim(substr(v_temp_str, 1, v_temp - 1)));
      v_temp_str := substr(v_temp_str, v_temp + 1, length(v_temp_str));
    else
      utl_smtp.rcpt(c, trim(v_temp_str));
      v_temp_str := null;
    end if;
    end loop;
     utl_smtp.open_data(c);
     utl_smtp.write_data(c,'From: "Oracle Fin (' || v_instance ||') " <lc_ofin@abc.com>' || utl_tcp.CRLF);
     utl_smtp.write_data(c, 'To: ' || x_email_to || utl_tcp.CRLF);
     utl_smtp.write_data(c, 'Cc: ' || x_email_cc || utl_tcp.CRLF);
     utl_smtp.write_data(c, 'Subject: AP invoice reject list ' || to_char(sysdate,'MM/dd/yyyy HH:mm:ss') || utl_tcp.CRLF);
       utl_smtp.write_data(c, 'MIME-Version:' || '1.0' || utl_tcp.CRLF);
       utl_smtp.write_data(c,'Content-Type: ' || 'text/html; charset=utf-8' ||utl_tcp.CRLF);
       utl_smtp.write_data(c, 'Content-Transfer-Encoding: ' || '"8Bit"' || utl_tcp.CRLF);
     
     send_data('<html><body>');
     send_data('<p>Dear All,</p>');
     send_data('<p>Please find the enclosed AP reject list and advise. Thanks!</p>');
     send_data('<table border=''0'' width=''100%'' cellpadding=''1'' cellspacing=''1''>');
     send_data('<tr style=''font-size:75%;font-weight:bold;'' >');
     send_data('<td>AP ORG</td>
                  <td>ORG ID</td>
                  <td>Source</td>
                  <td>INVOICE ID</td>
                  <td>Reject Reason</td>
                  <td>Invoice Num</td>
                  <td>Invoice Date</td>
                  <td>GL Date</td>
                  <td>Vendor Name</td>
                  <td>Vendor Site Code</td>
                  <td>INVOICE AMOUNT</td>
                  <td>INVOICE CURRENCY CODE</td>
                  <td>EXCHANGE RATE</td>');
    v_r1_row_num := 0;
    open c1;
      loop
       fetch c1
      into r1;
     exit when c1%notfound;
     if (mod(v_r1_row_num,2)=0) then
      send_data('<tr style=''font-size:75%;background-color:#E0EBFF;'' >');
     else
      send_data('<tr style=''font-size:75%;'' >');
     end if;   
    send_data('<td>' || r1.AP_ORG || '</td>
             <td>' || r1.ORG_ID || '</td>
             <td>' || r1.source || '</td>
             <td>' || r1.INVOICE_ID || '</td>
             <td>' || r1.Reject_Reason || '</td>
             <td>' || r1.Invoice_Num || '</td>
             <td>' || r1.Invoice_Date || '</td>
             <td>' || r1.GL_Date || '</td>
             <td>' || r1.Vendor_Name || '</td>
             <td>' || r1.Vendor_Site_Code|| '</td>
             <td>' || r1.INVOICE_AMOUNT || '</td>
             <td>' || r1.INVOICE_CURRENCY_CODE || '</td>
             <td>' || r1.EXCHANGE_RATE || '</td></tr>');
     v_r1_row_num := v_r1_row_num + 1;
      END LOOP;
      CLOSE c1;
      send_data('</table>');
      send_data('<p>Best Regards,</p>');
      send_data('<p>Technology Department</p>');
      send_data('</body></html>');
      utl_smtp.close_data(c);
      utl_smtp.quit(c);
     end if ;
      END check_ap_reject_list;
内容的提问来源于stack exchange，提问作者Ben Lee