RE: sending email via pl/ sql using query

From: Thomas, Kevin <Kevin.Thomas_at_calanais.com>
Date: Fri, 01 Feb 2002 00:35:19 -0800
Message-ID: <F001.00402E74.20020201002017@fatcity.com>

Suhen,  

You should be able to pass in the values returned from your query as a 'variable' to the utl_smtp.write_data command. I've used an example from the asktom.oracle.com website (
http://asktom.oracle.com/pls/ask/f?p=4950:8:457115::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:2118740358226,%7Butl_smtp%7D <http://asktom.oracle.com/pls/ask/f?p=4950:8:457115::NO::F4950_P8_DISPLAYID, F4950_P8_CRITERIA:2118740358226,%7Butl_smtp%7D> ) and added my own bits to it. Hopefully it makes sense, if not try the website...for more info.  

So for example:
create or replace procedure send_mail
as

   CURSOR c_query
  SELECT data
    FROM table;

   l_mailhost varchar2(255) := '10.228.1.75' ;
   l_mail_conn utl_smtp.connection ;
   l_message VARCHAR2(n) := 'This mail has been automatically generated

by...'; -- for example

begin  

• Open the cursor in a for loop
  • this enables you to build up your l_message variable
  • to contain all the data you want to send.
  • Of course, you can add more in to this to add static information
  • such as l_message could initially contain an opening line like
  • 'This mail has been automatically generated by...' and then
  • you just build up the rest of the message like so, below...I think!
    --
    FOR r_query IN c_query LOOP

      l_message := l_message || CHR(13) || r_query.data_element;  

  END LOOP;

   l_mail_conn := utl_smtp.open_connection(l_mailhost, 25) ;
   utl_smtp.helo(l_mail_conn, l_mailhost) ;
   utl_smtp.mail(l_mail_conn, p_sender) ;
   utl_smtp.rcpt(l_mail_conn, p_recipient ) ;
   utl_smtp.open_data(l_mail_conn) ;
   utl_smtp.write_data(l_mail_conn, p_message ) ;

   utl_smtp.close_data(l_mail_conn) ;
   utl_smtp.quit(l_mail_conn );
   dbms_output.put_line('Message send to the user successfully') ; end ;    

Hope this helps,
Cheers,
Kev.
"my computer beat me at chess but I won when it came to kick boxing."

---

Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
(2nd Floor East - Weirs Building)
Tel: 0141 568 2377
Fax: 0141 568 2366
http://www.calanais.com <http://www.calanais.com/>  

-----Original Message-----
Sent: 01 February 2002 03:25
To: Multiple recipients of list ORACLE-L

List,  

I am trying to send email from Oracle when a table is updated.

The message must be a dynamic one, so hard coded message will not be allowed.  

I have created a trigger when the table is updated - that works

I am using UTL_SMTP to send emails. - that works

However this procedure (send_mail) only has hard-coded messages. - I need it to be a dynamic message, from a query which I already have.

The query will return more than 1 row.  

• So the trigger executes when a column is updated.
• A query would be executed giving the required information to be mailed.
• email program must kick off, emailing the results of the above query.

Any ideas how to do this.  

Thanks & Regards

Suhen    

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: Kevin.Thomas_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Feb 01 2002 - 02:35:19 CST