Can I use variable value in email body when using UTL_mail package in oracle SQL

Question

Context: I'm comparing 2 variables and generating an email with variable values in the body of the email

------------Code-----------------------------

--Variable 1
select sum(sales) 
into V1_sales
from sales

--variable 2
selsct sum(sales)
into v2_sales
from yesretday_sales

if v2_sales > V1_sales
then 
EMAIL_ALERT
(p_mail_host => 'mailhost.abc.com',
p_from => 'sender.abc.com',
p_to => 'rec.abc.com',
p_subject => 'Sales of yesterday is greater than today'
p_message => 'Message: Today's sales which is'@v1_sales 'is lesser than yesretday's sales which is @v2_sales'
endif 


Explanation:
Trying to compare 2 variables and include the variable in the email body, I'm using UTL_MAIL in orcale SQL

Answer 1

Yes, you can.

I suggest you create a variable which will be used to compose message text, and use it when calling the procedure.

Also, if you declare the third variable (v3_sales), it can be used in the message as well.

Something like this:

declare
  v_message varchar2(500);
  v1_sales  number;
  v2_sales  number;
  v3_sales  number;
begin
  ...
  v3_sales := v1_sales - v2_sales;

  v_message := 'Message: Today''s sales which is '           || v1_sales ||
               'is lesser than yesterday''s sales which is ' || v2_sales ||
               '. Difference is ' || v3_sales;

  email_alert (...,
               p_message => v_message
              );
end;

Code you wrote is wrong because of invalid single quote usage; you have to use two consecutive quotes if it is used within the string.