Oracle Apex 22.2中apex_mail发送后查apex_mail_log报ORA-01403求助

我使用Oracle APEX 22.2版本，通过PL/SQL流程发送邮件，另一流程需依据邮件发送状态执行。我可从apex_mail_log中获取mail_id并显示在页面项中，但用该mail_id查询mail_send_error时，始终报ORA-01403: 未找到数据（邮件实际已发送）。已确认返回的mail_id正确，且SQL语句在SQL Workshop中可正常执行。需验证邮件确实发送后才能执行后续流程，求解决方案。

我的PL/SQL代码如下：

declare

    v_orgname       varchar2(500); 
    v_mailid        number;
    v_mailresult    number; 

begin

    -- select our variables to send with our email
    select name into v_orgname from organizations where keyid = :P277_ORGKEYID;

    v_mailid := apex_mail.send (

        p_to                 => :P277_EMAILTO,
        p_from               => :P277_EMAILFROM,
        p_template_static_id => 'ACTUM_WELCOME_LETTER',
        p_placeholders       => '{"SITENAME":'            || apex_json.stringify( v_orgname ) ||
        ',"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 'test' )) ||
        ',"SUBID":'               || apex_json.stringify( :P277_PARENTID ) ||
        '}' );

    apex_mail.push_queue();

    -- Using the mailid, we can select the result of that email
    select nvl(mail_send_error,0) into v_mailresult from apex_mail_log where mail_id = v_mailid;

    -- This page item is being set so this variable is correct, why is my process saying no data found?
    select v_mailid into :P277_MAILID from dual;
    
    -- If the result is a success, we select 1 into our mail sent variable to tell the login process to run
    if v_mailresult = 0 then
        select 1 into :P277_EMAILSENT from dual;
    else
        select 0 into :P277_EMAILSENT from dual;
    end if; 
    
end;

问题原因 #

调用apex_mail.push_queue()后，邮件发送是异步执行的：APEX只是把邮件加入发送队列，实际发送操作和apex_mail_log的日志写入是后台完成的，不会在当前PL/SQL块执行时立即完成。所以你在push_queue()之后立刻查询apex_mail_log，此时日志记录还未生成，就会触发ORA-01403错误。

解决方案 #

方法1：使用APEX邮件回调（推荐） #

利用APEX的邮件回调功能，在邮件发送完成后自动触发后续逻辑：

1. 创建处理邮件状态的存储过程：

create or replace procedure process_mail_status(
    p_mail_id in number,
    p_status  in varchar2
) as
begin
    -- 根据发送状态执行后续逻辑
    if p_status = 'SUCCESS' then
        -- 邮件发送成功，更新状态或触发流程
        update your_table set email_sent = 1 where mail_id = p_mail_id;
        your后续处理流程();
    else
        -- 发送失败的处理逻辑
        update your_table set email_sent = 0, error_msg = '邮件发送失败' where mail_id = p_mail_id;
    end if;
    commit;
end;
/

2. 修改邮件发送代码，指定回调过程：

v_mailid := apex_mail.send (
    p_to                 => :P277_EMAILTO,
    p_from               => :P277_EMAILFROM,
    p_template_static_id => 'ACTUM_WELCOME_LETTER',
    p_placeholders       => '{"SITENAME":'            || apex_json.stringify( v_orgname ) ||
                            ',"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 'test' )) ||
                            ',"SUBID":'               || apex_json.stringify( :P277_PARENTID ) ||
                            '}',
    p_callback           => 'process_mail_status' -- 绑定回调过程
);
apex_mail.push_queue();

邮件发送完成后，APEX会自动调用process_mail_status，传入邮件ID和状态，你可以在这个过程里安全处理后续流程。

方法2：延迟查询（适合简单场景） #

如果不需要实时触发，可通过定时任务或页面刷新延迟查询状态：

1. 先完成邮件发送并记录mail_id：

declare
    v_orgname       varchar2(500); 
    v_mailid        number;
begin
    select name into v_orgname from organizations where keyid = :P277_ORGKEYID;

    v_mailid := apex_mail.send (
        p_to                 => :P277_EMAILTO,
        p_from               => :P277_EMAILFROM,
        p_template_static_id => 'ACTUM_WELCOME_LETTER',
        p_placeholders       => '{"SITENAME":'            || apex_json.stringify( v_orgname ) ||
                                ',"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 'test' )) ||
                                ',"SUBID":'               || apex_json.stringify( :P277_PARENTID ) ||
                                '}' );

    apex_mail.push_queue();

    select v_mailid into :P277_MAILID from dual;
end;

2. 创建定时作业（比如每分钟执行一次）查询状态：

declare
    v_mailresult number;
begin
    select nvl(mail_send_error,0) into v_mailresult 
    from apex_mail_log 
    where mail_id = :P277_MAILID;

    if v_mailresult = 0 then
        select 1 into :P277_EMAILSENT from dual;
        your后续处理流程();
    else
        select 0 into :P277_EMAILSENT from dual;
    end if;
exception
    when no_data_found then
        -- 日志未生成，等待下次执行
        null;
end;

也可以在页面添加刷新按钮，让用户手动触发状态查询。

方法3：循环等待查询（仅临时调试用） #

如果一定要在当前PL/SQL块中尝试获取状态，可以循环等待，但会阻塞会话，不建议生产环境使用：

declare
    v_orgname       varchar2(500); 
    v_mailid        number;
    v_mailresult    number; 
    v_wait_count    number := 0;
begin
    select name into v_orgname from organizations where keyid = :P277_ORGKEYID;

    v_mailid := apex_mail.send (
        p_to                 => :P277_EMAILTO,
        p_from               => :P277_EMAILFROM,
        p_template_static_id => 'ACTUM_WELCOME_LETTER',
        p_placeholders       => '{"SITENAME":'            || apex_json.stringify( v_orgname ) ||
                                ',"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 'test' )) ||
                                ',"SUBID":'               || apex_json.stringify( :P277_PARENTID ) ||
                                '}' );

    apex_mail.push_queue();

    -- 最多等待5秒（10次*0.5秒）
    while v_wait_count < 10 loop
        begin
            select nvl(mail_send_error,0) into v_mailresult 
            from apex_mail_log 
            where mail_id = v_mailid;
            exit; -- 查询到结果则退出
        exception
            when no_data_found then
                dbms_lock.sleep(0.5); -- 等待0.5秒
                v_wait_count := v_wait_count + 1;
        end;
    end loop;

    select v_mailid into :P277_MAILID from dual;
    
    if v_mailresult = 0 then
        select 1 into :P277_EMAILSENT from dual;
        your后续处理流程();
    else
        select 0 into :P277_EMAILSENT from dual;
    end if; 
    
end;

注意：使用dbms_lock.sleep需要给用户授权：

grant execute on dbms_lock to your_apex_user;

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