CODEWITHSUNDEEP
oracle-databaseemail
MITHIYA MOIZ
MITHIYA MOIZ

Reputation: 29

Send Dynamic Emails from Oracle Server

I am able to send emails from Oracle after configuration using below script 

BEGIN
  UTL_MAIL.send(sender     => '[email protected]',  
                recipients => '[email protected]',  
                subject    => 'UTL_MAIL Test',
                message    => 'your procedure will come here ');
END;

I need to attach output of a query to the body. How can I do that?

Upvotes: 1

Views: 1451

Answers (3)

Imran
Imran

Reputation: 167

You can attache a file

begin
     UTL_MAIL.SEND_ATTACH_VARCHAR2
    (
            sender => '[email protected]'
           ,recipients => '[email protected],[email protected],[email protected]'
           ,cc => null
           ,bcc =>  NULL
           ,subject => 'Message from [email protected] '
           ,message => ''
           ,mime_type => 'text/html; charset=us-ascii'
           ,priority => 3
           ,attachment => '<html>
                                            <head>
                                               <p><img src="https://lh6.DWEDEEDEtent.com/-yeca6z1QTB8/To11ZnsaCCI/AAAAAAAAAFc/8tFVIZhl7YM/w674-h399-k/Picture1.jpg" alt="Site Logo" />
                                              <title>data Team</title>
                                              
                                            </head>
                                            <body>
                                                <body style="background-color:yellow;">
                                                
                                                <p>You are using <b><i>bad Cartesian<b><i> on <b>DATABASE<B> </p>
                                             </body>
                                          </html>'
           ,att_inline => TRUE
           ,att_mime_type=>'application/html'
           ,att_filename => 'cartesien.html'  );
      end;

Upvotes: 1

Kevin Burton
Kevin Burton

Reputation: 11936

Try the following (for a single row returned by your select):

DECLARE
    v_message        VARCHAR2(4000);
BEGIN
    SELECT text 
    INTO v_message
    FROM yourtable;

    UTL_MAIL.send(sender     => '[email protected]',  
                recipients => '[email protected]',  
                subject    => 'UTL_MAIL Test',
                message    => v_message);
END;

or if you have several rows you could build up the value of v_message (by replacing the select into with)

FOR rec IN 
(
    SELECT text 
    INTO v_message
    FROM yourtable
)
LOOP
    v_message:=v_message||rec.text;
END;

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30828

I suggest you write some PL/SQL to output the results you want into a VARCHAR2 string and pass this into the message body (ie the "message" parameter in your procedure above)

Upvotes: 2

Related Questions