UTL_MAIL
- From: Vishal <mr.v.shah@xxxxxxxxx>
- To: "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Jul 2005 15:44:46 +0530
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@xxxxxxxxx', 'vishal.shah@xxxxxxx');
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@DWH> exec mail_attach('UTL_PATH','KFL11-17-JUL-2005')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.54
PROD@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!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
