Re: Send email from PL/SQL procedure

From: Danut Bancea <danut_at_telepages.ca>
Date: Wed, 31 May 2000 14:57:13 -0400
Message-Id: <10514.107348@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_027B_01BFCB10.8113D8A0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

Hi,

There is an Oracel Notes how to send e-mail from PL/SQLusing Java (works nly if you have 8i).

In version 8.1.6 there is a new package dbms_smtp used to send e-mail.

I attached theOracle Notes about how to send e-mail in 8.1.5 version. Can be used in 8.1.6 too.

Danut,

Danut Bancea
Senior Oracle DBA

Tele-Pages Inc.
Phone : (416) 296-9011 Ext. 2271
Fax : (905) 477-7062
E-mail : danut_at_telepages.ca

Original Message ----- From: Dave Drienhuizen <nldrien1_at_ey.nl> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 31, 2000 11:52 AM Subject: Re: Send email from PL/SQL procedure

>
> Well, I didn't follow the old thread but if I'm correct, DBMS_MAIL can
only be
> used if you
> install Oracle Office. Don't know if you have it installed ( I wonder if
it's
> still used).
>
> However, you can check out this link :
>
> http://members.xoom.com/hakan_yuksel/htmls/utilities.htm
>
> There is some example code there that shows you how to e-mail from within
a
> PL/SQL
> procedure, especially on Windows NT.
>
>
>
>
>
> "Daiminger, Helmut" <Helmut.Daiminger_at_gedas.de>@fatcity.com on 31-05-2000
> 14:55:51
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
>
> Hi!
>
> Sorry for asking this question again, but I deleted the old thread...
>
> How do I send an email from within a PL/SQL procedure? I think it is by
> using the dbms_mail package, right?
> How do I create this package? What's the script file's name?
>
> Is this also availabale on 8.0.5 (on NT) or is it an 8.1.x feature?
>
> Thanks,
> Helmut
> ____________________________________
> Helmut Daiminger
> gedas GmbH
> Geschdftsstelle M|nchen
> Ludwigstr. 45, D-85399 Hallbergmoos
> Telefon / phone +49-811-60007-35
> Telefax / fax +49-811-60007-30
> Mailto:Helmut.Daiminger_at_gedas.de
> http://www.gedas.de
>
> *** select 'bye for now' from sys.dual; ***
> ____________________________________
>
>
> --
> Author: Daiminger, Helmut
> INET: Helmut.Daiminger_at_gedas.de
>
> 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).
>
>
>
>
>
>
> ===================================================================
> De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor
> de geadresseerde. Gebruik van deze informatie door anderen dan de
> geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding
> en/of verstrekking van deze informatie aan derden is niet toegestaan.
> Ernst & Young staat niet in voor de juiste en volledige overbrenging van
de
> inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan.
> ===================================================================
> The information contained in this communication is confidential and may be
> legally privileged. It is intended solely for the use of the individual or
> entity to whom it is addressed and others authorised to receive it. If you
> are not the intended recipient you are hereby notified that any
disclosure,
> copying, distribution or taking any action in reliance on the contents of
> this information is strictly prohibited and may be unlawful. Ernst &
> Young is neither liable for the proper and complete transmission of the
> information contained in this communication nor for any delay in its
> receipt.
> ===================================================================
>
>
>
> --
> Author: Dave Drienhuizen
> INET: nldrien1_at_ey.nl
>
> 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).
>

------=_NextPart_000_027B_01BFCB10.8113D8A0 Content-Type: text/plain;

name="Email_RDBMS_usingJSP.txt"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

filename="Email_RDBMS_usingJSP.txt"

=20
Bookmark Fixed font Go to End=20

Doc ID: Note:73321.1=20
Subject: Mail Processing from within JSP/ PL/SQL=20 Type: BULLETIN=20
Status: PUBLISHED=20
Content Type: TEXT/PLAIN=20
Creation Date: 21-JUL-1999=20
Last Revision Date: 05-JAN-2000=20
Language: USAENG=20
=20

Overview

This article contains information on how to process e-mail messages = inside
PL/SQL stored procedures, database triggers, or Java stored procedures = using
Aurora JVM and JavaMail API. Additional information on the JavaMail API = can=20
be found at:

http://java.sun.com/products/javamail/index.html

Note

All working examples in this bulletin were compiled and tested using JDK =

release 1.1.7b for Win32 and Oracle 8i Enterprise Edition 8.1.5.0.0 on Microsoft Windows NT 4.0 SP5.

Procedure

Before loading compressed JAR files into 8.1.5 RDBMS, read Note:74711.1 entitled "java.io.IOException When Running Loadjava Script to Load" which describes how to create JAR files without compression. 8i release =

2 loadjava utility supports compressed JAR, so you do not need to = recreate=20
JAR files.

Load the JavaBeans Activation Framework which can be obtained from:

http://java.sun.com/products/beans/glasgow/jaf.html

into RDBMS:

% loadjava -user sys/<PWD>@<DB> -grant Public -resolve = activation.jar

2. Load the JavaMail API which can be obtained from:

http://java.sun.com/products/javamail/index.html

into RDBMS:

% loadjava -user sys/<PWD>@<DB> -grant Public -resolve mail.jar

3. Create test Java class for sending plain text messages:

   import java.util.*;
   import javax.mail.*;
   import javax.mail.internet.*;

public class zMail{

     static InternetAddress fromAddress;
     static String IncomingServer;
     static Properties propsOutgoing;

     public static void init(
         String OutgoingServerName,
         String IncomingServerName,
         String SenderAddress) {

       try {
         fromAddress =3D new InternetAddress(SenderAddress);
       } catch (MessagingException e) { e.printStackTrace(); }

       IncomingServer =3D IncomingServerName;
       propsOutgoing =3D new Properties();
       propsOutgoing.put("mail.smtp.host", OutgoingServerName);

     }

     public static void sendPlainTextMessage(
         String To,
         String Subject,
         String Body) {

       boolean debug =3D false;

       Session session =3D Session.getDefaultInstance(propsOutgoing, =
null);
       session.setDebug(debug);

       try {
           // create a message
           Message msg =3D new MimeMessage(session);

           // set the from
           msg.setFrom(fromAddress);

           InternetAddress[] address =3D
               {new InternetAddress(To)};
           msg.setRecipients(Message.RecipientType.TO, address);
           msg.setSubject(Subject);

           msg.setContent(Body, "text/plain");
           Transport.send(msg);
       } catch (MessagingException e) {e.printStackTrace(); }
     }

}

4. Compile and load this class into RDBMS:

% javac -classpath =
D:\javamail\activation.jar;D:\javamail\mail.jar;%CLASSPATH% zMail.java

% loadjava -user sys/<PWD>@<DB> -verbose zMail.class

5. Publish our Sample JSP:

create or replace package dbms_mail_service authid CURRENT_USER as

     procedure init
       (OutServer in varchar2, InServer in varchar2, SenderName in =
varchar2);
     procedure SendPlainTextMsg(ToName in varchar2,
                                MsgSubject in varchar2,
                                MsgBody in varchar2);

end dbms_mail_service;
/

create or replace package body dbms_mail_service as

     procedure init
      (OutServer in varchar2, InServer in varchar2, SenderName in =
varchar2)
     as language JAVA
     name 'zMail.init(java.lang.String, java.lang.String, =

java.lang.String)';

     procedure SendPlainTextMsg(ToName in varchar2,
                                MsgSubject in varchar2,
                                MsgBody in varchar2)
     as language JAVA
     name 'zMail.sendPlainTextMessage(java.lang.String, =

java.lang.String, java.lang.String)';

end dbms_mail_service;
/

6. Invoke our Sample JSP from SQL*Plus:

begin

     dbms_mail_service.init(
       'Your.SMTP.Server.Name',
       'Your.POP3-or-IMAP4.Server.Name',
       'JAVAMAIL_at_Oracle.COM');
     dbms_mail_service.SendPlainTextMsg(
       'Your-mail-recipient_at_acme.com',
       'Test from RDBMS',
       'This is a test message.\nPlease ignore.\nBest =

regards,\nJAVAMAIL');

end;
/