CODEWITHSUNDEEP
oracle-databaseoracle11goracle-apexoracle-apex-5
user402282
user402282

Reputation: 41

sql statment in email messge utl_mail.send

I am sending emails true oracle apex which works fine. what i need however is to have an sql count statement in the message of the email.

what i have is 

begin
   utl_mail.send(sender     => '[email protected]',
                  recipients =>'[email protected]',
                  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;

which obviously is not working

what i will like to see is 

begin
   utl_mail.send(sender     => '[email protected]',
                  recipients =>'[email protected]',
                  subject    => 'FileRequest',
                  message    =>  5 files from registry '||:p5_filenumber ||''||'  ' ||:p5_filename || ' has now been assigned to the ' || :p5_department || '');

end;

Upvotes: 1

Views: 122

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Compute the value BEFORE sending the mail. Something like this:

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     => '[email protected]',
                recipients => '[email protected]',
                subject    => 'FileRequest',
                message    =>  l_msg);
end;

Upvotes: 3

Related Questions