PL/SQL捕获UTL_SMTP发件错误并存入Error表实现方案

两次尝试失败的根因 #

• 首次尝试直接在循环外层写异常分支：PL/SQL的异常块一旦触发，会直接退出当前块的执行逻辑，只要单个收件人发信报错，整个循环就会终止，既无法继续处理后续收件人，若变量作用域覆盖不全，也无法正确获取出错行的收件人参数。
• 第二次嵌套块触发ORA-06550：apex_exec.get_varchar2 仅能在apex_exec组件执行逐行DML/处理的专属上下文中取列值，页面按钮触发的普通PL/SQL流程没有这个上下文，直接调用会触发列不存在错误；若将该函数直接嵌入INSERT语句未做本地变量中转，也会触发PL/SQL语法校验错误。

前置准备 #

确认employee_error错误表至少包含以下字段：

• err_id：数值型主键，可配置自增序列/标识列
• email_addr：VARCHAR2(200)，存储出错的收件人邮箱地址
• err_msg：VARCHAR2(4000)，存储发信失败的错误详情
• create_time：DATE类型，记录错误生成时间，可设置默认值SYSDATE

正确实现代码 #

核心逻辑是给单个收件人的发信流程单独嵌套BEGIN-EXCEPTION块，循环启动时先将当前行收件人邮箱赋值到本地变量，异常触发时直接读取本地变量写入错误表，全程不在异常块中读取报表动态上下文，保证单个收件人发信失败不会中断整个循环。

DECLARE
  -- 定义游标匹配交互式报表的数据源查询逻辑，过滤条件和报表保持一致
  CURSOR cur_recipient IS
    SELECT emp_email, emp_id, emp_name
    FROM your_report_source_table -- 替换为交互式报表绑定的实际数据源
    WHERE dept_id = :Pxx_CURRENT_DEPT; -- 替换为页面实际用到的筛选参数

  v_current_email VARCHAR2(200); -- 本地变量暂存当前循环的收件人邮箱
  v_emp_id        NUMBER;
  v_emp_name      VARCHAR2(100);
  v_smtp_conn     UTL_SMTP.CONNECTION;
  v_error_msg     VARCHAR2(4000);
BEGIN
  OPEN cur_recipient;
  LOOP
    FETCH cur_recipient INTO v_current_email, v_emp_id, v_emp_name; -- 变量和游标查询字段一一对应
    EXIT WHEN cur_recipient%NOTFOUND;

    -- 单个收件人发信逻辑单独包裹内层块，异常仅中断当前收件人流程
    BEGIN
      -- 原有UTL_SMTP发信逻辑
      v_smtp_conn := UTL_SMTP.OPEN_CONNECTION('你的SMTP服务地址', 25);
      UTL_SMTP.HELO(v_smtp_conn, '你的SMTP服务域名');
      UTL_SMTP.MAIL(v_smtp_conn, '发件人邮箱地址');
      UTL_SMTP.RCPT(v_smtp_conn, v_current_email); -- 无效邮箱会在此处抛错
      UTL_SMTP.DATA(v_smtp_conn, '邮件正文内容');
      UTL_SMTP.QUIT(v_smtp_conn);

    EXCEPTION
      WHEN OTHERS THEN
        v_error_msg := SQLERRM;
        -- 异常时主动关闭SMTP连接，避免连接泄漏
        BEGIN
          UTL_SMTP.QUIT(v_smtp_conn);
        EXCEPTION
          WHEN OTHERS THEN NULL;
        END;
        -- 写入错误表，直接用提前暂存的本地变量，不调用apex_exec类方法取上下文
        INSERT INTO employee_error(email_addr, err_msg, create_time)
        VALUES (v_current_email, v_error_msg, SYSDATE);
        COMMIT; -- 单独提交错误记录，避免后续事务回滚丢失错误日志
    END;
    -- 内层块结束，无论当前收件人是否发信成功，都自动进入下一次循环
  END LOOP;
  CLOSE cur_recipient;
END;

如果是基于APEX原生的行选择（勾选复选框）做批量发信，把游标遍历替换为APEX_APPLICATION.G_F01数组遍历即可，核心的内层异常块、本地变量暂存邮箱的逻辑保持不变。

避坑要点 #

• 禁止在页面按钮触发的PL/SQL流程中调用apex_exec.get_varchar2取报表行值：该方法无对应执行上下文时100%触发列不存在错误，直接通过游标/行数组就能拿到所有需要的字段值。
• 禁止将异常块写在循环外层：外层异常块触发后会直接终止整个循环，无法实现容错继续执行的需求。
• 发信异常后必须主动关闭SMTP连接：否则会造成数据库SMTP连接泄漏，累计到阈值后后续所有发信请求都会失败。
• 错误日志写入后必须单独提交：避免后续发信逻辑的事务回滚将已写入的错误记录清除。

内容的提问来源于stack exchange，提问作者kiric8494