Solved

Email attachment through a stored procedure in Oracle

Posted on 2002-03-12
11
6,464 Views
Last Modified: 2007-11-27
I would like to send a mail with attachment through a storedprocedure in Oracle
0
Comment
Question by:gsbharathi
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 9

Accepted Solution

by:
dbalaski earned 50 total points
ID: 6859730
Okay -- It is possible with the

1)  You could using  Java Stored Procedures (JSP)
and the Sun Microsystems JavaMail package.  Attachments can also be sent..  This article references JavaMail, a Java package provided by Sun Microsystems
that implements the mail functionality.  For additional details, refer to:
   http://java.sun.com/products/javamail

Okay -- here is the example from Oracle  (I have not tried this):

nstallation
------------

Download JavaMail (v1.1.3 or later) from http://java.sun.com/products/javamail
and the Javabeans(tm) Activation Framework.  The archive consists of
documentation, demos, and a .jar file containing the required java classes.
Those .jar files must be extracted and loaded into the server using the
loadjava utility.

loadjava -user user/password -resolve -synonym activation.jar

loadjava -user user/password -resolve -synonym mail.jar



Once the classes have been loaded, you may need to resolve permission issues
using the following statements:

-- Grant needed permissions based on the Java 2 Security Standard

-- Allows access to system properties
exec dbms_java.grant_permission('SCOTT',
                                'java.util.PropertyPermission',
                                '*',
                                'read,write');

-- Allows access to sockets
exec dbms_java.grant_permission('SCOTT',
                                'java.net.SocketPermission',
                                '*',
                                'connect, resolve');

-- Allows access to local files
exec dbms_java.grant_permission('SCOTT',
                                'java.io.FilePermission',
                                '/dir/dir/attachments/*',
                                'read, write');

Next, the following SQL*PLUS script should be executed. It simply creates a
Java class named SendMail with only one member function called Send(), and a
PL/SQL package SendMailJPkg.  These form an interface to JavaMail.  At the
end of the script, an anonymous PL/SQL block tests the whole program.

-- SendMail with attachment, the java way;
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SendMail" AS
   import java.util.*;
   import java.io.*;
   import javax.mail.*;
   import javax.mail.internet.*;
   import javax.activation.*;
   public class SendMail {
      // Sender, Recipient, CCRecipient, and BccRecipient are comma-
      // separated lists of addresses;
      // Body can span multiple CR/LF-separated lines;
      // Attachments is a ///-separated list of file names;
      public static int Send(String SMTPServer,
                             String Sender,
                             String Recipient,
                             String CcRecipient,
                             String BccRecipient,
                             String Subject,
                             String Body,
                             String ErrorMessage[],
                             String Attachments) {

         // Error status;
         int ErrorStatus = 0;

          // create some properties and get the default Session;
          Properties props = System.getProperties();
          props.put("mail.smtp.host", SMTPServer);
          Session session = Session.getDefaultInstance(props, null);

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

             // extracts the senders and adds them to the message;
             // Sender is a comma-separated list of e-mail addresses as
             // per RFC822;
             {
                InternetAddress[] TheAddresses =
                                         InternetAddress.parse(Sender);
                msg.addFrom(TheAddresses);
             }

             // extract the recipients and assign them to the message;
             // Recipient is a comma-separated list of e-mail addresses
             // as per RFC822;
             {
                InternetAddress[] TheAddresses =
                                      InternetAddress.parse(Recipient);
                msg.addRecipients(Message.RecipientType.TO,
                                  TheAddresses);
             }

             // extract the Cc-recipients and assign them to the
             // message;
             // CcRecipient is a comma-separated list of e-mail
             // addresses as per RFC822;
             if (null != CcRecipient) {
                InternetAddress[] TheAddresses =
                                    InternetAddress.parse(CcRecipient);
                msg.addRecipients(Message.RecipientType.CC,
                                  TheAddresses);
             }

             // extract the Bcc-recipients and assign them to the
             // message;
             // BccRecipient is a comma-separated list of e-mail
             // addresses as per RFC822;
             if (null != BccRecipient) {
                InternetAddress[] TheAddresses =
                                   InternetAddress.parse(BccRecipient);
                msg.addRecipients(Message.RecipientType.BCC,
                                  TheAddresses);
             }

             // subject field;
             msg.setSubject(Subject);

             // create the Multipart to be added the parts to;
             Multipart mp = new MimeMultipart();

             // create and fill the first message part;
             {
                MimeBodyPart mbp = new MimeBodyPart();
                mbp.setText(Body);

                // attach the part to the multipart;
                mp.addBodyPart(mbp);
             }

             // attach the files to the message;
             if (null != Attachments) {
                int StartIndex = 0, PosIndex = 0;
                while (-1 != (PosIndex = Attachments.indexOf("///",
                                                       StartIndex))) {
                   // create and fill other message parts;
                   MimeBodyPart mbp = new MimeBodyPart();
                   FileDataSource fds =
                   new FileDataSource(Attachments.substring(StartIndex,
                                                            PosIndex));
                   mbp.setDataHandler(new DataHandler(fds));
                   mbp.setFileName(fds.getName());
                   mp.addBodyPart(mbp);
                   PosIndex += 3;
                   StartIndex = PosIndex;
                }
                // last, or only, attachment file;
                if (StartIndex < Attachments.length()) {
                   MimeBodyPart mbp = new MimeBodyPart();
                   FileDataSource fds =
                 new FileDataSource(Attachments.substring(StartIndex));
                   mbp.setDataHandler(new DataHandler(fds));
                   mbp.setFileName(fds.getName());
                   mp.addBodyPart(mbp);
                }
             }

             // add the Multipart to the message;
             msg.setContent(mp);

             // set the Date: header;
             msg.setSentDate(new Date());

             // send the message;
             Transport.send(msg);
          } catch (MessagingException MsgException) {
               ErrorMessage[0] = MsgException.toString();
               Exception TheException = null;
               if ((TheException = MsgException.getNextException()) !=
                                                                  null)
                  ErrorMessage[0] = ErrorMessage[0] + "\n" +
                                    TheException.toString();
              ErrorStatus = 1;
          }
          return ErrorStatus;
      }
   }
/
show errors java source "SendMail"

CREATE OR REPLACE PACKAGE SendMailJPkg AS
   -- EOL is used to separate text line in the message body;
   EOL CONSTANT STRING(2) := CHR(13) || CHR(10);

   TYPE ATTACHMENTS_LIST IS
      TABLE OF VARCHAR2(4000);

   -- high-level interface with collections;
   FUNCTION SendMail(SMTPServerName IN STRING,
                     Sender IN STRING,
                     Recipient IN STRING,
                     CcRecipient IN STRING              DEFAULT '',
                     BccRecipient IN STRING             DEFAULT '',
                     Subject IN STRING                  DEFAULT '',
                     Body IN STRING                     DEFAULT '',
                     ErrorMessage OUT STRING,
                     Attachments IN ATTACHMENTS_LIST    DEFAULT NULL)
                                                         RETURN NUMBER;
END SendMailJPkg;
/
show errors

CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS

   PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
                             AttachmentList OUT VARCHAR2) IS
      AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
   BEGIN
      -- boolean short-circuit is used here;
      IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
         AttachmentList := Attachments(Attachments.FIRST);
         -- scan the collection, skip first element since it has been
         -- already processed;
         -- accommodate for sparse collections;
         FOR I IN Attachments.NEXT(Attachments.FIRST) ..
                  Attachments.LAST LOOP
            AttachmentList := AttachmentList || AttachmentSeparator ||
                              Attachments(I);
         END LOOP;
      ELSE
         AttachmentList      := '';
      END IF;
   END ParseAttachment;

   -- forward declaration;
   FUNCTION JSendMail(SMTPServerName IN STRING,
                      Sender IN STRING,
                      Recipient IN STRING,
                      CcRecipient IN STRING,
                      BccRecipient IN STRING,
                      Subject IN STRING,
                      Body IN STRING,
                      ErrorMessage OUT STRING,
                      Attachments IN STRING) RETURN NUMBER;

   -- high-level interface with collections;
   FUNCTION SendMail(SMTPServerName IN STRING,
                     Sender IN STRING,
                     Recipient IN STRING,
                     CcRecipient IN STRING,
                     BccRecipient IN STRING,
                     Subject IN STRING,
                     Body IN STRING,
                     ErrorMessage OUT STRING,
                     Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
      AttachmentList VARCHAR2(4000) := '';
      AttachmentTypeList VARCHAR2(2000) := '';
   BEGIN
      ParseAttachment(Attachments,
                      AttachmentList);
      RETURN JSendMail(SMTPServerName,
                       Sender,
                       Recipient,
                       CcRecipient,
                       BccRecipient,
                       Subject,
                       Body,
                       ErrorMessage,
                       AttachmentList);
   END SendMail;

   -- JSendMail's body is the java function SendMail.Send();
   -- thus, no PL/SQL implementation is needed;
   FUNCTION JSendMail(SMTPServerName IN STRING,
                      Sender IN STRING,
                      Recipient IN STRING,
                      CcRecipient IN STRING,
                      BccRecipient IN STRING,
                      Subject IN STRING,
                      Body IN STRING,
                      ErrorMessage OUT STRING,
                      Attachments IN STRING) RETURN NUMBER IS
   LANGUAGE JAVA
   NAME 'SendMail.Send(java.lang.String,
                       java.lang.String,
                       java.lang.String,
                       java.lang.String,
                       java.lang.String,
                       java.lang.String,
                       java.lang.String,
                       java.lang.String[],
                       java.lang.String) return int';
END SendMailJPkg;
/
show errors

var ErrorMessage VARCHAR2(4000);  
var ErrorStatus NUMBER;

-- enable SQL*PLUS output;
SET SERVEROUTPUT ON
-- redirect java output into SQL*PLUS buffer;
exec dbms_java.set_output(5000);
BEGIN
   :ErrorStatus := SendMailJPkg.SendMail(
                SMTPServerName => 'gmsmtp03.oraclecorp.com',
                Sender    => 'cesare.cervini@oracle.com',
                Recipient => 'ccervini@ch.oracle.com',
                CcRecipient => '',
                BccRecipient => '',
                Subject   => 'This is the subject line: Test JavaMail',
                Body => 'This is the body: Hello, this is a test' ||
                         SendMailJPkg.EOL || 'that spans 2 lines',
                ErrorMessage => :ErrorMessage,
                Attachments  => SendMailJPkg.ATTACHMENTS_LIST(
                                   '/export/home/osupport/on.lst',
                                   '/export/home/osupport/sqlnet.log.Z'
                                )
   );
END;
/
print



------------------------------------

con't in next message:

dBalaski


0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6859741
Okay --  with the JAVA stuff said,

I read a document about using the SMTP package at:
http://home.clara.net/dwotton/dba/oracle_smtp.txt

It looks good, but again I have not tried it.

Another package you can try is PSP_MAIL at:
http://www.dpsp-yes.com/dpsp/prod/!go?id_=nproduct
PSP_Mail is the Dynamic PSP add-on which can be also used standalone, encapsulating SMTP interface provided in Oracle8i with our DNS lookup functions in a single easy-to-use package that gives Oracle PL/SQL developers a simple way to send e-mail from Oracle RDBMS without the need for any external programs/scripts


Another option:   You can encode  MIME formating with UTL_SMTP
It is possible -- I have used this with different charactersets,  but not attachements.

I hope these suggestions help.
sincereley,
dBalaski
0
 

Author Comment

by:gsbharathi
ID: 6859802
I am using the front end as VB to access the stored procedure in Oracle so i would like to do everything in the stored procedure and just call the stored procedure through VB
0
 
LVL 47

Expert Comment

by:schwertner
ID: 6859830
This is the stored procedure. Only modify and call it!

You need version >=8.1.6, and Java installed for this to work.
Create a job that checks what it needs to check and when needed, runs this:

PROCEDURE SEND_MAIL
(V_MAILTO IN VARCHAR2
,V_MAILFROM IN VARCHAR2
,V_SUBJECT IN VARCHAR2
,V_BODY IN LONG
,V_CC IN VARCHAR2 := null
,V_BCC IN VARCHAR2 := null
)
IS
-- PL/SQL Specification
CONN UTL_SMTP.CONNECTION;
dato varchar2(4000);

-- PL/SQL Block
BEGIN
DECLARE
CONN UTL_SMTP.CONNECTION;
dato VARCHAR2(4000);
nSqlCode NUMBER(8);
vSqlErrm VARCHAR2(4000);
vSrv VARCHAR2(100);

BEGIN

vSrv := 'smtp.server.com;
CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);
UTL_SMTP.HELO(CONN,'Origin.server.com');
UTL_SMTP.MAIL(CONN,v_mailfrom);
UTL_SMTP.RCPT(CONN,v_mailto);
UTL_SMTP.OPEN_DATA(CONN);

UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);

-- Splits body (long) into varchar2s.

FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP

DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);

END LOOP;

UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);


EXCEPTION
WHEN utl_smtp.transient_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;

BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20100, vSqlErrm);

WHEN utl_smtp.permanent_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20200, vSqlErrm);
END;
END SEND_MAIL;
0
 

Author Comment

by:gsbharathi
ID: 6859837
I am using the front end as VB to access the stored procedure in Oracle so i would like to do everything in the stored procedure and just call the stored procedure through VB
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.

 

Author Comment

by:gsbharathi
ID: 6859909
I am using the front end as VB to access the stored procedure in Oracle so i would like to do everything in the stored procedure and just call the stored procedure through VB
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6861212
I sense from your three posts about your environment, that you're looking for something different.  To accomplish this without using Java, you can use the UTL_SMTP PL/SQL supplied procedure.  To find out more, look here:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/utl_smtp.htm#1000695

(registration is required, but free)

There you will find this example, whi I believe does what you want, or could be called by a procedure that does whatever else you wish to do:

The following code example illustrates how the SMTP package might be used by an application to send email. The application connects to an SMTP server at port 25 and sends a simple text message.

PROCEDURE send_mail (sender    IN VARCHAR2,
                     recipient IN VARCHAR2,
                     message   IN VARCHAR2)
IS
    mailhost    VARCHAR2(30) := 'mailhost.mydomain.com';
    mail_conn  utl_smtp.connection;

BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);
    utl_smtp.data(mail_conn, message);
    utl_smtp.quit(mail_conn);
EXCEPTION
    WHEN OTHERS THEN
        -- Handle the error
END;

Good luck!
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6862824
Mail itself is easy with UTL_SMTP,  

sending attachments with  mail is not as easy...

That I why I presented those options --   The Java Stored Procedure seems the most promising option.  
Remember -- this is JAVA STORED IN THE DATABASE utilizing the Oracle JServer running in the Database.
So it won't matter what the client is running -  vb,  Access,  whatever...

The other options seem promising --   although one is a bit costly  (the PSP_MAIL).

dBalaski
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7063052
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 7067222
Mindphaser,
    Unless gsbharathi comes back and decides I'd suggest a split between dbalaski and schwertner.  On rereading, I find my answer to be somewhat repetitive, and without the attachments.
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7069957
Force accepted

** Mindphaser - Community Support Moderator **

schwertner, there will be a separate question with points for your help.
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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

16 Experts available now in Live!

Get 1:1 Help Now