dba123chameleon82,
еще предложение
менять имена границ подразделов(boundary), например
S7RUMailPart-001-xxxxxxxx-xxxxxxxxxxxxxxxxx
S7RUMailPart-002-xxxxxxxx-xxxxxxxxxxxxxxxxx
где ххх может быть счетчиком, уникальным идентификатором, цифровой сверткой/подписью и т.д.
и тебе учет отправленных и сормовцам метки :)
надо обдумать как это сделать. идея интересная. Думаю, можно внедрить допнастройками пакета. А вот где хранить счетчик? создавать ради этого сиквенс???
---
Обновил пакет. Убрал немного мусора, перевел все на UTF-8 (протестил на бате и аутлуке), исправил ошибку с длинным именем заголовка письма и именами отправителей/получателей и что-то еще по мелочи, и замечание от IgorD. Вот моя последняя версия )
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.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
CREATE OR REPLACE PACKAGE MAIL_PKG IS
-- --------------------------------------------------------------------------
-- Name : MAIL_PKG
-- Author : Nekrasov Alexander
-- Description : Mail package, send email with attachments
-- Ammedments :
-- When Who What
-- =========== ========== =================================================
-- 22-JAN-2010 Nekrasov A. Initial Creation
-- 26-MAY-2010 Nekrasov A. Update package bugs
-- --------------------------------------------------------------------------
/* EXAMPLE:
1) Short text email
BEGIN
MAIL_PKG.SEND( 'a.ivanov@yourcomany.ru','Test subject', 'Some message!');
END;
2) Extension Email with attacments
BEGIN
MAIL_PKG.SET_MAILSERVER ('localhost',25);
MAIL_PKG.SET_AUTH ('a.nekrasov','password');
MAIL_PKG.ADD_ATTACHMENT( 'ODPDIR'
,'girl3d.jpeg'
,'image/jpeg'
);
MAIL_PKG.SEND( mailto => 'A. Ivanov <a.ivanov@yourcomany.ru>;, O.Petrov <o.petrov@yourcompany.ru>;'
, subject => 'Test subject'
, message => 'Some <b>bold</b> message!'
, mailfrom => 'Oracle Notify <no-reply@yourcompany.ru>;'
, mimetype => 'text/html'
, priority => 1
);
END;
*/
-- SET_MAILSERVER:
-- Set up mail server for send emails. Default Localhost
-- IN
-- MAILSERVER is ip or url of mail server
-- MAILPORT is port for mail server. Default 25
PROCEDURE SET_MAILSERVER ( mailserver varchar2
, mailport number default 25
);
-- SET_AUTH
-- Set authorization on smtp server
-- IN
-- AUTH_USER is authorization user
-- AUTH_PASS is password for AUTH_USER
--
-- Execute SET_AUTH(); -- for disable authorization
PROCEDURE SET_AUTH ( auth_user varchar2 default null
, auth_pass varchar2 default null
);
-- ENCODE:
-- Encodes string to email compatible view
-- IN
-- STR is string to convert
-- TP is type of convert:
-- B - is base64 encoding
FUNCTION ENCODE( str IN VARCHAR2
, tp IN VARCHAR2 DEFAULT 'Q') RETURN VARCHAR2;
-- PREPARE
-- Prepare configs for email.
PROCEDURE PREPARE;
-- ADD_RCPT
-- Add recipient to recipients list exploded by ','
-- STR is variable with recipients
-- RCPTMAIL is recipient mail added to STR
-- RCPTNAME is recipient name added to STR
-- Example: str='user1@domain.ru' rcptmail='user2@domain.ru'
-- after => str='user1@domain.ru, user2@domain.ru'
PROCEDURE ADD_RCPT( str IN OUT VARCHAR2
, rcptmail IN VARCHAR2
, rcptname IN VARCHAR2 DEFAULT NULL);
-- ADD_ATTACHMENT
-- Add attachment to attachments list to email
-- IN
-- DIRNAME is logical link to access physical directories of server. See DBA_DIRECTORIES table
-- FILENAME is name of file to attach
-- MIMETYPE is mime-type for sended file
-- NAME is name for attached file for email. Default eq FILENAME
PROCEDURE ADD_ATTACHMENT ( dirname IN varchar2
, filename IN varchar2
, mimetype IN varchar2 DEFAULT 'text/plain'
, name IN varchar2 DEFAULT NULL
);
-- SEND
-- Send email with attachments to recipient
-- IN
-- MAILTO is name and email addresses of recipients ( ex. "user@domain.com"
-- , "User Name <user@domain.com>;", "User1 <user1@domain>;, User2 <user2@domain>;")
-- SUBJECT is subject of email
-- MESSAGE is message of email
-- MAILFROM is name and email of sender. (ex. "no-reply@domain", "Notify system <no-reply@domain>;")
-- MIMETYPE is mime-type of message. Available values is 'text/plain' and 'text/html'
-- PRIORITY is priority of mail (1 - High, 2 - Highest, 3 - Normal, 4 - Lowest, 5 - Low)
PROCEDURE SEND ( mailto IN VARCHAR2
, subject IN VARCHAR2
, message IN VARCHAR2
, mailfrom IN VARCHAR2 DEFAULT NULL
, mimetype IN VARCHAR2 DEFAULT 'text/plain'
, priority IN NUMBER DEFAULT NULL
);
END MAIL_PKG;
/
CREATE OR REPLACE PACKAGE BODY MAIL_PKG
IS
mailserver VARCHAR2( 30 ):='localhost';
mailport INTEGER:= 25 ;
auth_user VARCHAR2( 50 );
auth_pass VARCHAR2( 50 );
crlf VARCHAR2( 2 ) := utl_tcp.CRLF; -- chr(13)||chr(10);
type attach_row is record ( dirname varchar2( 30 )
, filename varchar2( 30 )
, name varchar2( 30 )
, mimetype varchar2( 30 )
);
type attach_list is table of attach_row;
attachments attach_list;
type rcpt_row is record ( rcptname varchar2( 100 )
, rcptmail varchar2( 50 )
);
type rcpt_list is table of rcpt_row;
PROCEDURE SET_MAILSERVER ( mailserver varchar2
, mailport number default 25
) IS
BEGIN
MAIL_PKG.mailserver := mailserver;
MAIL_PKG.mailport := mailport;
END;
PROCEDURE SET_AUTH ( auth_user varchar2 default null
, auth_pass varchar2 default null
) IS
BEGIN
MAIL_PKG.auth_user := auth_user;
MAIL_PKG.auth_pass := auth_pass;
END;
FUNCTION ENCODE(str IN VARCHAR2, tp IN VARCHAR2 DEFAULT 'Q') RETURN VARCHAR2 IS
BEGIN
IF tp='B' THEN
RETURN '=?utf-8?b?'|| UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw (CONVERT (SUBSTR(str, 1 , 24 ), 'UTF8'))))|| '?='
|| CASE WHEN SUBSTR(str, 25 ) IS NOT NULL THEN crlf || ' '|| ENCODE(SUBSTR(str, 25 ),tp) END;
ELSIF tp='Q' THEN
RETURN '=?utf-8?q?' || UTL_RAW.cast_to_varchar2(utl_encode.QUOTED_PRINTABLE_ENCODE(utl_raw.cast_to_raw(CONVERT (SUBSTR(str, 1 , 8 ), 'UTF8') ))) || '?='
|| CASE WHEN SUBSTR(str, 9 ) IS NOT NULL THEN crlf || ' '|| ENCODE(SUBSTR(str, 9 ),tp) END;
ELSE
RETURN str;
END IF;
END;
PROCEDURE PREPARE
IS
BEGIN
MAIL_PKG.attachments:=MAIL_PKG.attach_list();
END;
PROCEDURE ADD_RCPT( str IN OUT VARCHAR2
, rcptmail IN VARCHAR2
, rcptname IN VARCHAR2 DEFAULT NULL) IS
rcpt varchar2( 255 );
BEGIN
rcpt:=CASE WHEN rcptname is null THEN
rcptmail
ELSE
trim(replace(replace(rcptname,',',' '),';',' '))||' <'|| rcptmail ||'>'
END;
IF trim(str) is NULL THEN
str := trim(rcpt);
ELSE
str := str||', '||trim(rcpt);
END IF;
END;
PROCEDURE ADD_ATTACHMENT ( dirname IN varchar2
, filename IN varchar2
, mimetype IN varchar2 DEFAULT 'text/plain'
, name IN varchar2 DEFAULT NULL
)
IS
v_fl BFILE :=BFILENAME(dirname,filename);
BEGIN
IF DBMS_LOB.FILEEXISTS (v_fl)= 1 THEN
MAIL_PKG.attachments.extend;
MAIL_PKG.attachments(MAIL_PKG.attachments.count).dirname:=dirname;
MAIL_PKG.attachments(MAIL_PKG.attachments.count).filename:=filename;
MAIL_PKG.attachments(MAIL_PKG.attachments.count).name:=nvl(name,filename);
MAIL_PKG.attachments(MAIL_PKG.attachments.count).mimetype:=mimetype;
ELSE
RAISE_APPLICATION_ERROR(- 20001 , 'File is not exists');
END IF;
END;
FUNCTION CREATE_RCPT_LIST(mailto IN VARCHAR2) RETURN MAIL_PKG.rcpt_list IS
v_mailto VARCHAR2( 4096 ) := replace(mailto,';',',')||',';
pntr INTEGER;
buf VARCHAR2( 255 );
rcptmail VARCHAR2( 255 );
rcptlist MAIL_PKG.rcpt_list:=MAIL_PKG.rcpt_list();
BEGIN
FOR maxrcptnts IN 1 .. 50
LOOP
pntr:=INSTR(v_mailto,','); buf := substr(v_mailto, 1 ,pntr- 1 );
IF pntr> 0 THEN
IF INSTR(buf,'<')> 0 AND INSTR(buf,'>')> 0 THEN
rcptmail:= SUBSTR(buf,INSTR(buf,'<')+ 1 ,INSTR(SUBSTR(buf,INSTR(buf,'<')+ 1 ),'>')- 1 );
IF rcptmail IS NOT NULL THEN
rcptlist.extend;
rcptlist(rcptlist.count).rcptmail := TRIM(rcptmail);
rcptlist(rcptlist.count).rcptname := TRIM(SUBSTR(buf, 1 ,INSTR(buf,'<')- 1 ));
END IF;
ELSE
rcptmail := TRIM(buf);
IF rcptmail IS NOT NULL THEN
rcptlist.extend;
rcptlist(rcptlist.count).rcptmail:= TRIM(rcptmail);
END IF;
END IF;
ELSE
EXIT;
END IF;
v_mailto := substr(v_mailto,pntr+ 1 );
END LOOP;
RETURN rcptlist;
END;
PROCEDURE SEND ( mailto IN VARCHAR2
, subject IN VARCHAR2
, message IN VARCHAR2
, mailfrom IN VARCHAR2 DEFAULT NULL
, mimetype IN VARCHAR2 DEFAULT 'text/plain'
, priority IN NUMBER DEFAULT NULL
)
IS
v_Mail_Conn utl_smtp.Connection;
boundary VARCHAR2( 50 ) := '-----7D81B75CCC90DFRW4F7A1CBD';
vFile BFILE;
vRAW RAW( 32767 );
amt CONSTANT BINARY_INTEGER := 48 ; -- 48bytes binary convert to 128bytes of base64.
v_amt BINARY_INTEGER;
ps BINARY_INTEGER := 1 ;
v_mime VARCHAR2( 30 );
reply UTL_SMTP.REPLY;
replies UTL_SMTP.REPLIES;
rcptlist MAIL_PKG.rcpt_list;
sndr MAIL_PKG.rcpt_row;
BEGIN
rcptlist:=create_rcpt_list(mailto);
IF rcptlist.count= 0 THEN
RAISE_APPLICATION_ERROR(- 20001 , 'Recipients requered');
END IF;
IF mimetype<>'text/html' and mimetype<>'text/plain' THEN
RAISE_APPLICATION_ERROR(- 20001 , 'MimeType must be "text/html" or "text/plain"');
ELSE
v_mime:=mimetype;
END IF;
v_Mail_Conn := utl_smtp.Open_Connection(MAIL_PKG.mailserver, MAIL_PKG.mailport);
replies:=utl_smtp.Ehlo(v_Mail_Conn,MAIL_PKG.mailserver);
if create_rcpt_list(mailfrom).count> 0 then
sndr := create_rcpt_list(mailfrom)( 1 );
else
sndr := create_rcpt_list( 'mail@' || UTL_INADDR.GET_HOST_NAME )( 1 ); -- host from oracle-server
-- sndr := create_rcpt_list( 'mail@' || substr(replies(1).text,1,instr(replies(1).text,' ')-1))(1); -- Addr from ehlo answer
end if;
if mail_pkg.auth_user is not null then
for x IN 1 .. replies.count loop
dbms_output.put_line(replies(x).text);
IF INSTR(replies(x).text,'AUTH')> 0 then -- If server supply authorization
utl_smtp.command(v_Mail_Conn, 'AUTH LOGIN');
utl_smtp.command(v_Mail_Conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(auth_user))));
utl_smtp.command(v_Mail_Conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(auth_pass))));
exit;
END IF;
end loop;
end if;
utl_smtp.Mail(v_Mail_Conn, sndr.rcptmail);
FOR rcpts IN 1 .. rcptlist.count
LOOP
utl_smtp.Rcpt(v_Mail_Conn, rcptlist(rcpts).rcptmail);
END LOOP;
utl_smtp.open_data(v_Mail_Conn); -- open data sheet
utl_smtp.write_data(v_Mail_Conn, 'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss','NLS_DATE_LANGUAGE = ''american''') || crlf);
utl_smtp.write_data(v_Mail_Conn, 'From: ');
if sndr.rcptname is not null then
utl_smtp.write_data(v_Mail_Conn, MAIL_PKG.ENCODE(sndr.rcptname) ||' <'|| sndr.rcptmail || '>');
else
utl_smtp.write_data(v_Mail_Conn, sndr.rcptmail);
end if;
utl_smtp.write_data(v_Mail_Conn, crlf );
utl_smtp.write_data(v_Mail_Conn, 'Subject: '|| MAIL_PKG.ENCODE(subject) || crlf );
utl_smtp.write_data(v_Mail_Conn, 'To: ');
FOR rcpts IN 1 .. rcptlist.count
LOOP
if rcpts> 1 then
utl_smtp.write_data(v_Mail_Conn, ',');
end if;
if rcptlist(rcpts).rcptname is not null then
utl_smtp.write_data(v_Mail_Conn, MAIL_PKG.ENCODE(rcptlist(rcpts).rcptname) ||' <'|| rcptlist(rcpts).rcptmail || '>');
else
utl_smtp.write_data(v_Mail_Conn, rcptlist(rcpts).rcptmail);
end if;
END LOOP;
utl_smtp.write_data(v_Mail_Conn, crlf );
IF priority IS NOT NULL and priority BETWEEN 1 AND 5 THEN
utl_smtp.write_data(v_Mail_Conn, 'X-Priority: ' || priority || crlf );
END IF;
utl_smtp.write_data(v_Mail_Conn, 'MIME-version: 1.0' || crlf );
utl_smtp.write_data(v_Mail_Conn, 'Content-Type: multipart/mixed;'|| crlf );
utl_smtp.write_data(v_Mail_Conn, ' boundary="'||boundary||'"'|| crlf );
utl_smtp.write_data(v_Mail_Conn, crlf );
--Message
utl_smtp.write_data(v_Mail_Conn, '--'|| boundary || crlf );
utl_smtp.write_data(v_Mail_Conn, 'Content-Type: '||v_mime||'; charset="utf-8"'|| crlf );
utl_smtp.write_data(v_Mail_Conn, 'Content-Transfer-Encoding: 8bit'|| crlf );
utl_smtp.write_data(v_Mail_Conn, crlf );
utl_smtp.write_raw_data(v_Mail_Conn, utl_raw.cast_to_raw(CONVERT(message,'UTF8')));
utl_smtp.write_data(v_Mail_Conn, crlf );
utl_smtp.write_data(v_Mail_Conn, crlf );
--Attachments
IF MAIL_PKG.attachments is not null THEN
FOR x IN 1 .. MAIL_PKG.attachments.count LOOP
utl_smtp.write_data(v_Mail_Conn, '--'|| boundary || crlf );
utl_smtp.write_data(v_Mail_Conn, 'Content-Type: '||MAIL_PKG.attachments(x).mimetype||';'|| crlf );
utl_smtp.write_data(v_Mail_Conn, ' name="');
utl_smtp.write_raw_data(v_Mail_Conn,utl_raw.cast_to_raw(MAIL_PKG.attachments(x).name));
utl_smtp.write_data(v_Mail_Conn, '"' || crlf);
utl_smtp.write_data(v_Mail_Conn, 'Content-Transfer-Encoding: base64'|| crlf );
utl_smtp.write_data(v_Mail_Conn, 'Content-Disposition: attachment;'|| crlf );
utl_smtp.write_data(v_Mail_Conn, ' filename="' || MAIL_PKG.ENCODE(MAIL_PKG.attachments(x).name) || '"' || crlf);
utl_smtp.write_data(v_Mail_Conn, crlf );
vFile := BFILENAME(MAIL_PKG.attachments(x).dirname,MAIL_PKG.attachments(x).filename);
dbms_lob.fileopen(vFile, dbms_lob.file_readonly);
ps:= 1 ; v_amt:=amt;
LOOP
BEGIN
dbms_lob.read (vFile, v_amt, ps, vRAW);
ps := ps + v_amt;
utl_smtp.write_raw_data(v_Mail_Conn, UTL_ENCODE.base64_encode(vRAW));
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
END LOOP;
dbms_lob.fileclose(vFile);
utl_smtp.write_data(v_Mail_Conn, crlf );
utl_smtp.write_data(v_Mail_Conn, crlf );
END LOOP;
END IF;
-- Final Boundary
utl_smtp.write_data(v_Mail_Conn, '--' || boundary || '--');
utl_smtp.close_data(v_Mail_Conn);
utl_smtp.quit(v_Mail_Conn);
-- Clear attachments
MAIL_PKG.attachments:=MAIL_PKG.attach_list();
EXCEPTION
WHEN OTHERS THEN
BEGIN
MAIL_PKG.attachments:=MAIL_PKG.attach_list();
utl_smtp.rset(v_Mail_Conn);
utl_smtp.quit(v_Mail_Conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;
RAISE;
END;
BEGIN
MAIL_PKG.attachments:=MAIL_PKG.attach_list();
END;
/
|
