Validate email that was sent using apex_mail.send and apex_mail

I'm using Oracle Apex version 22.2. I have a pl/sql process where I'm sending an email, and another process runs based on the status of that email being sent. I can select the mail_id from apex_mail_log and display it on one of my page items just fine. But when I try to use that mail_id to select the mail_send_error of that email record, I keep getting ORA-01403: no data found (the email sends regardless of this error). I have validated that it's returning the correct mail_id of the record that was just inserted. I have also validated that my select statement runs just find when ran in SQL Workshop. I need to be able to validate this email was actually sent before I can continue with the other processes, any ideas?

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;

CodePudding user response：

The issue is that the apex_mail.push_queue is an asynchronous call. Your code to be executed afterwards does not wait for the actual mail sending to finish. You can check this in DBA_SYNONYMS where APEX_MAIL synonym leads to APEX_220200.WWV_FLOW_MAIL_API. Unwrap its code and see that it leads to WWW_FLOW_MAIL.PUSH_QUEUE which leads to PUSH_QUEUE_BACKGROUND whose code is:

SYS.DBMS_SCHEDULER.RUN_JOB( JOB_NAME => 'ORACLE_APEX_MAIL_QUEUE', USE_CURRENT_SESSION => FALSE );

So, what you can do, you can run directly that job but with USE_CURRENT_SESSION => TRUE, if you really want to synchronously wait for the execution result. An other option is to check the result in a separate block of code / functionality, and then take UI rendering decisions based on that result.