passing a variable in a stored procedure-CodePudding

I have a working stored procedure as seen below

 Declare
      l_id number;
    begin
      l_id := apex_mail.send( 
        p_to => '[email protected]', 
        p_from => '[email protected]', 
        p_subj => 'Mail from APEX with attachment', 
        p_body => 'Please review the attachment.', 
        p_body_html => 'Please review the attachment.'
      );
      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
      );
     end;

This procedure emails an attachment after it is downloaded, which is what I want. This issue however is, I will like the p_to to change based on the new email_address in my table called TEMP_FEES. The table will have one record/email address at a time.

I have tried

Declare
  l_id number;
begin
    FOR m IN (SELECT parent_email
            FROM TEMP_FEES
           )
LOOP
  apex_mail.send( p_to => m.parent_email,
                  p_from => '[email protected]',
                  p_body => 'test',
                  p_subj => 'invoice'
  );
  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
  );
 
  END LOOP;  
end

But I am getting an error

ORA-20022: Null value supplied for parameter P_mail_id

when I submit the form.

Can I get any suggestions on how to solve this?

CodePudding user response：

Not sure if this could be the issue, but you are not assigning the procedure call output to the variable l_id in your second example. So the code should be as follows:-

Declare
  l_id number;
begin
    FOR m IN (SELECT parent_email
            FROM TEMP_FEES
           )
LOOP
  l_id := apex_mail.send( p_to => m.parent_email,
                  p_from => '[email protected]',
                  p_body => 'test',
                  p_subj => 'invoice'
  );
  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
  );
 
  END LOOP;  
end;

Hope this helps.

Thanks Arnab P