This is an example of sending multiple attachment through PL/SQL using UTL_SMTP. This code is posted in OTN by me.
SQL> /* My database version */
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> /* Creating the image store table */
SQL> CREATE TABLE image_store(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(50),image BLOB);
Table created.
SQL> /* Procedure to load images in the table */
SQL> CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
2 src_file BFILE;
3 dst_file BLOB;
4 lgh_file BINARY_INTEGER;
5 BEGIN
6 src_file := bfilename('TEMP', pfname);
7
8 INSERT INTO image_store
9 (id_pk,fname,image)
10 VALUES
11 (pi_id,pfname, EMPTY_BLOB())
12 RETURNING image INTO dst_file;
13
14 DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
15 lgh_file := dbms_lob.getlength(src_file);
16 DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
17 dbms_lob.close(src_file);
18 COMMIT;
19 END load_file;
20 /
Procedure created.
SQL> /* Loading some images in the table*/
SQL> EXECUTE load_file(1,'Waterlilies.jpg');
PL/SQL procedure successfully completed.
SQL> EXECUTE load_file(2,'Winter.jpg');
PL/SQL procedure successfully completed.
SQL> /* Checking the rows inserted*/
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(image) image_length
2 FROM image_store;
ID_PK FNAME IMAGE_LENGTH
---------- -------------------------------------------------- ------------
1 Waterlilies.jpg 83794
2 Winter.jpg 105542
SQL> DECLARE
2 /*LOB operation related varriables */
3 l_buffer RAW(54);
4 l_amount BINARY_INTEGER := 54;
5 l_pos INTEGER := 1;
6 l_blob BLOB := EMPTY_BLOB;
7 l_blob_len INTEGER;
8 v_amount INTEGER;
9 v_fname VARCHAR2(50);
10
11 /* This cursor will pick the two images for attachment in the mail. */
12 CURSOR img_cur IS SELECT fname,image
13 FROM image_store;
14
15 /*UTL_SMTP related varriavles. */
16 v_connection_handle UTL_SMTP.CONNECTION;
17 v_from_email_address VARCHAR2(30) := 'xx@xxxx.com';
18 v_to_email_address VARCHAR2(30) := 'xxxxxx@xxxxxx.com';
19 v_smtp_host VARCHAR2(30) := 'x.xxx.xxx.xxx'; --My mail server, replace it with yours
.
20 v_subject VARCHAR2(30) := 'Your Test Mail';
21 l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';
22
23 /* This send_header procedure is written in the documentation */
24 PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
25 BEGIN
26 UTL_SMTP.WRITE_DATA(v_connection_handle,
27 pi_name || ': ' || pi_header || UTL_TCP.CRLF);
28 END;
29
30 BEGIN
31
32 /*UTL_SMTP related coding. */
33 v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
34 UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
35 UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
36 UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
37 UTL_SMTP.OPEN_DATA(v_connection_handle);
38 send_header('From', '"Sender" <' || v_from_email_address || '>');
39 send_header('To', '"Recipient" <' || v_to_email_address || '>');
40 send_header('Subject', v_subject);
41
42 --MIME header.
43 UTL_SMTP.WRITE_DATA(v_connection_handle,
44 'MIME-Version: 1.0' || UTL_TCP.CRLF);
45 UTL_SMTP.WRITE_DATA(v_connection_handle,
46 'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
47 UTL_SMTP.WRITE_DATA(v_connection_handle,
48 ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
49 UTL_TCP.CRLF);
50 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
51
52 -- Mail Body
53 UTL_SMTP.WRITE_DATA(v_connection_handle,
54 '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
55 UTL_SMTP.WRITE_DATA(v_connection_handle,
56 'Content-Type: text/plain;' || UTL_TCP.CRLF);
57 UTL_SMTP.WRITE_DATA(v_connection_handle,
58 ' charset=US-ASCII' || UTL_TCP.CRLF);
59 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
60 UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
61 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
62
63 /* Opening the cursor to loop through the images*/
64 OPEN img_cur;
65 LOOP
66 FETCH img_cur INTO v_fname,l_blob;
67 EXIT WHEN img_cur%NOTFOUND;
68
69 -- Mail Attachment
70 UTL_SMTP.WRITE_DATA(v_connection_handle,
71 '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
72 UTL_SMTP.WRITE_DATA(v_connection_handle,
73 'Content-Type: application/octet-stream' ||
74 UTL_TCP.CRLF);
75 UTL_SMTP.WRITE_DATA(v_connection_handle,
76 'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
77 UTL_SMTP.WRITE_DATA(v_connection_handle,
78 ' filename="' || v_fname || '"' || --My filename
79 UTL_TCP.CRLF);
80 UTL_SMTP.WRITE_DATA(v_connection_handle,
81 'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
82 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
83
84 /* Re-initializing the varriables. This is very important*/
85 l_buffer := NULL;
86 l_pos :=1;
87 l_amount:=54;
88 /* Writing the BLOL in chunks */
89 l_blob_len := DBMS_LOB.getlength(l_blob);
90 WHILE l_pos < l_blob_len LOOP
91 DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
92 UTL_SMTP.write_raw_data(v_connection_handle,
93 UTL_ENCODE.BASE64_ENCODE(l_buffer));
94 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
95 l_buffer := NULL;
96 l_pos := l_pos + l_amount;
97 END LOOP;
98 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
99 l_blob:=EMPTY_BLOB;
100 END loop; --End cursor loop.
101
102 CLOSE img_cur; --Close the cursor.
103
104 -- Close Email
105 UTL_SMTP.WRITE_DATA(v_connection_handle,
106 '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
107 UTL_SMTP.WRITE_DATA(v_connection_handle,
108 UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
109
110 UTL_SMTP.CLOSE_DATA(v_connection_handle);
111 UTL_SMTP.QUIT(v_connection_handle);
112 DBMS_LOB.FREETEMPORARY(l_blob);
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 UTL_SMTP.QUIT(v_connection_handle);
117 DBMS_LOB.FREETEMPORARY(l_blob);
118 RAISE;
119 END;
120 /
SQL> /* My database version */
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> /* Creating the image store table */
SQL> CREATE TABLE image_store(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(50),image BLOB);
Table created.
SQL> /* Procedure to load images in the table */
SQL> CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
2 src_file BFILE;
3 dst_file BLOB;
4 lgh_file BINARY_INTEGER;
5 BEGIN
6 src_file := bfilename('TEMP', pfname);
7
8 INSERT INTO image_store
9 (id_pk,fname,image)
10 VALUES
11 (pi_id,pfname, EMPTY_BLOB())
12 RETURNING image INTO dst_file;
13
14 DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
15 lgh_file := dbms_lob.getlength(src_file);
16 DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
17 dbms_lob.close(src_file);
18 COMMIT;
19 END load_file;
20 /
Procedure created.
SQL> /* Loading some images in the table*/
SQL> EXECUTE load_file(1,'Waterlilies.jpg');
PL/SQL procedure successfully completed.
SQL> EXECUTE load_file(2,'Winter.jpg');
PL/SQL procedure successfully completed.
SQL> /* Checking the rows inserted*/
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(image) image_length
2 FROM image_store;
ID_PK FNAME IMAGE_LENGTH
---------- -------------------------------------------------- ------------
1 Waterlilies.jpg 83794
2 Winter.jpg 105542
SQL> DECLARE
2 /*LOB operation related varriables */
3 l_buffer RAW(54);
4 l_amount BINARY_INTEGER := 54;
5 l_pos INTEGER := 1;
6 l_blob BLOB := EMPTY_BLOB;
7 l_blob_len INTEGER;
8 v_amount INTEGER;
9 v_fname VARCHAR2(50);
10
11 /* This cursor will pick the two images for attachment in the mail. */
12 CURSOR img_cur IS SELECT fname,image
13 FROM image_store;
14
15 /*UTL_SMTP related varriavles. */
16 v_connection_handle UTL_SMTP.CONNECTION;
17 v_from_email_address VARCHAR2(30) := 'xx@xxxx.com';
18 v_to_email_address VARCHAR2(30) := 'xxxxxx@xxxxxx.com';
19 v_smtp_host VARCHAR2(30) := 'x.xxx.xxx.xxx'; --My mail server, replace it with yours
.
20 v_subject VARCHAR2(30) := 'Your Test Mail';
21 l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';
22
23 /* This send_header procedure is written in the documentation */
24 PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
25 BEGIN
26 UTL_SMTP.WRITE_DATA(v_connection_handle,
27 pi_name || ': ' || pi_header || UTL_TCP.CRLF);
28 END;
29
30 BEGIN
31
32 /*UTL_SMTP related coding. */
33 v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
34 UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
35 UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
36 UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
37 UTL_SMTP.OPEN_DATA(v_connection_handle);
38 send_header('From', '"Sender" <' || v_from_email_address || '>');
39 send_header('To', '"Recipient" <' || v_to_email_address || '>');
40 send_header('Subject', v_subject);
41
42 --MIME header.
43 UTL_SMTP.WRITE_DATA(v_connection_handle,
44 'MIME-Version: 1.0' || UTL_TCP.CRLF);
45 UTL_SMTP.WRITE_DATA(v_connection_handle,
46 'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
47 UTL_SMTP.WRITE_DATA(v_connection_handle,
48 ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
49 UTL_TCP.CRLF);
50 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
51
52 -- Mail Body
53 UTL_SMTP.WRITE_DATA(v_connection_handle,
54 '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
55 UTL_SMTP.WRITE_DATA(v_connection_handle,
56 'Content-Type: text/plain;' || UTL_TCP.CRLF);
57 UTL_SMTP.WRITE_DATA(v_connection_handle,
58 ' charset=US-ASCII' || UTL_TCP.CRLF);
59 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
60 UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
61 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
62
63 /* Opening the cursor to loop through the images*/
64 OPEN img_cur;
65 LOOP
66 FETCH img_cur INTO v_fname,l_blob;
67 EXIT WHEN img_cur%NOTFOUND;
68
69 -- Mail Attachment
70 UTL_SMTP.WRITE_DATA(v_connection_handle,
71 '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
72 UTL_SMTP.WRITE_DATA(v_connection_handle,
73 'Content-Type: application/octet-stream' ||
74 UTL_TCP.CRLF);
75 UTL_SMTP.WRITE_DATA(v_connection_handle,
76 'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
77 UTL_SMTP.WRITE_DATA(v_connection_handle,
78 ' filename="' || v_fname || '"' || --My filename
79 UTL_TCP.CRLF);
80 UTL_SMTP.WRITE_DATA(v_connection_handle,
81 'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
82 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
83
84 /* Re-initializing the varriables. This is very important*/
85 l_buffer := NULL;
86 l_pos :=1;
87 l_amount:=54;
88 /* Writing the BLOL in chunks */
89 l_blob_len := DBMS_LOB.getlength(l_blob);
90 WHILE l_pos < l_blob_len LOOP
91 DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
92 UTL_SMTP.write_raw_data(v_connection_handle,
93 UTL_ENCODE.BASE64_ENCODE(l_buffer));
94 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
95 l_buffer := NULL;
96 l_pos := l_pos + l_amount;
97 END LOOP;
98 UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
99 l_blob:=EMPTY_BLOB;
100 END loop; --End cursor loop.
101
102 CLOSE img_cur; --Close the cursor.
103
104 -- Close Email
105 UTL_SMTP.WRITE_DATA(v_connection_handle,
106 '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
107 UTL_SMTP.WRITE_DATA(v_connection_handle,
108 UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
109
110 UTL_SMTP.CLOSE_DATA(v_connection_handle);
111 UTL_SMTP.QUIT(v_connection_handle);
112 DBMS_LOB.FREETEMPORARY(l_blob);
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 UTL_SMTP.QUIT(v_connection_handle);
117 DBMS_LOB.FREETEMPORARY(l_blob);
118 RAISE;
119 END;
120 /
No comments:
Post a Comment