| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> UTL_SMTP to send email
<span style='font-size:10.0pt;
font-family:Arial'>List,
<span style='font-size:10.0pt;
font-family:Arial'>
<span style='font-size:10.0pt;
font-family:Arial'>I am sending email from the database.
<span style='font-size:10.0pt;
font-family:Arial'>I have UTL_SMTP setup which works fine.
<span style='font-size:10.0pt;
font-family:Arial'>
<span style='font-size:10.0pt;
font-family:Arial'>However in the message field I would like to query a table (using
a select)
<span
style='font-size:10.0pt;font-family:Arial'>rather<font
face=Arial> than a hardcoded
message.
<span style='font-size:10.0pt;
font-family:Arial'>
<span style='font-size:10.0pt;
font-family:Arial'>Not sure how to do this though.
<span style='font-size:10.0pt;
font-family:Arial'>Anyone got any pointers will be greatly appreciated.
<span style='font-size:10.0pt;
font-family:Arial'>
<span style='font-size:10.0pt;
font-family:Arial'>PROCEDURE send_mail (
<span
style='font-size:10.0pt;font-family:Arial'>sender<font
face=Arial> IN VARCHAR2,
<span
style='font-size:10.0pt;font-family:Arial'>recipient<font
face=Arial> IN VARCHAR2,
<span
style='font-size:10.0pt;font-family:Arial'>subject<font
face=Arial> IN VARCHAR2,
<span
style='font-size:10.0pt;font-family:Arial'>message<font
face=Arial> IN VARCHAR2
<span style='font-size:10.0pt;
font-family:Arial'>)
<span style='font-size:10.0pt;
font-family:Arial'>IS
<font size=2
face=Arial>mailhost<font
face=Arial> VARCHAR2 (30) := 'www';
<span
style='font-size:10.0pt;font-family:Arial'>mail_conn<font
face=Arial> utl_smtp.connection;
<font size=2
face=Arial>crlf<font
face=Arial> VARCHAR2 (2) := CHR (13)
<span style='font-size:10.0pt;
font-family:Arial'>|| CHR (10);
<font size=2
face=Arial>mesg<font
face=Arial> VARCHAR2 (1000);
<span style='font-size:10.0pt;
font-family:Arial'>BEGIN
<span
style='font-size:10.0pt;font-family:Arial'>mail_conn<span
class=GramE> :<font
face=Arial>= utl_smtp.open_connection
(mailhost, 25);
<font size=2
face=Arial>mesg<font
face=Arial> := 'Date: '
<span style='font-size:10.0pt;
font-family:Arial'>|| TO_CHAR (SYSDATE, 'dd Mon <span
class=SpellE>yy hh24:mi:ss')
<span style='font-size:10.0pt;
font-family:Arial'>|| crlf
<span style='font-size:10.0pt;
font-family:Arial'>|| 'From: <'
<span style='font-size:10.0pt;
font-family:Arial'>|| sender
<span style='font-size:10.0pt;
font-family:Arial'>|| '>'
<span style='font-size:10.0pt;
font-family:Arial'>|| crlf
<span style='font-size:10.0pt;
font-family:Arial'>|| 'Subject: '
<span style='font-size:10.0pt;
font-family:Arial'>|| subject
<span style='font-size:10.0pt;
font-family:Arial'>|| crlf
<span style='font-size:10.0pt;
font-family:Arial'>|| 'To: '
<span style='font-size:10.0pt;
font-family:Arial'>|| recipient
<span style='font-size:10.0pt;
font-family:Arial'>|| crlf
<span style='font-size:10.0pt;
font-family:Arial'>|| ''
<span style='font-size:10.0pt;
font-family:Arial'>|| crlf
<span style='font-size:10.0pt;
font-family:Arial'>|| message;
<span
style='font-size:10.0pt;font-family:Arial'>utl_smtp.helo<font
face=Arial> (mail_conn,
mailhost);
<span
style='font-size:10.0pt;font-family:Arial'>utl_smtp.mail<font
face=Arial> (mail_conn,
sender);
<span
style='font-size:10.0pt;font-family:Arial'>utl_smtp.rcpt<font
face=Arial> (mail_conn,
recipient);
<span
style='font-size:10.0pt;font-family:Arial'>utl_smtp.data<font
face=Arial> (mail_conn,
mesg);
<span
style='font-size:10.0pt;font-family:Arial'>utl_smtp.quit<font
face=Arial> (mail_conn);
<span style='font-size:10.0pt;
font-family:Arial'>END;
<span style='font-size:10.0pt;
font-family:Arial'>
<span style='font-size:10.0pt;
font-family:Arial'>
<span style='font-size:10.0pt;
font-family:Arial'>Thanks & Regards
<span style='font-size:10.0pt;
font-family:Arial'>Suhen
Received on Mon Dec 03 2001 - 17:44:13 CST
 |
 |