Solved

how to send mail in attchment using pl/sql code

Posted on 2004-04-27
6
1,458 Views
Last Modified: 2012-08-14
Dear all,

I have to attch XLS file  with attachment in a mail using pl/sql code.
I have downloaded 2 scripts from net.
Both is running and I am receiving mail but attchment is not received with it, eventhough it is given.

Plz help me.

Regards,
Maulin
0
Comment
Question by:maul_shah
6 Comments
 
LVL 13

Accepted Solution

by:
anand_2000v earned 64 total points
ID: 10936005
This is from a similiar question
If you're running Oracle8i and you're familiar with Java, you can write a Java stored procedure to do this.  
You'll need to do the following:

1.  Make sure that the JVM is installed (see $ORACLE_HOME/javavm/install)
2.  Load mail.jar and activation.jar into the database using loadjava.  Get these files by downloading the JavaMail and JavaBeans Activation Framework API's from javasoft.com.
3.  Write a java stored procedure which uses the JavaMail API's and load that into the database using loadjava.  Your program will create a MIME message, read a BLOB from the database, and attach it to the message.

This is not an easy process, but will give you about the most flexible mail capability possible.

Hope this helps,
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 62 total points
ID: 10936052
Not sure why but 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.  Hope it works for you

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;
 
0
 
LVL 2

Assisted Solution

by:n4nazim
n4nazim earned 62 total points
ID: 10936089
Hi,

try this script file ...



================= START SCRIPT FILE =================

REM Procedure mail_files
REM --------------------
REM
REM An Oracle PL/SQL procedure to send emails with file attachments from
REM within an Oracle PL/SQL program.
REM
REM Additional documentation for this script can be found at:
REM http://home.clara.net/dwotton/dba/oracle_smtp.htm
REM
REM If you are looking for a shell-script to send emails with file
REM attachments from the Unix environment, please refer to my web-page:
REM http://home.clara.net/dwotton/unix/mail_files.htm
 
 
create or replace procedure mail_files ( from_name varchar2,
                                         to_name varchar2,
                                         subject varchar2,
                                         message varchar2,
                                         max_size number default 9999999999,
                                         filename1 varchar2 default null,
                                         filename2 varchar2 default null,
                                         filename3 varchar2 default null,
                                         debug number default 0 ) is
 
/*
  This procedure uses the UTL_SMTP package to send an email message.
  Up to three file names may be specified as attachments.
 
  Parameters are:
 
    1) from_name (varchar2)
    2) to_name   (varchar2)
    3) subject   (varchar2)
    4) message   (varchar2)
    5) max_size  (number)
    5) filename1 (varchar2)
    6) filename2 (varchar2)
    7) filename3 (varchar2)
 
  eg.
 
    mail_files( from_name => 'oracle' ,
                to_name   => 'someone@somewhere.com' ,
                subject   => 'A test',
                message   => 'A test message',
                filename1 => '/data/oracle/dave_test1.txt',
                filename2 => '/data/oracle/dave_test2.txt');
 
  Most of the parameters are self-explanatory. "message" is a varchar2
  parameter, up to 32767 bytes long which contains the text of the message
  to be placed in the main body of the email.
 
  filename{1,2,3} are the names of the files to be attached to the email.
  The full pathname of each file must be specified. The files must exist
  in one of the directories specified in the init.ora parameter
  UTL_FILE_DIR. All filename parameters are optional: It is not necessary
  to specify unused file parameters (eg. filename3 is missing in the above
  example).
 
  The max_size parameter enables you to place a constraint on the maximum
  size of message, including all attachments, that the procedure will send.
  If this limit is exceeded, the procedure will truncate the message at
  that point with a '*** truncated ***' message. The default is effectively
  unlimited. However, the text of message body is still limited to 32Kb, as
  it is passed in as a varchar2.
 
  Obviously, as with any Oracle procedure, the parameter values can (and
  usually will be) PL/SQL variables, rather than hard-coded literals, as
  shown here.
 
 
  Written: Dave Wotton, 14/6/01 (Cambridge UK)
           This script comes with no warranty or support. You are free to
           modify it as you wish, but please retain an acknowledgement of
           my original authorship.
 
  Amended: Dave Wotton, 10/7/01
           Now uses the utl_smtp.write_data() method to send the message,
           eliminating the 32Kb message size constraint imposed by the
           utl_smtp.data() procedure.
 
  Amended: Dave Wotton, 20/7/01
           Increased the v_line variable, which holds the file attachment
           lines from 400 to 1000 bytes. This is the maximum supported
           by RFC2821, The Simple Mail Transfer Protocol specification.
 
  Amended: Dave Wotton, 24/7/01
           Now inserts a blank line before each MIME boundary line. Some
           mail-clients require this.
 
  Amended: Dave Wotton, 4/10/01
           Introduced a 'debug' parameter. Defaults to 0. If set to
           non-zero then errors in opening files for attaching are
           reported using dbms_output.put_line.
           Include code to hand MS Windows style pathnames.
*/
 
 
 
/*
  You may need to modify the following variable if you don't have a local
  SMTP service running (particularly relevant to Windows 2000 servers).
  Refer to http://home.clara.net/dwotton/dba/oracle_smtp.htm for more
  details.
*/
 
  v_smtp_server      varchar2(10) := 'localhost';
  v_smtp_server_port number  := 25;
 
  v_directory_name   varchar2(100);
  v_file_name        varchar2(100);
 
  v_line             varchar2(1000);
 
  crlf               varchar2(2):= chr(13) || chr(10);
 
  mesg               varchar2(32767);
 
  conn               UTL_SMTP.CONNECTION;
 
  type varchar2_table is table of varchar2(200) index by binary_integer;
 
  file_array         varchar2_table;
  i                  binary_integer;
 
  v_file_handle      utl_file.file_type;
  v_slash_pos        number;
 
  mesg_len           number;
 
  mesg_too_long      exception;
  invalid_path       exception;
 
  mesg_length_exceeded boolean := false;
 
begin
 
   -- first load the three filenames into an array for easier handling later ...
 
   file_array(1) := filename1;
   file_array(2) := filename2;
   file_array(3) := filename3;
 
   -- Open the SMTP connection ...
   -- ------------------------
 
   conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );
 
   -- Initial handshaking ...
   -- -------------------
 
   utl_smtp.helo( conn, v_smtp_server );
   utl_smtp.mail( conn, from_name );
   utl_smtp.rcpt( conn, to_name );
 
   utl_smtp.open_data ( conn );
 
   -- build the start of the mail message ...
   -- -----------------------------------
 
   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
          'From: ' || from_name || crlf ||
          'Subject: ' || subject || crlf ||
          'To: ' || to_name || crlf ||
          'Mime-Version: 1.0' || crlf ||
          'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || crlf ||
          '' || crlf ||
          'This is a Mime message, which your current mail reader may not' || crlf ||
          'understand. Parts of the message will appear as text. If the remainder' || crlf ||
          'appears as random characters in the message body, instead of as' || crlf ||
          'attachments, then you''ll have to extract these parts and decode them' || crlf ||
          'manually.' || crlf ||
          '' || crlf ||
          '--DMW.Boundary.605592468' || crlf ||
          'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || crlf ||
          'Content-Disposition: inline; filename="message.txt"' || crlf ||
          'Content-Transfer-Encoding: 7bit' || crlf ||
          '' || crlf ||
          message || crlf ;
 
   mesg_len := length(mesg);
 
   if mesg_len > max_size then
      mesg_length_exceeded := true;
   end if;
 
   utl_smtp.write_data ( conn, mesg );
 
   -- Append the files ...
   -- ----------------
 
   for i in  1..3 loop
 
       -- Exit if message length already exceeded ...
 
       exit when mesg_length_exceeded;
 
       -- If the filename has been supplied ...
 
       if file_array(i) is not null then
 
          begin
 
             -- locate the final '/' or '\' in the pathname ...
 
             v_slash_pos := instr(file_array(i), '/', -1 );
 
             if v_slash_pos = 0 then
                v_slash_pos := instr(file_array(i), '\', -1 );
             end if;
 
             -- separate the filename from the directory name ...
 
             v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 );
             v_file_name      := substr(file_array(i), v_slash_pos + 1 );
 
             -- open the file ...
 
             v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' );
   
             -- generate the MIME boundary line ...
 
             mesg := crlf || '--DMW.Boundary.605592468' || crlf ||
             'Content-Type: application/octet-stream; name="' || v_file_name || '"' || crlf ||
             'Content-Disposition: attachment; filename="' || v_file_name || '"' || crlf ||
             'Content-Transfer-Encoding: 7bit' || crlf || crlf ;
 
             mesg_len := mesg_len + length(mesg);
 
             utl_smtp.write_data ( conn, mesg );
 
             -- and append the file contents to the end of the message ...
 
             loop
   
                 utl_file.get_line(v_file_handle, v_line);
   
                 if mesg_len + length(v_line) > max_size then
 
                    mesg := '*** truncated ***' || crlf;
 
                    utl_smtp.write_data ( conn, mesg );
 
                    mesg_length_exceeded := true;
 
                    raise mesg_too_long;
 
                 end if;
 
                 mesg := v_line || crlf;
 
                 utl_smtp.write_data ( conn, mesg );
   
                 mesg_len := mesg_len + length(mesg);
 
             end loop;
 
          exception
 
             when utl_file.invalid_path then
                 if debug > 0 then
                    dbms_output.put_line('Error in opening attachment '||
                                          file_array(i) );
                 end if;
 
             -- All other exceptions are ignored ....
 
             when others then
                 null;
 
          end;
 
          mesg := crlf;
 
          utl_smtp.write_data ( conn, mesg );
 
          -- close the file ...
 
          utl_file.fclose(v_file_handle);
 
        end if;
 
   end loop;
 
   -- append the final boundary line ...
 
   mesg := crlf || '--DMW.Boundary.605592468--' || crlf;
 
   utl_smtp.write_data ( conn, mesg );
 
   -- and close the SMTP connection  ...
 
   utl_smtp.close_data( conn );
 
   utl_smtp.quit( conn );
 
end;
.
/
 
 
 
=========== END SCRIPT FILE ====================
Rgds
Nazim M
0
 
LVL 2

Assisted Solution

by:netuser1976
netuser1976 earned 62 total points
ID: 10981780
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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

11 Experts available now in Live!

Get 1:1 Help Now