UTL_MAIL

From: Vishal <mr.v.shah_at_gmail.com>
Date: Tue, 26 Jul 2005 15:44:46 +0530
Message-ID: <33fcbdce050726031417f994dd@mail.gmail.com>

Hi List,

Has somebody used UTL_MAIL in 10g for sending emails with attachments? I'm using the following procedure:

CREATE OR REPLACE PROCEDURE mail_attach (dir1 VARCHAR2, filename VARCHAR2) AS

     l_output   utl_file.file_type;
     l_file          RAW(32767);
     l_size          NUMBER;
     BLOCK           NUMBER;
     b               BOOLEAN;
	 l_start NUMBER := 1;
	 TYPE ARRAY IS TABLE OF VARCHAR2(255);
	 l_data ARRAY := ARRAY('whizkid420_at_gmail.com', 'vishal.shah_at_xyz.com');
   BEGIN
     UTL_FILE.FGETATTR(dir1, filename, b, l_size, BLOCK);
     l_output := utl_file.fopen(dir1, filename, 'r' );
     utl_file.get_raw(l_output, l_file, l_size);
     utl_file.fclose( l_output );
	 FOR i IN l_start .. l_data.COUNT
	 LOOP
		 UTL_MAIL.SEND_ATTACH_RAW (
	           sender     => 'Mailer',
	           recipients => l_data(i),
	           subject    => 'Data for ' || TO_CHAR((SYSDATE-1),'DD-MON-YYYY'),
	           attachment => l_file,
	           message    => 'Dear User, Please find attached the upload
data for the period mentioned in the subject line.',
	           att_inline => FALSE,
	           att_filename => filename);
			   l_start := l_start + 1;
	 END LOOP;

   END;
/

If I attach a file which is less than 32767 bytes (as specified in the declaration), it sends the mail successfully. But it the filesize exceeds then it gives the following error:

PROD_at_DWH> exec mail_attach('UTL_PATH','KFL11-17-JUL-2005')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.54
PROD_at_DWH> exec mail_attach('UTL_PATH','KFL20-26-JUN-2005') BEGIN mail_attach('UTL_PATH','KFL20-26-JUN-2005'); END;

*
ERROR at line 1:

ORA-06525: Length Mismatch for CHAR or RAW data
ORA-06512: at "SYS.UTL_FILE", line 119
ORA-06512: at "SYS.UTL_FILE", line 1100
ORA-06512: at "PROD.MAIL_ATTACH", line 15
ORA-06512: at line 1

Elapsed: 00:00:00.03

How can I send attachments larger than 32767 bytes? Thanks a lot for your help!

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 26 2005 - 05:17:54 CDT