From oracle-l-bounce@freelists.org Wed Jul 7 06:32:51 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i67BWak09882 for ; Wed, 7 Jul 2004 06:32:46 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i67BWQ609823 for ; Wed, 7 Jul 2004 06:32:36 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D438F72C6A5; Wed, 7 Jul 2004 06:13:51 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09225-88; Wed, 7 Jul 2004 06:13:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1444C72C67F; Wed, 7 Jul 2004 06:13:51 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 07 Jul 2004 06:12:25 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B7B0A72C600 for ; Wed, 7 Jul 2004 06:12:24 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09225-59 for ; Wed, 7 Jul 2004 06:12:24 -0500 (EST) Received: from mail39.messagelabs.com (mail39.messagelabs.com [193.109.254.243]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id E884B72C5EE for ; Wed, 7 Jul 2004 06:12:23 -0500 (EST) X-VirusChecked: Checked X-Env-Sender: Ian.Cary@ons.gsi.gov.uk X-Msg-Ref: server-9.tower-39.messagelabs.com!1089200169!3063740 X-StarScan-Version: 5.2.10; banners=ons.gsi.gov.uk,-,- X-Originating-IP: [212.137.57.25] Received: (qmail 1282 invoked from network); 7 Jul 2004 11:36:09 -0000 Received: from gateway101.gsi.gov.uk (HELO mail7.gsi.gov.uk) (212.137.57.25) by server-9.tower-39.messagelabs.com with SMTP; 7 Jul 2004 11:36:09 -0000 Received: from statbase.ons.gsi.net ([51.64.32.18] helo=cybergsi.ons.gsi.gov.uk) by mail7.gsi.gov.uk with smtp id 1BiAie-000408-R8 for oracle-l@freelists.org; Wed, 07 Jul 2004 12:36:08 +0100 Subject: Re: Request for information on automated notification. To: oracle-l@freelists.org X-Mailer: Lotus Notes Release 5.0.11 July 24, 2002 Message-ID: From: "Ian Cary" Date: Wed, 7 Jul 2004 12:36:13 +0100 X-MIMETrack: Serialize by Router on NEXTERNAL2/NEWPORT/ONS(5012HF514 | November 19, 2003) at 07/07/2004 12:36:07 MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 4542 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ian.Cary@ons.gsi.gov.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I had a specific requirement to send BLOBS (pdfs in the main) to multiple destinations so had to develop (with much assistance from the URL in my previous posting) the code below which seemed flexible enough to most things with the exception of multiple attachments which I never got round to. Cheers, Ian 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.*; import javax.mail.*; import javax.mail.internet.*; import oracle.jdbc.driver.*; import oracle.sql.*; public class mail { 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; } catch (Exception e) { e.printStackTrace(); rc = 0; } finally { return new oracle.sql.NUMBER(rc); } } 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 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 as language java name 'mail.send(java.lang.String, 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'; / N.B. mlist is a type representing the recipient_type and recipient_address For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk ********************************************************************** Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications ********************************************************************** Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics ********************************************************************** ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------