| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Emailing Query Results?
Sorry if this posting is a bit long winded but some people might find it useful.
Cheers,
Ian
I use this piece of code to send simple emails to people - obviously the message can be changed to reflect simple query results.
create or replace procedure system.send_mail (p_recipient in varchar2,
p_subject in varchar2,
p_message in varchar2) as
crlf varchar2(2):= utl_tcp.crlf;
l_mailhost varchar2(255) := '<your mailhoust>' ;
l_mail_conn utl_smtp.connection ;
l_header:= 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss')||crlf||
'From: anybody_at_anywhere'||crlf||
'Subject: '||p_subject||crlf||
'To: '||p_recipient;
utl_smtp.helo(l_mail_conn,l_mailhost);
utl_smtp.mail(l_mail_conn,'anybody_at_anywhere');
utl_smtp.rcpt(l_mail_conn,p_recipient);
utl_smtp.open_data(l_mail_conn);
utl_smtp.write_data(l_mail_conn,l_header);
utl_smtp.write_data(l_mail_conn,crlf||p_message);
utl_smtp.close_data(l_mail_conn);
I also wrote this (with a lot of help from ask tom!) about 18 months ago when I needed to send BLOBS as attachments. I seem to remember having to download and instal the Java mail client from Sun
create or replace and compile
java source named "mail"
as
import java.io.*; import java.sql.*; import java.util.Properties; import java.util.Date; import javax.activation.*;
static String dftMime = "application/octet-stream"; static String dftName = "filename.dat";
public static oracle.sql.NUMBER
send(String from,
oracle.sql.ARRAY tolist,
String subject,
String body,
String SMTPHost,
oracle.sql.BLOB attachmentData,
String attachmentType,
String attachmentFileName)
{
int rc = 0;
try
{
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPHost);
Message msg =
new MimeMessage(Session.getDefaultInstance(props, null));
msg.setFrom(new InternetAddress(from));
ResultSet to = tolist.getResultSet();
for(int i = 0; i < tolist.length();i++) {
to.next();
STRUCT maillist = (STRUCT)to.getObject(2);
Object[] mlist = maillist.getAttributes();
String mtype = (String)mlist[0];
String address = (String)mlist[1];
if (mtype.equals("TO"))
msg.addRecipient(Message.RecipientType.TO,
new InternetAddress(address,false));
else if (mtype.equals("CC"))
msg.addRecipient(Message.RecipientType.CC,
new InternetAddress(address,false));
else if (mtype.equals("BCC"))
msg.addRecipient(Message.RecipientType.BCC,
new InternetAddress(address,false));
}
if ( subject != null && subject.length() > 0 )
msg.setSubject(subject);
else msg.setSubject("(no subject)");
msg.setSentDate(new Date());
if (attachmentData != null)
{
MimeBodyPart mbp1 = new MimeBodyPart();
mbp1.setText((body != null ? body : ""));
mbp1.setDisposition(Part.INLINE);
MimeBodyPart mbp2 = new MimeBodyPart();
String type =
(attachmentType != null ? attachmentType : dftMime);
String fileName = (attachmentFileName != null ?
attachmentFileName : dftName);
mbp2.setDisposition(Part.ATTACHMENT);
mbp2.setFileName(fileName);
mbp2.setDataHandler(new
DataHandler(new BLOBDataSource(attachmentData, type))
);
MimeMultipart mp = new MimeMultipart();
mp.addBodyPart(mbp1);
mp.addBodyPart(mbp2);
msg.setContent(mp);
}
else
{
msg.setText((body != null ? body : ""));
}
Transport.send(msg);
rc = 1;
e.printStackTrace();
rc = 0;
return new oracle.sql.NUMBER(rc);
}
}
// Nested class that implements a DataSource. static class BLOBDataSource implements DataSource {
private BLOB data;
private String type;
BLOBDataSource(BLOB data, String type)
{
this.type = type;
this.data = data;
}
public InputStream getInputStream() throws IOException
{
try
{
if(data == null)
throw new IOException("No data.");
return data.getBinaryStream();
} catch(SQLException e)
{
throw new
IOException("Cannot get binary input stream from BLOB.");
}
}
public OutputStream getOutputStream() throws IOException
{
throw new IOException("Cannot do this.");
}
public String getContentType()
{
return type;
}
public String getName()
{
return "BLOBDataSource";
}
create type mail_to as object (address varchar2(100), type varchar2(2)); create or replace type mlist as table of mail_to;
create or replace function send(p_from in varchar2,
p_to in mlist,
p_subject in varchar2,
p_body in varchar2,
p_smtp_host in varchar2,
p_attachment_data in blob,
p_attachment_type in varchar2,
p_attachment_file_name in varchar2)
return number
oracle.sql.ARRAY,
java.lang.String,
java.lang.String,
java.lang.String,
oracle.sql.BLOB,
java.lang.String,
java.lang.String)
return oracle.sql.NUMBER';
You mail also need some java priviliges along the lines of,
begin
dbms_java.grant_permission(
grantee => '<user>',
permission_type =>
'java.util.PropertyPermission',
permission_name => '*',
permission_action => 'read,write'
);
dbms_java.grant_permission(
grantee => '<user>',
permission_type =>
'java.net.SocketPermission',
permission_name => '*',
permission_action => 'connect,resolve'
);
but this is stretching my memory a bit . I'm sure if you do a search for "java" + "email" on ask tom you should find the page I got most of this from
For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk
The original of this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Energis in partnership with MessageLabs.
On leaving the GSi this email was certified virus-free
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 28 2004 - 10:28:39 CDT
 |
 |