Solved

Sending Email From Oracle

Posted on 2001-07-10
13
1,972 Views
Last Modified: 2009-07-29
All,

The following is the system of my database:

Oracle Server: 8.0.5
Operating System: Windows NT
Mail Server: MS Outlook.

I am trying to send email from Oracle. I can successfully send email from Oracle Alert using MS Outlook. Does anyone have experience to send email from procedures or triggers by calling MS Outlook? I prefer to use procedure or trigger to send mail.


Thanks!

Max


0
Comment
Question by:xiangmin
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6269345
Use Following Steps (a jave procedure is to be created in the database. assume that this procedure is in 'mail.sql'The code of mail.sql is given in the end).

All the jar and sql files mentioned here , u can get them from me, just drop me an email at 'Usama@faujisoft.com'


1.      Upload the mail8i.jar and activation8i.jar files to the
database using 'loadjava'. These need to be uploaded using the
SYS user since they have 'protected' Java packages that regular
users cannot upload. These two jar files come packaged from Sun
in ZIP files but the are compressed jar files which loadjava
cannot handle. Therefore, I had to un-jar them then re-jar them
without compression. Contanct me for right jar
files.
Since they are owned by SYS, access needs to be granted to
PUBLIC or (whomever). Here is the load syntax:

loadjava -u sys/change_on_install -o -r -v -g public
activation8i.jar
loadjava -u sys/change_on_install -o -r -v -g public
mail8i.jar

2.Grant JAVASYSPRIV to callers of the PL/SQL API function:

        sqlplus system/manager;
        grant JAVASYSPRIV to <user>;

3.      Load the Java code into the database:

        loadjava -u <user>/<pw> -o -r -f -v mail.java

4.      Load the PL/SQL package:
        sqlplus <user>/<pw> @mail.sql

        dev8i> desc mail
        FUNCTION SEND RETURNS NUMBER
         Argument Name    Type                 In/Out Default?
         ---------------- -------------------- ------ --------
         P_FROM           VARCHAR2             IN
         P_TO             VARCHAR2             IN
         P_CC             VARCHAR2             IN
         P_BCC            VARCHAR2             IN
         P_SUBJECT        VARCHAR2             IN
         P_BODY           VARCHAR2             IN
         P_SMTP_HOST      VARCHAR2             IN

5.    Here is a chunk of PL/SQL code that shows it in action:
      declare
         ret_code number;
      begin
         ret_code := mail.send(
                       p_from => 'Usama@faujisoft.com',
                       p_to => 'Usama@faujisoft.com,
                       p_subject => 'Urgent Notice!',
                       p_body => 'Your car lights are on....',
                       p_smtp_host => '210.56.8.20');
           if ret_code = 1 then
           dbms_output.put_line ('Successful sent message...');
         else
           dbms_output.put_line ('Failed to send message...');
         end if;
      end;


THE CODE OF MAIL.SQL
set define off
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 {
  // ****************************************************************************************
  // Simple method to send a message. Will return a 1 if successful and a 0 otherwise. This
  // is very basic but you could dynamically build the message body from data you pull from
  // the DBMS. You can also make it intelligently capture the exceptions and report back to
  // the caller what the exact error is (invalid recipient address, no transport, etc).
  // ****************************************************************************************
  public static NUMBER send(String from,
                            String to,
                            String cc,
                            String bcc,
                            String subject,
                            String body,
                            String SMTPHost,
                            BLOB   attachmentData,
                            String attachmentType,
                            String attachmentFileName) {
    int rc = 0;
    boolean hasAttachments = (attachmentData != null);  // Do we need to attach a file?
    String content = (body != null ? body : "");        // Make sure we got something for the body.

    try {
      Properties props = System.getProperties();
      props.put("mail.smtp.host", SMTPHost);
      Message msg = new MimeMessage(Session.getDefaultInstance(props, null));

      // Who is this message from???
      msg.setFrom(new InternetAddress(from));

      // Set the to/cc/bcc values if we have them
      if (to != null && to.length() > 0)
        msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(to, false));
      if (cc != null && cc.length() > 0)
        msg.setRecipients(Message.RecipientType.CC, InternetAddress.parse(cc, false));
      if (bcc != null && bcc.length() > 0)
        msg.setRecipients(Message.RecipientType.BCC, InternetAddress.parse(bcc, false));

      // Set the subject. Default the value if necessary.
      msg.setSubject((subject != null || subject.length() > 0) ? subject : "(no subject)");
      msg.setSentDate(new Date());

      if (hasAttachments) {
        // Create the first part which is the message itself.
        MimeBodyPart mbp1 = new MimeBodyPart();
        mbp1.setText(content);
        mbp1.setDisposition(Part.INLINE);

        // Create the second part. If the caller didn't indicate what type of attachment this
        // is, then default it to a generic binary stream. If we didn't get a file name then
        // default that as well.
        MimeBodyPart mbp2 = new MimeBodyPart();
        String type = (attachmentType != null ? attachmentType : "application/octet-stream");
        String fileName = (attachmentFileName != null ? attachmentFileName : "filename.dat");
        mbp2.setDisposition(Part.ATTACHMENT);
        mbp2.setFileName(fileName);
        mbp2.setDataHandler(new DataHandler(new BLOBDataSource(attachmentData, type)));

        // Create a container then add the two parts. Next, add the container to the message.
        MimeMultipart mp = new MimeMultipart();
        mp.addBodyPart(mbp1);
        mp.addBodyPart(mbp2);
        msg.setContent(mp);
      }
      else {
        // No attachment so just make a simple message
        msg.setText(content);
      }

      Transport.send(msg);
      rc = 1;
    } catch (Exception e) {
      e.printStackTrace();
      rc = 0;
    } finally {
      return new 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";
    }
  }
}
/
show errors
/
create or replace function send(
    p_from                  in varchar2,
    p_to                    in varchar2,
    p_cc                    in varchar2,
    p_bcc                   in varchar2,
    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,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    oracle.sql.BLOB,
    java.lang.String,
    java.lang.String) return oracle.sql.NUMBER';

/
show errors
/
set serveroutput on size 1000000
/

0
 
LVL 4

Accepted Solution

by:
jtrifts earned 80 total points
ID: 6269690
Or you can try the following:

Please read this using courier new fonts - This will format it better.
Article-ID: <Note:61736.1>
Alias: OLS:13000147.6
Circulation: NOT KROWN Controlled (LIMITED)
Folder: client.Dev2000.Forms.V4
Topic: OLSBB SQL*FORMS
Platform: GENERIC Generic issue
Subject: PROGRAMMATICALLY SENDING A FAX/EMAIL VIA MS EXCHANGE
Author: DMETCALF
Creation-Date: 18-SEP-1997 00:00:00
Modified-Date: 18-SEP-1997 00:00:00
Document-Type: BULLETIN
Attachments: NONE


Document ID: 13000147.6
Title: Programmatically sending a Fax/Email via Microsoft
Exchange
Creation Date: 26 May 1997
Last Revision Date: 26 May 1997
Expiration Date: 26 May 1998
Revision Number: 0
Distribution Code: External
Category: SQL*Forms
Product: Developer/2000 Forms
Product Version: V45
Platform: Windows95, WindowsNT 4.0
Information Type: Advisory
Impact: Medium
Abstract: This bulletin explains how to programmatically send
a
fax/email from a Forms/Reports application via
Microsoft
Exchange using OLE2
Keywords:
DEVELOPER/2000;FORMS;REPORTS;FAX;EMAIL;EXCHANGE;OLE;OLE2
________________________________________________________________________
_______

Programmatically sending a Fax/Email via Microsoft Exchange
==========================================================


INTRODUCTION:
-------------

This bulletin explains how to programmatically send a fax/email message
from
a Forms/Reports application via Microsoft Exchange without any kind of
user
interaction. It shows the general usage of the 'Mail' package as well as
a
fully coded Forms sample application.

The concept of OLE (Object Linking and Embedding) automation is used to
control
the OLE server application (Microsoft Exchange) using the client
application.
The client in this case may be a Developer/2000 Forms or Reports
application.
It uses the objects and methods exposed by the OLE Messaging Library
which are
much more robust than the MSMAPI OCX controls and allow access to many
more MAPI

properties.

Oracle provides support for OLE automation in its applications by means
of the
OLE2 built-in package. This package contains object types and built-ins
for
creating and manipulating the OLE objects. Some of these built-ins for
e.g.
OLE2.create_obj, OLE2.invoke, OLE2.set_property have been extensively
used in
the code.


GENERAL USAGE:
--------------

The Mail package contains three procedures:

1. Procedure Mail.logon( profile IN varchar2 default NULL);
----------
Use this procedure to logon to the MS Exchange mail client. The
procedure
takes a character argument which specifies the Exchange Profile to
use for
logon. Passing a NULL argument to the logon procedure brings up a
dialog
box which asks you to choose a profile from a list of valid profiles
or
create a new one if it doesn't exist.


2. Procedure Mail.send(
--------- Recipient IN varchar2,
Subject IN varchar2 default NULL,
Text IN varchar2 default NULL,
Attachment IN varchar2 default NULL
);

This is the procedure that actually sends the message and
attachments, if
any, to the recipient. The recipient may be specified directly as a
valid
email address or as an alias defined in the address book. If the
message is
intended for a fax recipient then a valid alias must be used that is
defined
as a fax address in the address book.


3. Procedure Mail.logoff;
-----------

This procedure closes the Exchange session and deallocates the
resources
used by the OLE automation objects.



SAMPLE FORMS APPLICATION:
-------------------------

1. Create the Mail Package using the following two Program Units:

(a) Mail Package Spec
(b) Mail Package Body


Mail Package Spec:
------------------

PACKAGE Mail IS

session OLE2.OBJ_TYPE; /* OLE object handle */
args OLE2.LIST_TYPE; /* handle to OLE argument list */

procedure logon( Profile IN varchar2 default NULL );

procedure logoff;

procedure send( Recp IN varchar2,
Subject IN varchar2,
Text IN varchar2,
Attch IN varchar2
);

END;



Mail Package Body:
------------------

PACKAGE BODY Mail IS

session_outbox OLE2.OBJ_TYPE;
session_outbox_messages OLE2.OBJ_TYPE;
message1 OLE2.OBJ_TYPE;
msg_recp OLE2.OBJ_TYPE;
recipient OLE2.OBJ_TYPE;
msg_attch OLE2.OBJ_TYPE;
attachment OLE2.OBJ_TYPE;


procedure logon( Profile IN varchar2 default NULL )is
Begin

session := ole2.create_obj('mapi.session'); /* create the session
object */
args := ole2.create_arglist;

ole2.add_arg(args,Profile); /* Specify a valid profile name
*/
ole2.invoke(session,'Logon',args); /* to avoid the logon dialog box
*/
ole2.destroy_arglist(args);

End;


procedure logoff is
Begin

ole2.invoke(session,'Logoff'); /* Logoff the session and
deallocate the */

/* resources for all the OLE
objects */

ole2.release_obj(session);
ole2.release_obj(session_outbox);
ole2.release_obj(session_outbox_messages);
ole2.release_obj(message1);
ole2.release_obj(msg_recp);
ole2.release_obj(recipient);
ole2.release_obj(msg_attch);
ole2.release_obj(attachment);

End;



procedure send( Recp IN varchar2,
Subject IN varchar2,
Text IN varchar2,
Attch IN varchar2
)is
Begin

/* Add a new object message1 to the outbox */

session_outbox := ole2.get_obj_property(session,'outbox');
session_outbox_messages :=
ole2.get_obj_property(session_outbox,'messages');
message1 := ole2.invoke_obj(session_outbox_messages,'Add');

ole2.set_property(message1,'subject',Subject);
ole2.set_property(message1,'text',Text);

/* Add a recipient object to the message1.Recipients collection */

msg_recp := ole2.get_obj_property(message1,'Recipients');
recipient := ole2.invoke_obj(msg_recp,'add') ;

ole2.set_property(recipient,'name',Recp);
ole2.set_property(recipient,'type',1);
ole2.invoke(recipient,'resolve');

/* Add an attachment object to the message1.Attachments collection */

msg_attch := ole2.get_obj_property(message1,'Attachments');
attachment := ole2.invoke_obj(msg_attch,'add') ;

ole2.set_property(attachment,'name',Attch);
ole2.set_property(attachment,'position',0);
ole2.set_property(attachment,'type',1); /* 1 => MAPI File Data
*/
ole2.set_property(attachment,'source',Attch);

/* Read the attachment from the file */

args := ole2.create_arglist;
ole2.add_arg(args,Attch);
ole2.invoke(attachment,'ReadFromFile',args);
ole2.destroy_arglist(args);

args := ole2.create_arglist;
ole2.add_arg(args,1); /* 1 => save copy */
ole2.add_arg(args,0); /* 0 => no dialog */

/* Send the message without any dialog box, saving a copy in the Outbox
*/

ole2.invoke(message1,'Send',args);

ole2.destroy_arglist(args);
message('Message successfully sent');

End;

END;




2. Create a block called MAPIOLE with the following canvas layout:

|-----------------------------------------------------------------------
|
|
|
| Exchange Profile: |====================|
|
|
|
| To: |============================|
|
|
|
| Subject: |============================|
|
|
|
| Message: |============================|
|
| | |
|
| | |
|
| | |
|
| | |
|
| | |
|
| |============================|
|
| |-----------|
|
| Attachment: |============================| | SEND |
|
| |-----------|
|
|-----------------------------------------------------------------------
|


The layout contains 5 text-itmes:

- Profile
- To
- Subject
- Message (multiline functional property set to true)
- Attach

and a 'Send' button with the following WHEN-BUTTON-PRESSED trigger:
---------------------------
mail.logon(:profile);
mail.send(:to,:subject,:message,:attch);
mail.logoff;



CONCLUSION:
-----------

This bulletin explained how to create a sample Forms application which
can send
a fax/email using OLE automation. The same concept/code can be extended
to
programmatically fax/email an Oracle Report without any user
interaction. In
order to fax a report, first generate the report output as a PDF file
and then
send this to the fax recipient as an attachment using the same Mail
Package.



REFERENCES:
-----------

1. Developer/2000 White Paper: Enhancing your Forms Applications with
OLE 2.0
2. Technical Bulletin: Object Linking and Embedding (OLE) in Oracle
Forms

________________________________________________________________________
_______
Oracle India Product Engineering
Center
Electronic Services
Group



WebIV:View NOTE










Article-ID: <Note:47498.1>
Circulation: PUBLISHED (EXTERNAL)
Folder: client.Dev2000.Forms.V4
Topic: Oracle Forms Hints - MS Windows Specific (OLE, DDE
etc.)
Platform: GENERIC Generic issue
Subject: Integrating Forms with MAPI on NT 4.0 & Windows 95
Creation-Date: 10-OCT-1997 12:03:34
Modified-Date: 14-MAY-1998 11:54:40
Revision: 1
Document-Type: FAQ
Attachments: NONE


Document ID: 13000147.6
Title: Programmatically sending a Fax/Email via Microsoft
Exchange
Author: Varun Puri (vpuri.in)
Department: IN: IPEC-ESG
Creation Date: 26 May 1997
Last Revision Date: 04 Feb 1998
Expiration Date: 26 May 1998
Revision Number: 3
Distribution Code: External
Category: SQL*Forms
Product: Developer/2000 Forms
Product Version: V45
Platform: Windows 95, WindowsNT 4.0
Information Type: Advisory
Impact: Medium
Abstract: This bulletin explains how to programmatically send
a
fax/email from a Forms/Reports application via
Microsoft
Exchange using OLE2
Keywords:
DEVELOPER/2000;FORMS;REPORTS;FAX;EMAIL;EXCHANGE;OLE;OLE2
________________________________________________________________________
_______

Programmatically sending a Fax/Email via Microsoft Exchange
===========================================================


INTRODUCTION:
-------------

This bulletin explains how to programmatically send a fax/email message
from
a Forms/Reports application via Microsoft Exchange without any kind of
user
interaction. It shows the general usage of the 'Mailx' package as well
as a
fully coded Forms sample application.

The concept of OLE (Object Linking and Embedding) automation is used to
control
the OLE server application (Microsoft Exchange) using the client
application.
The client in this case may be a Developer/2000 Forms or Reports
application.
It uses the objects and methods exposed by the OLE Messaging Library
which are
much more robust than the MSMAPI OCX controls and allow access to many
more
MAPI
properties.

Oracle provides support for OLE automation in its applications by means
of the
OLE2 built-in package. This package contains object types and built-ins
for
creating and manipulating the OLE objects. Some of these built-ins for
e.g.
OLE2.create_obj, OLE2.invoke, OLE2.set_property have been extensively
used in
the code.


Note for Windows'95 users:
--------------------------

The example in this bulletin assumes that the OLE Messaging Library was
properly
installed on your machine (typically, by installing Exchange in Windows
NT).
Since the OLE Messaging Library was released after Windows'95, it is NOT
included
as a part of the Windows'95 Exchange client. I have included a simple
script
and
the necessary files to install this OLE Messaging component which is
archived
in
OLEW95.ZIP file. This can be obtained by accessing the following URL:

ftp://anonymous:userid@www-ess8.oracle.com/desktop/download/olem95.zip

Replace userid by your valid user id on MetaLink.


GENERAL USAGE:
--------------

The Mailx package contains three procedures:

1. Procedure Mailx.logon( profile IN varchar2 default NULL);
----------
Use this procedure to logon to the MS Exchange mail client. The
procedure
takes a character argument which specifies the Exchange Profile to
use for
logon. Passing a NULL argument to the logon procedure brings up a
dialog
box which asks you to choose a profile from a list of valid profiles
or
create a new one if it doesn't exist.


2. Procedure Mailx.send(
--------- Recipient IN varchar2,
Subject IN varchar2 default NULL,
Text IN varchar2 default NULL,
Attachment IN varchar2 default NULL
);

This is the procedure that actually sends the message and
attachments, if
any, to the recipient. The recipient may be specified directly as a
valid
email address or as an alias defined in the address book. If the
message is
intended for a fax recipient then a valid alias must be used that is
defined
as a fax address in the address book.


3. Procedure Mailx.logoff;
-----------

This procedure closes the Exchange session and deallocates the
resources
used by the OLE automation objects.



SAMPLE FORMS APPLICATION:
-------------------------

1. Create the Mailx Package using the following two Program Units:

(a) Mailx Package Spec
(b) Mailx Package Body


Mailx Package Spec:
------------------

PACKAGE Mailx IS

session OLE2.OBJ_TYPE; /* OLE object handle */
args OLE2.LIST_TYPE; /* handle to OLE argument list */

procedure logon( Profile IN varchar2 default NULL );

procedure logoff;

procedure send( Recp IN varchar2,
Subject IN varchar2,
Text IN varchar2,
Attch IN varchar2
);

END;



Mailx Package Body:
------------------

PACKAGE BODY Mailx IS

session_outbox OLE2.OBJ_TYPE;
session_outbox_messages OLE2.OBJ_TYPE;
message1 OLE2.OBJ_TYPE;
msg_recp OLE2.OBJ_TYPE;
recipient OLE2.OBJ_TYPE;
msg_attch OLE2.OBJ_TYPE;
attachment OLE2.OBJ_TYPE;


procedure logon( Profile IN varchar2 default NULL )is
Begin

session := ole2.create_obj('mapi.session'); /* create the session
object */
args := ole2.create_arglist;

ole2.add_arg(args,Profile); /* Specify a valid profile name
*/
ole2.invoke(session,'Logon',args); /* to avoid the logon dialog box
*/
ole2.destroy_arglist(args);

End;


procedure logoff is
Begin

ole2.invoke(session,'Logoff'); /* Logoff the session and
deallocate the *
/
/* resources for all the OLE
objects *
/
ole2.release_obj(session);
ole2.release_obj(session_outbox);
ole2.release_obj(session_outbox_messages);
ole2.release_obj(message1);
ole2.release_obj(msg_recp);
ole2.release_obj(recipient);
ole2.release_obj(msg_attch);
ole2.release_obj(attachment);

End;



procedure send( Recp IN varchar2,
Subject IN varchar2,
Text IN varchar2,
Attch IN varchar2
)is
Begin

/* Add a new object message1 to the outbox */

session_outbox := ole2.get_obj_property(session,'outbox');
session_outbox_messages :=
ole2.get_obj_property(session_outbox,'messages');
message1 := ole2.invoke_obj(session_outbox_messages,'Add');

ole2.set_property(message1,'subject',Subject);
ole2.set_property(message1,'text',Text);

/* Add a recipient object to the message1.Recipients collection */

msg_recp := ole2.get_obj_property(message1,'Recipients');
recipient := ole2.invoke_obj(msg_recp,'add') ;

ole2.set_property(recipient,'name',Recp);
ole2.set_property(recipient,'type',1);
ole2.invoke(recipient,'resolve');

/* Add an attachment object to the message1.Attachments collection */

msg_attch := ole2.get_obj_property(message1,'Attachments');
attachment := ole2.invoke_obj(msg_attch,'add') ;

ole2.set_property(attachment,'name',Attch);
ole2.set_property(attachment,'position',0);
ole2.set_property(attachment,'type',1); /* 1 => MAPI File Data
*/
ole2.set_property(attachment,'source',Attch);

/* Read the attachment from the file */

args := ole2.create_arglist;
ole2.add_arg(args,Attch);
ole2.invoke(attachment,'ReadFromFile',args);
ole2.destroy_arglist(args);

args := ole2.create_arglist;
ole2.add_arg(args,1); /* 1 => save copy */
ole2.add_arg(args,0); /* 0 => no dialog */

/* Send the message without any dialog box, saving a copy in the Outbox
*/

ole2.invoke(message1,'Send',args);

ole2.destroy_arglist(args);
message('Message successfully sent');

End;

END;




2. Create a block called MAPIOLE with the following canvas layout:

|-----------------------------------------------------------------------
|
|
|
| Exchange Profile: |====================|
|
|
|
| To: |============================|
|
|
|
| Subject: |============================|
|
|
|
| Message: |============================|
|
| | |
|
| | |
|
| | |
|
| | |
|
| | |
|
| |============================|
|
| |-----------|
|
| Attachment: |============================| | SEND |
|
| |-----------|
|
|-----------------------------------------------------------------------
|


The layout contains 5 text-itmes:

- Profile
- To
- Subject
- Message (multiline functional property set to true)
- Attach

and a 'Send' button with the following WHEN-BUTTON-PRESSED trigger:
---------------------------
Mailx.logon(:profile);
Mailx.send(:to,:subject,:message,:attch);
Mailx.logoff;



CONCLUSION:
-----------

This bulletin explained how to create a sample Forms application which
can send
a fax/email using OLE automation. The same concept/code can be extended
to
programmatically fax/email an Oracle Report without any user
interaction. In
order to fax a report, first generate the report output as a PDF file
and then
send this to the fax recipient as an attachment using the same Mailx
Package.



REFERENCES:
-----------

1. Developer/2000 White Paper: Enhancing your Forms Applications with
OLE 2.0
2. Technical Bulletin: Object Linking and Embedding (OLE) in Oracle
Forms

________________________________________________________________________
_______
Oracle India Development
Center
Electronic Services
Group



Information from Oracle Corporate Repository.


0
 

Author Comment

by:xiangmin
ID: 6269768
UsamaMunir ,

I use Oracle 8.0.5 server. Can I run Java.

Max
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6269840
You don't need java to run the OLE2 that I supplied...
JT
0
 

Author Comment

by:xiangmin
ID: 6270400
jtrifts,


When I try to create package mail. I got the following error. PLS-00201: identifier 'OLE2.OBJ_TYPE' must be declared.

It seems we do not have OLE2 build in package in our server. Do you know the name of the codes for creating the package?


Thanks!

Max    
0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6271826
Ofcourse You can do that, The answer i proposed is for 8.1.5, for versions higher to that there is Utl_smtp package, so u can use the above solution , just make sure that ur Oracle Databas is installed with full Java Options.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:jtrifts
ID: 6272218
hmmm...upon reflection, I may have made an incorrect assumption.  The use of OLE2 in this case seems to work only if implemented from within Forms or Reports.

I take it that you are wanting a database trigger rather than a form, block, or item level trigger then?

JT
0
 

Author Comment

by:xiangmin
ID: 6273115
UsamaMunir,

Your answer is not for me. As I said, my Oracle version is 8.0.5.

Thanks!

Max
0
 

Author Comment

by:xiangmin
ID: 6273136
JT,

You are right. I am wanting an email from a database trigger or procedure. Do you have any idea?


Thanks!


max
0
 

Author Comment

by:xiangmin
ID: 6273688
JT,

You are right. I am wanting an email from a database trigger or procedure. Do you have any idea?


Thanks!


max
0
 

Author Comment

by:xiangmin
ID: 6278662
JT,

I created one Oracle report and used the all the codes you gave me. I got this error: -30550, User-Defined Exception when I called mail.logon('MS Exchange Settings').

Thanks!

Max
0
 

Expert Comment

by:P_Mahesh
ID: 6295999
Hi,

As far as I understand, whenever you there is an event on a table like insert, update or delete you want a mail to be sent. Right ?
I had successfully configured one system for 8.0.5. but then the platform was Tru64 unix. I Will be telling only in brief the mechanism, you can go to Oracle Documentation, and search for the key words, DBMS_PIPE, and you will get the sample programs.

 Whenever an event occurs on a table, a procedure is triggered which uses DBMS_PIPE.PACK and DBMS_PIPE.SEND packages, and puts in the required message, in the buffer. Then on the systems side,
a Pro*C program continuously runs checking the buffer for new messages, the moment it finds, the Pro*C program which runs on the system, uses DBMS_PIPE.RECEIVE and DBMS_PIPE.UNPACK package, to receive and then unpacks the message. and the program then uses the system's send mail option to send mail. depending upon the contents of the message from DBMS_PIPE.
The 2 important things about this configuration is you need to have Pro*C support on your OS(NT) of your database server and you should be able to send mails from the OS of your database server.


hope this helps,
Mahesh.
0
 

Author Comment

by:xiangmin
ID: 6419208
Thanks!

Max
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 45
Oracle function works in 11g but not in 12c 21 57
statspack purge automate 7 29
EXECUTE IMMEDIATE 5 36
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now