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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
