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!

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
951 views