| Sending Email Size > 32k Using UTL_mail.send_attach_raw [message #586814] |
Tue, 11 June 2013 02:20  |
 |
itworld
Messages: 1
Registered: June 2013
|
Junior Member |
|
|
Hello !!
I'm able to send emails from Database using UTL_mail as per the doc below
http://www.orafaq.com/wiki/Send_mail_from_PL/SQL#Send_mail_with_UTL_MAIL
Appreciate if i can get an sample to sending an email using UTL_MAIL.SEND_ATTACH_RAW
or any other ways with Attachments having size > 32k
As i'm getting this error message if file size is more
ERROR at line 1:
ORA-20001: The following error has occured: ORA-06502: PL/SQL: numeric or value error: raw variable
length too long
ORA-06512: at line 85
My code is as below
SQL> DECLARE
2 fil BFILE;
3 file_len PLS_INTEGER;
4 MAX_LINE_WIDTH PLS_INTEGER := 54;
5 buf RAW(2100);
6 amt BINARY_INTEGER := 2000;
7 pos PLS_INTEGER := 1; /* pointer for each piece */
8 filepos PLS_INTEGER := 1; /* pointer for the file */
9 filenm VARCHAR2(50) := 'inv.jpg'; /* binary file attachment */
10 data RAW(2100);
11 chunks PLS_INTEGER;
12 len PLS_INTEGER;
13 modulo PLS_INTEGER;
14 pieces PLS_INTEGER;
15 err_num NUMBER;
16 err_msg VARCHAR2(100);
17 resultraw RAW(32000);
18 BEGIN
19 /* Assign the file a handle */
20 fil := BFILENAME('ORADIR', filenm);
21 /* Get the length of the file in bytes */
22 file_len := dbms_lob.getlength(fil);
23 /* Get the remainer when we divide by amt */
24 modulo := mod(file_len, amt);
25 /* How many pieces? */
26 pieces := trunc(file_len / amt);
27 if (modulo <> 0) then
28 pieces := pieces + 1;
29 end if;
30 /* Open the file */
31 dbms_lob.fileopen(fil, dbms_lob.file_readonly);
32 /* Read the first amt into the buffer */
33 dbms_lob.read(fil, amt, filepos, buf);
34 /* For each piece of the file . . . */
35 FOR i IN 1..pieces LOOP
36 /* Position file pointer for next read */
37 filepos := i * amt + 1;
38 /* Calculate remaining file length */
39 file_len := file_len - amt;
40 /* Stick the buffer contents into data */
41 data := utl_raw.concat(data, buf);
42 /* Calculate the number of chunks in this piece */
43 chunks := trunc(utl_raw.length(data) / MAX_LINE_WIDTH);
44 /* Don't want too many chunks */
45 IF (i <> pieces) THEN
46 chunks := chunks - 1;
47 END IF;
48 /* For each chunk in this piece . . . */
49 FOR j IN 0..chunks LOOP
50 /* Position ourselves in this piece */
51 pos := j * MAX_LINE_WIDTH + 1;
52 /* Is this the last chunk in this piece? */
53 IF (j <> chunks) THEN
54 len := MAX_LINE_WIDTH;
55 ELSE
56 len := utl_raw.length(data) - pos + 1;
57 IF (len > MAX_LINE_width) THEN
58 len := MAX_LINE_WIDTH;
59 END IF;
60 END IF;
61 /* If we got something, let's write it */
62 IF (len > 0 ) THEN
63 resultraw := resultraw || utl_raw.substr(data, pos, len);
64 END IF;
65 END LOOP;
66 /* Point at the rest of the data buffer */
67 IF (pos + len <= utl_raw.length(data)) THEN
68 data := utl_raw.substr(data, pos + len);
69 ELSE
70 data := NULL;
71 END IF;
72 /* We're running out of file, only get the rest of it */
73 if (file_len < amt and file_len > 0) then
74 amt := file_len;
75 end if;
76 /* Read the next amount into the buffer */
77 dbms_lob.read(fil, amt, filepos, buf);
78 END LOOP;
79 /* Don't forget to close the file */
80 dbms_lob.fileclose(fil);
81 UTL_MAIL.SEND_ATTACH_RAW(sender => 'abc@hotmail.com', recipients => 'abc@hotmail.com', subject => 'Testmail', message => 'Hello', aattachment => resultraw, att_filename => 'inv.jpg');
82 EXCEPTION
83 WHEN OTHERS THEN
84 --dbms_output.put_line('Error');
85 raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
86 END;
87 /
DECLARE
*
ERROR at line 1:
ORA-20001: The following error has occured: ORA-06502: PL/SQL: numeric or value error: raw variable
length too long
ORA-06512: at line 85
Thanks & regards
Itworld
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
