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
| CREATE OR REPLACE TRIGGER OWNER.TRIG_SEND_EMAIL_OT
AFTER INSERT
ON OWNER.MAIL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Declare
/*
=======================================
-- Trigger pour l'envoie des emails
=======================================
*/
v_From OWNER.MAIL.MLQ100%type := :new.MLQ100;
v_Recipient1 OWNER.MAIL.MLQ120%type := :new.MLQ120;
v_Recipient2 OWNER.MAIL.MLQ143%type := :new.MLQ143;
v_Subject OWNER.MAIL.MLDL011%type := :new.MLDL011;
v_Mail_Host VARCHAR2(30) := '10.10.10.49';
v_Mail_Conn utl_smtp.Connection;
v_message1 OWNER.MAIL.MLAPTA%type := :new.MLAPTA;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient1);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient2);
UTL_SMTP.OPEN_DATA(v_Mail_Conn);
UTL_SMTP.WRITE_RAW_DATA(v_Mail_Conn,
UTL_RAW.CAST_TO_RAW( 'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient1 || crlf ||
'Cc: ' || v_Recipient2 || crlf ||
'Return-Receipt-To: <a href="mailto:test1@gmail.com">test1@gmail.com</a> '|| crlf ||
'X-Confirm-Reading-To: <a href="mailto:test2@gmail.com">test2@gmail.com</a> '|| crlf ||
v_message1|| crlf || -- Message body
' '|| crlf ||
'========================================'|| crlf ||
'Centre de Relation Clients '|| crlf ||
'========================================'|| crlf));
UTL_SMTP.WRITE_DATA(v_Mail_Conn, UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA(v_Mail_Conn);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/ |
