Home » SQL & PL/SQL » SQL & PL/SQL » SMTP Mail Attachment (Oracle 10g,WinXP)
SMTP Mail Attachment [message #388671] Wed, 25 February 2009 21:59 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I am using the following code to send Email with Attachments,

CREATE OR REPLACE PROCEDURE mail_files (
   from_name   VARCHAR2,
   to_name     VARCHAR2,
   subject     VARCHAR2,
   MESSAGE     VARCHAR2,
   max_size    NUMBER DEFAULT 99999,
   filename1   VARCHAR2
)
IS
   v_smtp_server          VARCHAR2 (20)       := 'Mail.XYZ.com';
   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
   file_array (1) := filename1;
   conn := UTL_SMTP.open_connection (v_smtp_server, v_smtp_server_port);
   UTL_SMTP.helo (conn, v_smtp_server);
   UTL_SMTP.mail (conn, from_name);
   UTL_SMTP.rcpt (conn, to_name);
   UTL_SMTP.open_data (conn);
   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
      || '--DMW.Boundary.605592468'
      || crlf
      || 'Content-Type: text/html; charset=window-1256'
      || crlf
      || 'Content-Transfer-Encoding: 7bit'
      || 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);

   IF file_array (1) IS NOT NULL
   THEN
      v_slash_pos := INSTR (file_array (1), '/', -1);

      IF v_slash_pos = 0
      THEN
         v_slash_pos := INSTR (file_array (1), '', -1);
      END IF;

      v_directory_name := SUBSTR (file_array (1), 1, v_slash_pos - 1);
      v_directory_name := 'D_FOLDER';
      v_file_name := SUBSTR (file_array (1), v_slash_pos + 1);
      v_file_name := 'text1.txt';
      v_file_handle := UTL_FILE.fopen (v_directory_name, v_file_name, 'r');
      mesg :=
            crlf
         || '--DMW.Boundary.605592468'
         || crlf
         || 'Content-Type: text/html; charset="window-1256"; name="'
         || v_file_name
         || '"'
         || crlf
         || 'Content-Disposition: attachment; filename="'
         || v_file_name
         || '"'
         || crlf
         || 'Content-Transfer-Encoding: quoted-printable'
         || crlf
         || crlf;
      mesg_len := mesg_len + LENGTH (mesg);
      UTL_SMTP.write_data (conn, mesg);

      LOOP
         BEGIN
            UTL_FILE.get_line (v_file_handle, v_line);
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               EXIT;
         END;

         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;

      mesg := crlf;
      UTL_SMTP.write_data (conn, mesg);
      UTL_FILE.fclose (v_file_handle);
   END IF;

   mesg := crlf || '--DMW.Boundary.605592468--' || crlf;
   UTL_SMTP.write_data (conn, mesg);
   UTL_SMTP.close_data (conn);
   UTL_SMTP.quit (conn);
END;
/



But when i execute the above procedure, i am actually able to send the Email but instead of attachment the Text in the file is getting written in the message body some thing like this,

Quote:

--DMW.Boundary.605592468
Content-Type: text/html; charset=window-1256
Content-Transfer-Encoding: 7bit
hiii

--DMW.Boundary.605592468
Content-Type: text/html; charset="window-1256"; name="text1.txt"
Content-Disposition: attachment; filename="text1.txt"
Content-Transfer-Encoding: quoted-printable

This is first file


--DMW.Boundary.605592468--





"This is first file" this actually is the text in text1.txt.

Please have a look at it, and guide me what could be the problem.

Re: SMTP Mail Attachment [message #388694 is a reply to message #388671] Wed, 25 February 2009 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no problem. This code is for sending text file attachment.
What make you think there is a problem?

Regards
Michel
Re: SMTP Mail Attachment [message #388695 is a reply to message #388671] Wed, 25 February 2009 23:17 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Ya,

I am able to send the email, but instead of attachment, the Text file is appearing in the message body. It is not getting attached.

Regards,
Ashoka BL
Bengaluru
Re: SMTP Mail Attachment [message #388697 is a reply to message #388695] Wed, 25 February 2009 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
, the Text file is appearing in the message body.

Where?

Regards
Michel
Re: SMTP Mail Attachment [message #388698 is a reply to message #388671] Wed, 25 February 2009 23:22 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Quote:

--DMW.Boundary.605592468
Content-Type: text/html; charset=window-1256
Content-Transfer-Encoding: 7bit
hiii

--DMW.Boundary.605592468
Content-Type: text/html; charset="window-1256"; name="text1.txt"
Content-Disposition: attachment; filename="text1.txt"
Content-Transfer-Encoding: quoted-printable

This is first file


--DMW.Boundary.605592468--



This is the Message body i am getting when i run the procedure.

I have attached my Email Screenshot which i recieved after executing the procedure.

Regards,
Ashoka BL
Bengaluru
  • Attachment: scr1.PNG
    (Size: 64.05KB, Downloaded 194 times)
Re: SMTP Mail Attachment [message #388700 is a reply to message #388671] Wed, 25 February 2009 23:24 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

The Text file content is appearing in the message body.

Quote:

This is first file

Re: SMTP Mail Attachment [message #388723 is a reply to message #388698] Thu, 26 February 2009 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then your message is bad formed.
Instead of trying to use utl_smtp which requires to know how to format a mail, you should use utl_mail which does it for you.
There are many working examples here.

Regards
Lichel
Re: SMTP Mail Attachment [message #388725 is a reply to message #388671] Thu, 26 February 2009 00:36 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Ohh..Ok Michel, Will look for UTL_MAIL, and will get back if any issues.

Thanks anyhow.

Regards,
Ashoka BL
Bengaluru
Previous Topic: SMTP
Next Topic: Base64 String to Image
Goto Forum:
  


Current Time: Wed Dec 07 16:45:37 CST 2016

Total time taken to generate the page: 0.05834 seconds