在存储过程中传递变量

Question

我有一个工作存储过程，如下所示

Declare
l_id number;
begin
l_id := apex_mail.send( 
p_to => '[e-mail]', 
p_from => '[e-mail]', 
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;

这个程序在下载后通过电子邮件发送附件，这正是我想要的。然而，这个问题是，我希望p_to在我的表TEMP_FEES中的新email_address的基础上进行更改。该表一次将有一个记录/电子邮件地址。

我试过

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 => '[e-mail]',
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

但我收到一个错误

ORA-2022：为参数p_mail_id 提供了空值

当我提交表单时。

我能得到关于如何解决这个问题的建议吗？

Answer 1

不确定这是否是问题所在，但在第二个示例中，您没有将过程调用输出分配给变量l_id。所以代码应该如下：-

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 => '[e-mail]',
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;

希望这能有所帮助。

谢谢Arnab P