PL-SQL Procedure to send email with attachment

1CREATE OR REPLACE PROCEDURE XXSEND_EMAIL (p_filename1     IN     VARCHAR2,
2                                          v_subjectline   IN     VARCHAR2,
3                                          o_errbuf           OUT VARCHAR2,
4                                          o_retcode          OUT NUMBER)
5AS
6   CONN               UTL_SMTP.connection;
7
8   v_msg_text         VARCHAR2 (8000) := NULL;
9
10   v_msg_from         VARCHAR2 (100);
11
12   v_mailhost         VARCHAR2 (100);
13
14   v_msg_subject      VARCHAR2 (100) := v_subjectline;
15
16   v_msg_to           VARCHAR2 (1000);
17
18   v_msg_rcpt         VARCHAR2 (1000);
19
20
21
22   TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2 (200)
23      INDEX BY BINARY_INTEGER;
24
25
26
27   FILE_ARRAY         VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/
28
29   I                  BINARY_INTEGER;                      /** ARRAY INDEX **/
30
31   L_FILE_HANDLE      UTL_FILE.FILE_TYPE;                 /** FILE POINTER **/
32
33   L_DIRECTORY_NAME   VARCHAR2 (200) := fnd_profile.VALUE ('UTIL_PATH'); /** TO STORE THE PATH / DIRECTORY NAME OF THE FILE **/
34
35   l_attachment       LONG; --    VARCHAR2(32767);                 /** TO STORE THE MESSAGE **/
36
37   L_LINE             VARCHAR2 (1000); /** TO STORE THE CONTENTS OF THE LINE READ FROM THE FILE **/
38
39   L_FILE_NAME        VARCHAR2 (100);            /** TO STORE THE FILENAME **/
40
41   reply              UTL_SMTP.reply;
42
43   i_idx              NUMBER := 0;
44
45   v_osuser           VARCHAR2 (100);
46
47   v_serverhost       VARCHAR2 (100);
48
49
50
51   CURSOR cur_mail_id
52   IS                           /*select recipients mail ids from table name*/
53      SELECT 'example@email.com' FROM DUAL;
54BEGIN
55   fnd_file.put_line (fnd_file.LOG, 'Sending Email...');
56
57
58
59   v_msg_from := 'noreply@example.com';                        -- from mail id
60
61
62
63   BEGIN
64      SELECT fscpv.parameter_value smtp_host
65        INTO v_mailhost
66        FROM fnd_svc_comp_params_b   fscpb,
67             fnd_svc_comp_param_vals fscpv,
68             fnd_svc_components      fsc
69       WHERE     fscpb.parameter_id = fscpv.parameter_id
70             AND fscpv.component_id = fsc.component_id
71             AND fscpb.parameter_name = 'OUTBOUND_SERVER'
72             AND fsc.component_name = 'Workflow Notification Mailer';
73   EXCEPTION
74      WHEN NO_DATA_FOUND
75      THEN
76         o_errbuf := ' SMTP HOST not found:' || SQLERRM;
77
78         o_retcode := 1;
79
80         RAISE;
81   END;
82
83
84
85   BEGIN
86      BEGIN
87         -- Open the connection
88
89         BEGIN
90            i_idx := 0;
91
92
93
94            WHILE i_idx < 5
95            LOOP
96               reply := UTL_SMTP.open_connection (v_mailhost, 25, conn);
97
98
99
100               IF reply.code BETWEEN 400 AND 599
101               THEN
102                  fnd_file.put_line (
103                     fnd_file.LOG,
104                     'Retrying to open connection: ' || TO_CHAR (i_idx + 1));
105
106                  fnd_file.put_line (fnd_file.LOG, reply.text);
107               ELSE
108                  EXIT;
109               END IF;
110
111
112
113               i_idx := i_idx + 1;
114            END LOOP;
115
116
117
118            IF i_idx >= 5
119            THEN
120               RAISE_APPLICATION_ERROR (
121                  -20010,
122                     'Could not establish SMTP connection: '
123                  || reply.code
124                  || ' '
125                  || reply.text);
126            END IF;
127         EXCEPTION
128            WHEN OTHERS
129            THEN
130               o_errbuf := 'error :' || SQLERRM;
131
132               o_retcode := 1;
133
134               RAISE;
135         END;
136
137
138
139         -- Check for handshake
140
141         BEGIN
142            i_idx := 0;
143
144
145
146            WHILE i_idx < 5
147            LOOP
148               reply := UTL_SMTP.helo (conn, v_mailhost);
149
150
151
152               IF reply.code BETWEEN 400 AND 599
153               THEN
154                  fnd_file.put_line (
155                     fnd_file.LOG,
156                     'Retrying helo: ' || TO_CHAR (i_idx + 1));
157
158                  fnd_file.put_line (fnd_file.LOG, reply.text);
159               ELSE
160                  EXIT;
161               END IF;
162
163
164
165               i_idx := i_idx + 1;
166            END LOOP;
167
168
169
170            IF i_idx >= 5
171            THEN
172               RAISE_APPLICATION_ERROR (
173                  -20010,
174                     'Could not establish shakehand: '
175                  || reply.code
176                  || ' '
177                  || reply.text);
178            END IF;
179         EXCEPTION
180            WHEN OTHERS
181            THEN
182               o_errbuf := 'error:' || SQLERRM;
183
184               o_retcode := 1;
185
186               RAISE;
187         END;
188
189
190
191         -- Add the Sender with respect to the site user's user group
192
193         BEGIN
194            i_idx := 0;
195
196
197
198            WHILE i_idx < 5
199            LOOP
200               reply := UTL_SMTP.Mail (conn, v_msg_from);
201
202
203
204               IF reply.code BETWEEN 400 AND 599
205               THEN
206                  fnd_file.put_line (
207                     fnd_file.LOG,
208                     'Retrying mail: ' || TO_CHAR (i_idx + 1));
209
210                  fnd_file.put_line (fnd_file.LOG, reply.text);
211               ELSE
212                  EXIT;
213               END IF;
214
215
216
217               i_idx := i_idx + 1;
218            END LOOP;
219
220
221
222            IF i_idx >= 5
223            THEN
224               RAISE_APPLICATION_ERROR (
225                  -20010,
226                     'Could not initiate mail transaction with SMTP server: '
227                  || reply.code
228                  || ' '
229                  || reply.text);
230            END IF;
231         EXCEPTION
232            WHEN OTHERS
233            THEN
234               o_errbuf := 'error :' || SQLERRM;
235
236               o_retcode := 1;
237
238               RAISE;
239         END;
240
241
242
243         -- Add the reciepents
244
245         BEGIN
246            i_idx := 0;
247
248
249
250            WHILE i_idx < 5
251            LOOP
252               FOR rec_mail_id IN cur_mail_id
253               LOOP
254                  v_msg_rcpt := rec_mail_id.description;
255
256                  reply := UTL_SMTP.Rcpt (conn, v_msg_rcpt);
257
258                  v_msg_to := v_msg_to || rec_mail_id.description || '; ';
259               END LOOP;
260
261
262
263               IF reply.code BETWEEN 400 AND 599
264               THEN
265                  fnd_file.put_line (
266                     fnd_file.LOG,
267                     'Retrying rcpt: ' || TO_CHAR (i_idx + 1));
268
269                  fnd_file.put_line (fnd_file.LOG, reply.text);
270               ELSE
271                  EXIT;
272               END IF;
273
274
275
276               i_idx := i_idx + 1;
277            END LOOP;
278
279
280
281            IF i_idx >= 5
282            THEN
283               RAISE_APPLICATION_ERROR (
284                  -20010,
285                     'Could not initiate Rcpt: '
286                  || reply.code
287                  || ' '
288                  || reply.text);
289            END IF;
290         EXCEPTION
291            WHEN OTHERS
292            THEN
293               o_errbuf := 'error :' || SQLERRM;
294
295               o_retcode := 1;
296
297               RAISE;
298         END;
299
300
301
302         v_msg_to := RTRIM (v_msg_to, '; ');
303
304
305
306         -- Open the data mode
307
308         BEGIN
309            i_idx := 0;
310
311
312
313            WHILE i_idx < 5
314            LOOP
315               reply := UTL_SMTP.Open_data (conn);
316
317
318
319               IF reply.code BETWEEN 400 AND 599
320               THEN
321                  fnd_file.put_line (
322                     fnd_file.LOG,
323                     'Retrying open data: ' || TO_CHAR (i_idx + 1));
324
325                  fnd_file.put_line (fnd_file.LOG, reply.text);
326               ELSE
327                  EXIT;
328               END IF;
329
330
331
332               i_idx := i_idx + 1;
333            END LOOP;
334
335
336
337            IF i_idx >= 5
338            THEN
339               RAISE_APPLICATION_ERROR (
340                  -20010,
341                     'Could not send open_data to SMTP server: '
342                  || reply.code
343                  || ' '
344                  || reply.text);
345            END IF;
346         EXCEPTION
347            WHEN OTHERS
348            THEN
349               o_errbuf := 'error :' || SQLERRM;
350
351               o_retcode := 1;
352
353               RAISE;
354         END;
355
356
357
358         BEGIN
359            -- Subject of the email
360
361            -- Message Text in HTML format
362
363            v_msg_text := UTL_TCP.crlf || 'Attachment: Wire Payment Files.';
364
365            -- Sending the header information
366
367            UTL_SMTP.Write_data (conn,
368                                 'From: ' || v_msg_from || UTL_TCP.crlf);
369
370            UTL_SMTP.Write_data (conn, 'To: ' || v_msg_to || UTL_TCP.crlf);
371
372            --utl_smtp.Write_data(conn,'Cc: ' ||'name@domain.com' ||utl_tcp.utl_tcp.crlf);
373
374            UTL_SMTP.Write_data (
375               conn,
376               'Subject: ' || v_msg_subject || UTL_TCP.crlf);
377
378
379
380            UTL_SMTP.Write_data (conn,
381                                 'MIME-Version: ' || '1.0' || UTL_TCP.crlf);
382
383
384
385            UTL_SMTP.Write_data (
386               conn,
387                  'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"'
388               || UTL_TCP.crlf
389               || ''
390               || UTL_TCP.crlf
391               || '--DMW.Boundary.605592468'
392               || UTL_TCP.crlf
393               || 'Content-Type: text/plain;'
394               || UTL_TCP.crlf
395               || 'Content-Transfer_Encoding: 7bit'
396               || UTL_TCP.crlf
397               || UTL_TCP.crlf);
398
399            -- End of header information
400
401            UTL_SMTP.Write_data (conn, v_msg_text);
402
403            UTL_SMTP.Write_data (conn, UTL_TCP.crlf);
404
405            UTL_SMTP.Write_data (conn, UTL_TCP.crlf);
406         EXCEPTION
407            WHEN OTHERS
408            THEN
409               o_errbuf := 'error :' || SQLERRM;
410
411               o_retcode := 1;
412
413               RAISE;
414         END;
415
416
417
418         /*** START ATTACHING THE FILES ***/
419
420         BEGIN
421            FILE_ARRAY (1) := p_filename1;
422
423
424
425            FOR i IN 1 .. 1       --run the loop to attach more than one files
426            LOOP
427               IF file_array (i) IS NOT NULL
428               THEN
429                  BEGIN
430                     l_file_name := FILE_ARRAY (i);
431
432                     l_file_handle :=
433                        UTL_FILE.fopen (l_directory_name, l_file_name, 'R');
434
435                     l_attachment :=
436                           UTL_TCP.crlf
437                        || '--DMW.Boundary.605592468'
438                        || UTL_TCP.crlf
439                        || 'Content-Type: application/octet-stream; name="'
440                        || l_file_name
441                        || '"'
442                        || UTL_TCP.crlf
443                        || 'Content-Disposition: attachment; filename="'
444                        || l_file_name
445                        || '"'
446                        || UTL_TCP.crlf
447                        || 'Content-Transfer-Encoding: 7bit'
448                        || UTL_TCP.crlf
449                        || UTL_TCP.crlf;
450
451
452
453                     UTL_SMTP.write_data (conn, l_attachment);
454
455
456
457                     LOOP
458                        BEGIN
459                           UTL_FILE.get_line (l_file_handle, l_line);
460                        EXCEPTION
461                           WHEN NO_DATA_FOUND
462                           THEN
463                              EXIT;
464                        END;
465
466
467
468                        l_attachment := l_line || UTL_TCP.crlf;
469
470                        UTL_SMTP.write_data (conn, l_attachment);
471                     END LOOP;
472                  EXCEPTION
473                     WHEN NO_DATA_FOUND
474                     THEN
475                        fnd_file.put_line (
476                           fnd_file.LOG,
477                              ' Invalid Operation in Mail attempt using UTL_SMTP:'
478                           || SQLERRM);
479                     WHEN UTL_FILE.invalid_path
480                     THEN
481                        fnd_file.put_line (
482                           fnd_file.LOG,
483                              ' Invalid Operation in Mail attempt using UTL_SMTP:'
484                           || SQLERRM);
485                     WHEN OTHERS
486                     THEN
487                        fnd_file.put_line (
488                           fnd_file.LOG,
489                              ' Invalid Operation in Mail attempt using UTL_SMTP:'
490                           || SQLERRM);
491                  END;
492
493
494
495                  UTL_FILE.fclose (l_file_handle);
496               END IF;
497            END LOOP;
498
499
500
501            l_attachment :=
502               UTL_TCP.crlf || '--DMW.Boundary.605592468--' || UTL_TCP.crlf;
503
504            UTL_SMTP.write_data (conn, l_attachment);
505         EXCEPTION
506            WHEN OTHERS
507            THEN
508               fnd_file.put_line (
509                  fnd_file.LOG,
510                     ' Invalid Operation in Mail attempt using UTL_SMTP:'
511                  || SQLERRM);
512         END;
513
514
515
516         UTL_SMTP.Close_data (conn);
517
518         UTL_SMTP.Quit (conn);
519
520         COMMIT;
521      END;
522   EXCEPTION
523      WHEN OTHERS
524      THEN
525         fnd_file.put_line (
526            fnd_file.LOG,
527            ' Invalid Operation in Mail attempt using UTL_SMTP:' || SQLERRM);
528   END;
529END XXSEND_EMAIL;
530/

1DECLARE
2   o_errbuf    VARCHAR2 (2000);
3
4   o_retcode   NUMBER;
5BEGIN
6   XXSEND_EMAIL ('testfile.pdf',
7                 'Test Subject',
8                 o_errbuf,
9                 o_retcode);
10   DBMS_OUTPUT.put_line (o_errbuf);
11END;