SQL & PL/SQL

Hi,

Under Oracle APEX and Oracle DB 23.i, I am creating an automation under APEX for sending by mail a PDF document generated by Apex Office Print (AOP). I have this procedure that AOP gives me (and that I modified to my needs):

create or replace procedure send_email_prc(
    p_output_blob      in blob,
    p_output_filename  in varchar2,
    p_output_mime_type in varchar2)
is
  l_id   number;
  l_to   varchar2(200);
  l_from varchar2(200);
begin
  -- in case you want to reference a page item
  l_to := v('P1_EMAIL');
  -- sending an email with an attachment
  l_id := apex_mail.send( 
            p_to   => l_to, 
            p_from => 'mailsender@dummydomain.com', 
            p_subj => 'Your confirmation volunteering certificate is attached to this email',
            p_body => 'You will find attached Your confirmation volunteering certificate.' 
                 || chr(13) || chr(13) 
                 || 'MED''EQUALITEAM'
                 || chr(13) || chr(13) 
                 || 'Do not reply to this email, it will not be treated.');
  apex_mail.add_attachment( 
      p_mail_id    => l_id, 
      p_attachment => p_output_blob, 
      p_filename   => p_output_filename, 
      p_mime_type  => p_output_mime_type) ;
  apex_mail.push_queue;    
end send_email_prc;
/

I have created this automation under APEX:

begin
for x in (Select email, DEPARTURE_DATE FROm ME_VOLUNTEERS where trunc(current_date) = trunc(DEPARTURE_DATE) + 7)
loop
apex_mail.send(
      p_to      => x.email,
      p_from    => 'mailsender@dummydomain.com',
      p_body    => 'You will find attached Your confirmation volunteering certificate.' 
                 || chr(13) || chr(13) 
                 || 'MED''EQUALITEAM'
                 || chr(13) || chr(13) 
                 || 'Do not reply to this email, it will not be treated.',
      p_subj    => 'Your confirmation volunteering certificate is attached to this email'
    );
  end loop;
end;

But for sure it is not invoking the procedure. I would like to invoke the procedure in the loop and passes it the value of x.email. How to proceed?

Best regards.