Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sending email via pl/ sql using query

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  

      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.  

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US