Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Send email with query results in the message of the email

bigmac704Oct 15 2020

Hello
I am tring to email an email with query results inside the message of the email. Thanks in advanced.

declare  
  l_body clob := q'{  
        Hi %FNAME %LNAME,
        Just a friendly reminder to email %CLIENT today.
        AT %EMAIL


        Thank You!!!,
        ***Please DO NOT REPLY to this Message***
        }'; 
begin  
  l_body := replace(l_body, '%FNAME', c1.FNAME);
  l_body := replace(l_body, '%LNAME', c1.LNAME);
  l_body := replace(l_body, '%EMAIL', c1.EMAIL);
  l_body := replace(l_body, '%CLIENT', c1.CLIENT);
 
  for c1 in (
         SELECT distinct 'Barry' as FNAME, 'doe' as LNAME, 'barry@email.com' AS EMAIL, 'John' as CLIENT
          FROM dual
)  
  loop  
    if c1.EMAIL is not null then  
    htmldb_mail.send(  
      p_to => c1.EMAIL,  
      p_from => 'FAKE@email.com',  
      p_body => l_body,  
      p_subj => 'test');  
    end if;  
  end loop;  
end;
This post has been answered by BluShadow on Oct 15 2020
Jump to Answer

Comments

Post Details

Added on Oct 15 2020
3 comments
903 views