Envoyer MAIL avec pièce jointe Oracle 11g

Version imprimable

Bonjour,

Je suis en Oracle 11g et je souhaite faire une procédure qui envoi un mail avec pièce jointe.
J'arrive finalement à envoyer des petits fichiers mais quand le fichier Excel est plus gros je reçois l'erreur ORA06525: Longueur non valide pour les données CHAR ou RAW.

J'ai lu sur quelques articles que la limite de taille est fixée à 32ko. AUriez-vous une idée svp comment je peux envoyer des fichiers sans contrainte de taille ou à la limite avoir une fenetre de 2 ou 3 Mo?

Voici le code de ma procédure:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE OR REPLACE procedure COMMUN.send_email_with_attach
 
 
is  
  v_dir          varchar2(100) := 'XML_DIR';  
  v_sender    varchar2(100) := 'globalsi@oc.gov.ma';  
  v_recipients varchar2(200) := 'globalsi@oc.gov.ma';  
  v_cc            varchar2(200) := null;  
  v_sub          varchar2(60) := 'Sujet Attache';  
  v_message  varchar2(2000) := 'Message ';  
 
  fhandle UTL_FILE.file_type;  
  flen NUMBER;  
  bsize NUMBER;  
  ex BOOLEAN;  
  vtextout RAW (32767);  
  filename varchar2(150):= 'Inspection_2019.xls';
begin  
 fhandle := UTL_FILE.fopen(v_dir, filename, 'r');  -- fichier déposé dans le serveur BDD dans le répertoire v_dir
  UTL_FILE.fgetattr(v_dir, filename, ex, flen, bsize);  
  UTL_FILE.get_raw(fhandle, vtextout, flen);  
  UTL_FILE.fCLOSE(fhandle);  
 
 
  UTL_MAIL.send_attach_varchar2 (
    sender       => 'xx@yy',
    recipients   => 'xx@yy',
    cc           => 'xx@yy',
    bcc          => 'xx@yy',
    subject      => 'UTL_MAIL Test',
    message      => 'If you get this message it worked!',
    attachment   => 'The is the contents of the attachment.',
    att_filename => filename
  );
 
 
 
EXCEPTION  
  when others then  
 null;
end;
/

26/12/2019, 12h27
vanagreg

Bonjour,

Tu vas devoir utiliser UTL_SMTP si tu veux envoyer des mails avec des pièces jointes dont la taille est supérieure à 32k. J'ai écrit un package d'envoi de mail qui gère des pièces jointes CLOB et BLOB, ce qui permet d'envoyer dans le même mail des pièces jointes "texte" et des pièces jointes "binaires". Je peux le poster si cela t'intéresse.
26/12/2019, 14h22
strompakha

Bonjour vanagreg ,

Ouiiii SVP. J'en serais reconnaissant. J'attends ton retour.

Merci

Ok,

D'abord, il faut créer les types suivants:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE TYPE mail_clob_attachment_typ  
IS OBJECT(name    VARCHAR2(255 CHAR),  
          type    VARCHAR2(50 CHAR),  
          content CLOB   
          );  
/  
CREATE OR REPLACE TYPE mail_clob_attachments_typ IS TABLE OF mail_clob_attachment_typ;  
/  
 
 
CREATE OR REPLACE TYPE mail_blob_attachment_typ  
IS OBJECT(name    VARCHAR2(255 CHAR),  
          type    VARCHAR2(50 CHAR),  
          content BLOB   
          );  
/  
CREATE OR REPLACE TYPE mail_blob_attachments_typ IS TABLE OF mail_blob_attachment_typ;  
/

Ils permettent de gérer des pièces jointes BLOB et CLOB. Les types Nested Table permettent de joindre plusieurs pièces si besoin. Voici maintenant le package. Libre à toi de l'adapter à ton besoin.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
create or replace package mail_pkg  
is  
procedure send_mail(p_from             in varchar2,  
                    p_to               in varchar2,  
                    p_subject          in varchar2,  
                    p_message          in varchar2,                      
                    p_smtp_host        in varchar2,  
                    p_smtp_port        in number default 25,  
                    p_cc               in varchar2 default null,  
                    p_bcc              in varchar2 default null,                     
                    p_clob_attachments in mail_clob_attachments_typ default null,  
                    p_blob_attachments in mail_blob_attachments_typ default null                      
                   );  
 
 
end mail_pkg;  
/  
 
 
create or replace package body mail_pkg  
is  
--===========================================--  
--         Constants and Variables           --  
--===========================================--  
g_boundary varchar2(30 char) := 'secbound';  
 
 
type t_email_addresses is table of varchar2(255 char) index by pls_integer;  
to_recipient_list_tab  t_email_addresses;  
cc_recipient_list_tab  t_email_addresses;  
bcc_recipient_list_tab t_email_addresses;  
 
 
--===========================================--  
--         Procedures and Functions          --  
--===========================================--  
/**********************************************************************************  
*                           function Valid_Email_Address                          *  
*---------------------------------------------------------------------------------*  
* Function that checks if an email address is valid                               *  
* - The p_email_adress parameter specifies the email address to check             *  
***********************************************************************************/  
function valid_email_address(p_email_adress in varchar2)  
return boolean  
is  
  begin  
       if regexp_like(p_email_adress, '^[[:alnum:]._%+-]+@[[:alnum:].-]+\.[[:alpha:]]{2,4}$')  
       then  
           return true;  
       else      
           return false;  
       end if;  
end valid_email_address;         
 
 
/**********************************************************************************  
*                         function Check_Email_Addresses                          *  
*---------------------------------------------------------------------------------*  
* Function that checks if an email address is valid                               *  
* - The p_email_addresses parameter specifies the email addresses to check        *  
***********************************************************************************/  
function check_email_addresses(p_email_addresses in t_email_addresses)  
return varchar2  
is  
  begin  
       for i in 1 .. p_email_addresses.count  
       loop  
           if not valid_email_address(p_email_addresses(i))  
           then  
               return 'email address ' || p_email_addresses(i) || ' is not valid';  
           end if;  
       end loop;  
 
       return 'ok';  
 
end check_email_addresses;       
 
/**********************************************************************************  
*                             procedure send_mail                                 *  
*---------------------------------------------------------------------------------*  
* main procedure that sends a mail with possibly multiple attachments             *  
* - the p_from parameter identifies the email address that sends the message      *  
* - the p_to parameter identifies the email addresses to which the message is to  *  
*   be sent. emails are specified as a comma or semicolon-separated list          *   
* - the p_subject parameter specifies the email's subject                         *  
* - the p_message parameter gives the email's body                                *  
* - the p_smtp_host specifies the host that will send the email                   *  
* - the p_smtp_port specifies the port through which the email will be sent       *  
* - the p_cc parameter identifies the copy email addresses to which the message   *  
*   is to be sent. emails are specified as a comma or semicolon-separated list    *   
* - the p_bcc parameter identifies the blind copy email addresses to which the    *  
*   message is to be sent. emails are specified as a comma or semicolon-separated *  
*   list                                                                          *   
* - the p_clob_attachments parameter is optional: it contains text-based pieces   *  
*   to be attached to the mail                                                    *  
* - the p_blob_attachments parameter is optional: it contains binary-based pieces *  
*   to be attached to the mail                                                    *  
***********************************************************************************/         
procedure send_mail(p_from             in varchar2,  
                    p_to               in varchar2,  
                    p_subject          in varchar2,  
                    p_message          in varchar2,                      
                    p_smtp_host        in varchar2,  
                    p_smtp_port        in number default 25,  
                    p_cc               in varchar2 default null,  
                    p_bcc              in varchar2 default null,                     
                    p_clob_attachments in mail_clob_attachments_typ default null,  
                    p_blob_attachments in mail_blob_attachments_typ default null                      
                   )  
is  
  l_mail_conn     utl_smtp.connection;  
  l_offset        number;  
  l_amount        number := 54;    
  l_check_message varchar2(2000 char);  
 
  /***********************************************************************************  
  * Local procedure Add_Recipients : adds all the recipients to the email to be sent *  
  ***********************************************************************************/  
  procedure add_recipients(p_email_addresses in t_email_addresses)   
  is  
    begin  
         for i in 1 .. p_email_addresses.count  
         loop             
             utl_smtp.rcpt(l_mail_conn, p_email_addresses(i));  
         end loop;  
 
    end add_recipients;    
 
  begin  
       -- Prepare the connection  
       if not valid_email_address(p_from)  
       then  
           raise_application_error(-20001, 'the p_from email address ' || p_from || ' is not valid');  
       end if;  
 
       l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);  
       UTL_SMTP.helo(l_mail_conn, p_smtp_host);  
       UTL_SMTP.mail(l_mail_conn, p_from);  
 
       -- Split the email addresses in tables  
       select trim(regexp_substr(p_to, '[^;,]+', 1, level))  
       bulk collect into to_recipient_list_tab  
       from dual  
       connect by level <= regexp_count(p_to, ';|,') + 1;  
 
       if p_cc is not null  
       then  
           select trim(regexp_substr(p_cc, '[^;,]+', 1, level))  
           bulk collect into cc_recipient_list_tab  
           from dual  
           connect by level <= regexp_count(p_cc, ';|,') + 1;  
       end if;      
 
       if p_bcc is not null  
       then  
           select trim(regexp_substr(p_bcc, '[^;,]+', 1, level))  
           bulk collect into bcc_recipient_list_tab  
           from dual  
           connect by level <= regexp_count(p_bcc, ';|,') + 1;  
       end if;   
 
       -- Check the email addresses validity  
       l_check_message := Check_Email_Addresses(to_recipient_list_tab);  
 
       if l_check_message <> 'OK'  
       then  
           raise_application_error(-20001, 'p_to : ' || l_check_message);  
       end if;  
 
       l_check_message := Check_Email_Addresses(cc_recipient_list_tab);  
 
       if l_check_message <> 'OK'  
       then  
           raise_application_error(-20001, 'p_cc : ' || l_check_message);  
       end if;  
 
       -- Check the email addresses validity  
       l_check_message := Check_Email_Addresses(bcc_recipient_list_tab);  
 
       if l_check_message <> 'OK'  
       then  
           raise_application_error(-20001, 'p_bcc : ' || l_check_message);  
       end if;  
 
       -- Prepare all the recepients  
       Add_Recipients(to_recipient_list_tab);  
       Add_Recipients(cc_recipient_list_tab);  
       Add_Recipients(bcc_recipient_list_tab);  
 
       UTL_SMTP.open_data(l_mail_conn);  
 
       -- Build the message info  
       UTL_SMTP.write_data(l_mail_conn, 'From: <' || p_from || '>' || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);  
       IF TRIM(p_cc) IS NOT NULL   
       THEN  
           UTL_SMTP.write_data(l_mail_conn, 'CC: ' || replace(p_cc, ',', ';') || UTL_TCP.crlf);  
       END IF;  
       IF TRIM(p_bcc) IS NOT NULL  
       THEN  
           UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || replace(p_bcc, ',', ';') || UTL_TCP.crlf);  
       END IF;  
 
       UTL_SMTP.write_data(l_mail_conn, 'Date: ' || to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss') || UTL_TCP.crlf);  
 
       UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);  
 
       UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || g_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
       -- Build the message body  
       UTL_SMTP.write_data(l_mail_conn, '--' || g_boundary || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, p_message);  
       UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);  
 
       -- CLOB Attachment Part  
       if p_clob_attachments is not null  
       then  
           for i in 1 .. p_clob_attachments.count  
           loop  
               -- Attachment info  
               UTL_SMTP.write_data(l_mail_conn, '--SECBOUND' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' ||  p_clob_attachments(i).type || '; name="' || p_clob_attachments(i).name || '"' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_clob_attachments(i).name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
               -- Attachment content  
               l_offset := 1;  
               while l_offset < DBMS_LOB.getlength(p_clob_attachments(i).content)  
               loop  
                   UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_clob_attachments(i).content, l_amount, l_offset));  
                   l_offset := l_offset + l_amount;  
              end loop;  
              UTL_SMTP.write_data(l_mail_conn, '' || UTL_TCP.crlf);  
 
           end loop;  
       end if;  
 
       -- BLOB Attachment Part  
       if p_blob_attachments is not null  
       then  
           for i in 1 .. p_blob_attachments.count  
           loop  
               -- Attachment info  
               UTL_SMTP.write_data(l_mail_conn, '--SECBOUND' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' ||  p_blob_attachments(i).type || '; name ="' || p_blob_attachments(i).name || '"' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_blob_attachments(i).name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
               -- Attachment content  
               l_offset := 1;  
               while l_offset < DBMS_LOB.getlength(p_blob_attachments(i).content)  
               loop  
                   UTL_SMTP.write_raw_data(l_mail_conn, UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob_attachments(i).content, l_amount, l_offset)));  
                   l_offset := l_offset + l_amount;  
              end loop;  
              UTL_SMTP.write_data(l_mail_conn, '' || UTL_TCP.crlf);  
 
           end loop;  
       end if;  
 
       -- Last boundry  
       UTL_SMTP.write_data(l_mail_conn, '--SECBOUND--' || UTL_TCP.crlf);  
 
       -- Close data  
       UTL_SMTP.close_data(l_mail_conn);  
       UTL_SMTP.quit(l_mail_conn);  
 
 
end Send_Mail;  
 
end mail_pkg;  
/

26/12/2019, 20h14
strompakha

Salut vanagreg,

Merci bcp pour ce beau bout de code :lol:
Déjà au regard, je vois que c'est exactement ce dont j'avais besoin.

:mouarf:
31/12/2019, 15h50
strompakha

Salut vanagreg,

Est ce que tu peux stp partager avec moi un exemple du code de l'appel de la procédure package mail_pkg.send_mail?

Merci bcp
31/12/2019, 18h05
vanagreg

Ok, j'essaierai de faire ça. Je n'ai pas d'environnement pour tester actuellement, mais je peux fournir un exemple.

Bonjour,

J'ai dû corriger légèrement le code car il y avait quelques problèmes ('ok' en minuscules au lieu de majuscules, utilisation de g_boundary uniformément). Voici le nouveau code:

Les types (j'ai augmenté la taille du type pour les mime longs):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE OR REPLACE TYPE mail_clob_attachment_typ  
IS OBJECT(name    VARCHAR2(255 CHAR),  
          type    VARCHAR2(255 CHAR),  
          content CLOB   
          );  
/  
CREATE OR REPLACE TYPE mail_clob_attachments_typ IS TABLE OF mail_clob_attachment_typ;  
/  
 
 
CREATE OR REPLACE TYPE mail_blob_attachment_typ  
IS OBJECT(name    VARCHAR2(255 CHAR),  
          type    VARCHAR2(255 CHAR),  
          content BLOB   
          );  
/  
CREATE OR REPLACE TYPE mail_blob_attachments_typ IS TABLE OF mail_blob_attachment_typ;  
/

Le package:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
create or replace package mail_pkg  
is  
procedure send_mail(p_from             in varchar2,  
                    p_to               in varchar2,  
                    p_subject          in varchar2,  
                    p_message          in varchar2,                      
                    p_smtp_host        in varchar2,  
                    p_smtp_port        in number default 25,  
                    p_cc               in varchar2 default null,  
                    p_bcc              in varchar2 default null,                     
                    p_clob_attachments in mail_clob_attachments_typ default null,  
                    p_blob_attachments in mail_blob_attachments_typ default null                      
                   );  
 
 
end mail_pkg;  
/  
 
 
create or replace package body mail_pkg  
is  
--===========================================--  
--         Constants and Variables           --  
--===========================================--  
g_boundary varchar2(30 char) := 'secbound';  
 
 
type t_email_addresses is table of varchar2(255 char) index by pls_integer;  
to_recipient_list_tab  t_email_addresses;  
cc_recipient_list_tab  t_email_addresses;  
bcc_recipient_list_tab t_email_addresses;  
 
 
--===========================================--  
--         Procedures and Functions          --  
--===========================================--  
/**********************************************************************************  
*                           function Valid_Email_Address                          *  
*---------------------------------------------------------------------------------*  
* Function that checks if an email address is valid                               *  
* - The p_email_adress parameter specifies the email address to check             *  
***********************************************************************************/  
function valid_email_address(p_email_adress in varchar2)  
return boolean  
is  
  begin  
       if regexp_like(p_email_adress, '^[[:alnum:]._%+-]+@[[:alnum:].-]+\.[[:alpha:]]{2,4}$')  
       then  
           return true;  
       else      
           return false;  
       end if;  
end valid_email_address;         
 
 
/**********************************************************************************  
*                         function Check_Email_Addresses                          *  
*---------------------------------------------------------------------------------*  
* Function that checks if an email address is valid                               *  
* - The p_email_addresses parameter specifies the email addresses to check        *  
***********************************************************************************/  
function check_email_addresses(p_email_addresses in t_email_addresses)  
return varchar2  
is  
  begin  
       for i in 1 .. p_email_addresses.count  
       loop  
           if not valid_email_address(p_email_addresses(i))  
           then  
               return 'email address ' || p_email_addresses(i) || ' is not valid';  
           end if;  
       end loop;  
 
       return 'OK';  
 
end check_email_addresses;       
 
/**********************************************************************************  
*                             procedure send_mail                                 *  
*---------------------------------------------------------------------------------*  
* main procedure that sends a mail with possibly multiple attachments             *  
* - the p_from parameter identifies the email address that sends the message      *  
* - the p_to parameter identifies the email addresses to which the message is to  *  
*   be sent. emails are specified as a comma or semicolon-separated list          *   
* - the p_subject parameter specifies the email's subject                         *  
* - the p_message parameter gives the email's body                                *  
* - the p_smtp_host specifies the host that will send the email                   *  
* - the p_smtp_port specifies the port through which the email will be sent       *  
* - the p_cc parameter identifies the copy email addresses to which the message   *  
*   is to be sent. emails are specified as a comma or semicolon-separated list    *   
* - the p_bcc parameter identifies the blind copy email addresses to which the    *  
*   message is to be sent. emails are specified as a comma or semicolon-separated *  
*   list                                                                          *   
* - the p_clob_attachments parameter is optional: it contains text-based pieces   *  
*   to be attached to the mail                                                    *  
* - the p_blob_attachments parameter is optional: it contains binary-based pieces *  
*   to be attached to the mail                                                    *  
***********************************************************************************/         
procedure send_mail(p_from             in varchar2,  
                    p_to               in varchar2,  
                    p_subject          in varchar2,  
                    p_message          in varchar2,                      
                    p_smtp_host        in varchar2,  
                    p_smtp_port        in number default 25,  
                    p_cc               in varchar2 default null,  
                    p_bcc              in varchar2 default null,                     
                    p_clob_attachments in mail_clob_attachments_typ default null,  
                    p_blob_attachments in mail_blob_attachments_typ default null                      
                   )  
is  
  l_mail_conn     utl_smtp.connection;  
  l_offset        number;  
  l_amount        number := 54;    
  l_check_message varchar2(2000 char);  
 
  /***********************************************************************************  
  * Local procedure Add_Recipients : adds all the recipients to the email to be sent *  
  ***********************************************************************************/  
  procedure add_recipients(p_email_addresses in t_email_addresses)   
  is  
    begin  
         for i in 1 .. p_email_addresses.count  
         loop             
             utl_smtp.rcpt(l_mail_conn, p_email_addresses(i));  
         end loop;  
 
    end add_recipients;    
 
  begin  
       -- Prepare the connection  
       if not valid_email_address(p_from)  
       then  
           raise_application_error(-20001, 'the p_from email address ' || p_from || ' is not valid');  
       end if;  
 
       l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);  
       UTL_SMTP.helo(l_mail_conn, p_smtp_host);  
       UTL_SMTP.mail(l_mail_conn, p_from);  
 
       -- Split the email addresses in tables  
       select trim(regexp_substr(p_to, '[^;,]+', 1, level))  
       bulk collect into to_recipient_list_tab  
       from dual  
       connect by level <= regexp_count(p_to, ';|,') + 1;  
 
       if p_cc is not null  
       then  
           select trim(regexp_substr(p_cc, '[^;,]+', 1, level))  
           bulk collect into cc_recipient_list_tab  
           from dual  
           connect by level <= regexp_count(p_cc, ';|,') + 1;  
       end if;      
 
       if p_bcc is not null  
       then  
           select trim(regexp_substr(p_bcc, '[^;,]+', 1, level))  
           bulk collect into bcc_recipient_list_tab  
           from dual  
           connect by level <= regexp_count(p_bcc, ';|,') + 1;  
       end if;   
 
       -- Check the email addresses validity  
       l_check_message := Check_Email_Addresses(to_recipient_list_tab);  
 
       if l_check_message <> 'OK'  
       then  
           raise_application_error(-20001, 'p_to : ' || l_check_message);  
       end if;  
 
       l_check_message := Check_Email_Addresses(cc_recipient_list_tab);  
 
       if l_check_message <> 'OK'  
       then  
           raise_application_error(-20001, 'p_cc : ' || l_check_message);  
       end if;  
 
       -- Check the email addresses validity  
       l_check_message := Check_Email_Addresses(bcc_recipient_list_tab);  
 
       if l_check_message <> 'OK'  
       then  
           raise_application_error(-20001, 'p_bcc : ' || l_check_message);  
       end if;  
 
       -- Prepare all the recepients  
       Add_Recipients(to_recipient_list_tab);  
       Add_Recipients(cc_recipient_list_tab);  
       Add_Recipients(bcc_recipient_list_tab);  
 
       UTL_SMTP.open_data(l_mail_conn);  
 
       -- Build the message info  
       UTL_SMTP.write_data(l_mail_conn, 'From: <' || p_from || '>' || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);  
       IF TRIM(p_cc) IS NOT NULL   
       THEN  
           UTL_SMTP.write_data(l_mail_conn, 'CC: ' || replace(p_cc, ',', ';') || UTL_TCP.crlf);  
       END IF;  
       IF TRIM(p_bcc) IS NOT NULL  
       THEN  
           UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || replace(p_bcc, ',', ';') || UTL_TCP.crlf);  
       END IF;  
 
       UTL_SMTP.write_data(l_mail_conn, 'Date: ' || to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss') || UTL_TCP.crlf);  
 
       UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);  
 
       UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || g_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
       -- Build the message body  
       UTL_SMTP.write_data(l_mail_conn, '--' || g_boundary || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);  
       UTL_SMTP.write_data(l_mail_conn, p_message);  
       UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);  
 
       -- CLOB Attachment Part  
       if p_clob_attachments is not null  
       then  
           for i in 1 .. p_clob_attachments.count  
           loop  
               -- Attachment info  
               UTL_SMTP.write_data(l_mail_conn, '--' || g_boundary || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' ||  p_clob_attachments(i).type || '; name="' || p_clob_attachments(i).name || '"' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_clob_attachments(i).name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
               -- Attachment content  
               l_offset := 1;  
               while l_offset < DBMS_LOB.getlength(p_clob_attachments(i).content)  
               loop  
                   UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_clob_attachments(i).content, l_amount, l_offset));  
                   l_offset := l_offset + l_amount;  
              end loop;  
              UTL_SMTP.write_data(l_mail_conn, '' || UTL_TCP.crlf);  
 
           end loop;  
       end if;  
 
       -- BLOB Attachment Part  
       if p_blob_attachments is not null  
       then  
           for i in 1 .. p_blob_attachments.count  
           loop  
               -- Attachment info  
               UTL_SMTP.write_data(l_mail_conn, '--' || g_boundary || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' ||  p_blob_attachments(i).type || '; name ="' || p_blob_attachments(i).name || '"' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);  
               UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_blob_attachments(i).name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
               -- Attachment content  
               l_offset := 1;  
               while l_offset < DBMS_LOB.getlength(p_blob_attachments(i).content)  
               loop  
                   UTL_SMTP.write_raw_data(l_mail_conn, UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob_attachments(i).content, l_amount, l_offset)));  
                   l_offset := l_offset + l_amount;  
              end loop;  
              UTL_SMTP.write_data(l_mail_conn, '' || UTL_TCP.crlf);  
 
           end loop;  
       end if;  
 
       -- Last boundary  
       UTL_SMTP.write_data(l_mail_conn, '--' || g_boundary || '--' || UTL_TCP.crlf || UTL_TCP.crlf);  
 
       -- Close data  
       UTL_SMTP.close_data(l_mail_conn);  
       UTL_SMTP.quit(l_mail_conn);  
 
 
end Send_Mail;  
 
end mail_pkg;  
/

Dans une prochaine réponse le test.

2 pièce(s) jointe(s)

Voici un test. J'ai pu configurer ma VM Oracle Linux pour envoi de mail en utilisant gmail.

D'abord, transfert d'un fichier pdf et d'un fichier docx dans un répertoire Oracle:
Je n'ai pas de BLOB dans ma base de données donc je vais en charger directement en tant que BFILE.
Je transfère donc un fichier pdf et un fichier Word dans mon répertoire Oracle TEST_DIR qui pointe sur 'u02/test_dir'

Pièce jointe 527879

Maintenant, envoi du mail avec ces pièces jointes et une pièce jointe texte (fichier texte avec une phrase):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
declare
-- Variables bfile pour les fichiers binaires 
v_pdf_bfile bfile := bfilename('TEST_DIR', 'metadiffer11gr2_twp_1009.pdf');
v_doc_bfile bfile := bfilename('TEST_DIR', 'migrating-databases-from-non-asm-to-asm.docx');
 
-- Variables pour charger les bfile dans des blob
v_pdf_blob blob;
v_doc_blob blob;
 
-- Variables pour pièces jointes
v_blob_attachments mail_blob_attachments_typ := mail_blob_attachments_typ();
v_clob_attachments mail_clob_attachments_typ := mail_clob_attachments_typ();
 
begin
-- Chargement des bfile dans des blob
dbms_lob.fileopen(v_pdf_bfile, dbms_lob.FILE_READONLY);
dbms_lob.createtemporary(v_pdf_blob, true);
dbms_lob.loadfromfile(v_pdf_blob, v_pdf_bfile, dbms_lob.getlength(v_pdf_bfile));
dbms_lob.fileclose(v_pdf_bfile);
 
dbms_lob.fileopen(v_doc_bfile, dbms_lob.FILE_READONLY);
dbms_lob.createtemporary(v_doc_blob, true);
dbms_lob.loadfromfile(v_doc_blob, v_doc_bfile, dbms_lob.getlength(v_doc_bfile));
dbms_lob.fileclose(v_doc_bfile);
 
-- Alimentation des variables pièce jointe de type BLOB
v_blob_attachments.extend(2);
v_blob_attachments(1) := mail_blob_attachment_typ('metadiffer11gr2_twp_1009.pdf', 'application/pdf', v_pdf_blob);
v_blob_attachments(2) := mail_blob_attachment_typ('migrating-databases-from-non-asm-to-asm.docx', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', v_doc_blob);
 
-- Alimentation de la variable pièce jointe de type CLOB : on utilise simplement une phrase en guise de contenu
v_clob_attachments.extend;
v_clob_attachments(1) := mail_clob_attachment_typ('testfile.txt', 'text/plain', 'Test de fichier clob');
 
-- Envoi du mail avec les pièces jointes
mail_pkg.send_mail(p_from             => 'dba.gva@gmail.com',
                   p_to               => 'dba.gva@gmail.com',
                   p_subject          => 'Test Message', 
                   p_message          => 'Message test envoyé par mail_pkg.send_mail',                      
                   p_smtp_host        => 'ols7',
                   p_clob_attachments => v_clob_attachments,
                   p_blob_attachments => v_blob_attachments 
                  ); 
 
end;
/

Vérification du mail dans gmail:
Pièce jointe 527883

On voit bien le message avec les 3 pièces jointes.

NB: Il faut configurer les ACL pour autoriser l'accès au serveur de mail (ols7 ici dans mon cas)

02/01/2020, 00h08
strompakha

Merci bcp vanagreg. Grace à toi j'ai pu débloquer un grand problème de dev.
Je vous en suis tres reconnaissant.

Bonne année 2020.