时间戳

首页 > 解决方案 > 电子邮件消息中的 sql 语句 utl_mail.send

oracle - 电子邮件消息中的 sql 语句 utl_mail.send

问题描述

我正在发送电子邮件，真正的 oracle apex 工作正常。然而，我需要的是在电子邮件的消息中有一个 sql count 语句。

我所拥有的是

begin
   utl_mail.send(sender     => 'a@test.com',
                  recipients =>'a@test.com',
                  subject    => 'FileRequest',
                  message    =>  'select count(filenumber) where status is assigned' files from registry '||:p5_filenumber ||''||'  ' ||:p5_filename || ' has now been assigned to the ' || :p5_department || '');

end;

这显然是行不通的

我想看到的是

begin
   utl_mail.send(sender     => 'a@test.com',
                  recipients =>'a@test.com',
                  subject    => 'FileRequest',
                  message    =>  5 files from registry '||:p5_filenumber ||''||'  ' ||:p5_filename || ' has now been assigned to the ' || :p5_department || '');

end;

标签： oracleoracle11goracle-apexoracle-apex-5

解决方案

在发送邮件之前计算值。像这样的东西：

declare
  l_cnt number;
  l_msg varchar2(200);
begin
  -- select number you're interested in
  select count(*)
    into l_cnt
    from some_table
    where some_conditions;

  -- compose the message
  l_msg := l_cnt ||' files from registry ...';

  -- send mail
  utl_mail.send(sender     => 'a@test.com',
                recipients => 'a@test.com',
                subject    => 'FileRequest',
                message    =>  l_msg);
end;

推荐阅读