分类： Oracle
2010-04-29 14:53:16
1.* ** ****************************************************************************************** ** *
2. ** DESCRIPTION OF SCRIPT : THIS PROCESS IS FOR SENDING MAIL WITH THE ATTACHMENT
3.
4.* ** ****************************************************************************************** ** */
5.
6.CREATE OR REPLACE PROCEDURE MAIL_FILE( SUBJECT IN VARCHAR2,
7. MESSAGE IN VARCHAR2,
8. MAX_SIZE IN NUMBER DEFAULT 9999999999,
9. FILENAME1 IN VARCHAR2 DEFAULT NULL,
10. FILENAME2 IN VARCHAR2 DEFAULT NULL,
11. FILENAME3 IN VARCHAR2 DEFAULT NULL,
12. RETURN_DESC OUT VARCHAR2) IS
13.
14. L_SMTP_SERVER VARCHAR2(20); /** TO STORE THE IP ADDRESS OF THE SMTP SERVER **/
15. L_SMTP_SERVER_PORT NUMBER; /** TO STORE THE SMTP PORT OF THE SMTP SERVER **/
16. L_DIRECTORY_NAME VARCHAR2(200); /** TO STORE THE PATH / DIRECTORY NAME OF THE FILE **/
17. L_FILE_NAME VARCHAR2(100); /** TO STORE THE FILENAME **/
18. L_LINE VARCHAR2(1000); /** TO STORE THE CONTENTS OF THE LINE READ FROM THE FILE **/
19. CRLF VARCHAR2(2):= CHR(13) || CHR(10);
20. L_MESG VARCHAR2(32767); /** TO STORE THE MESSAGE **/
21. CONN UTL_SMTP.CONNECTION; /** SMTP CONNECTION VARIABLE **/
22. L_MSG_TO VARCHAR2(2000); /** TO STORE THE LIST OF RECIPEINTS **/
23. L_SENDER_NAME VARCHAR2(200); /** TO STORE THE NAME OF THE SENDER **/
24.
25. TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
26.
27. FILE_ARRAY VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/
28. I BINARY_INTEGER; /** ARRAY INDEX **/
29.
30. L_FILE_HANDLE UTL_FILE.FILE_TYPE; /** FILE POINTER **/
31. L_SLASH_POS NUMBER; /** TO STORE THE POSITION OF \ IN THE FILE NAME **/
32.
33. L_MESG_LEN NUMBER; /** TO STORE THE LENGHT OF THE MESSAGE **/
34.
35. ABORT_PROGRAM EXCEPTION; /** USER DEFINED EXCEPTION **/
36.
37. MESG_LENGTH_EXCEEDED BOOLEAN := FALSE; /** BOOLEAN VARIABLE TO TRAP IF THE MESSAGE LENGHT IS EXCEEDING **/
38.
39. RETURN_DESC1 VARCHAR2(2000); /** VARIABLE TO STORE THE ERROR MESSAGE. TO BE RETURNED TO THE CALLING PROGRAM **/
40.
41. /*** CURSOR TO SELECT THE RECIPEINTS AND MARK A COPY TO THE SENDER AS WELL ***/
42.
43. CURSOR RECIPIENT_CUR IS
44. SELECT VAL
45. FROM MISC
46. WHERE KEY1 = 'EMAIL'
47. AND (KEY2 = 'RECIPIENT EMAIL'
48. OR KEY2 = 'SENDER EMAIL');
49.
50.
51./***
52. ** THIS PROCEDURE FETCHES THE VALUES FOR MISCELLANEOUS PARAMETERS
53.***/
54.
55.PROCEDURE FETCH_MISC IS
56.
57.BEGIN
58. RETURN_DESC1 := '11 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = SMTP SERVER. ';
59. L_SMTP_SERVER := PACKAGE.GET_PVAL('SMTP SERVER');
60.
61. RETURN_DESC1 := '22 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = SMTP PORT. ';
62. L_SMTP_SERVER_PORT := PACKAGE.GET_PVAL('SMTP PORT');
63.
64. RETURN_DESC1 := '33 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = TICKET_EMAIL AND KEY2 =SENDER EMAIL. ';
65. L_SENDER_NAME := PACKAGE.GET_PVAL('TICKET_EMAIL','SENDER EMAIL');
66.
67.
68.EXCEPTION
69. WHEN OTHERS THEN
70. RAISE ABORT_PROGRAM;
71.
72.END FETCH_MISC;
73.
74.
75./**** MAIN PROGRAM STARTS HERE ****/
76.
77.BEGIN
78./*** FETCHING MISCELLANEOUS PARAMETERS ***/
79.
80. FETCH_MISC;
81.
82./*** ASSIGNING FILE NAMES TO ARRAY ***/
83.
84. FILE_ARRAY(1) := FILENAME1;
85. FILE_ARRAY(2) := FILENAME2;
86. FILE_ARRAY(3) := FILENAME3;
87.
88. RETURN_DESC1 := '10 - E: THERE WAS AN ERROR IN OPENING CONNECTION. ';
89. CONN:= UTL_SMTP.OPEN_CONNECTION( L_SMTP_SERVER, L_SMTP_SERVER_PORT ); /** OPEN CONNECTION ON THE SERVER **/
90.
91. UTL_SMTP.HELO( CONN, L_SMTP_SERVER ); /** DO THE INITIAL HAND SHAKE **/
92.
93. UTL_SMTP.MAIL( CONN, L_SENDER_NAME );
94.
95. RETURN_DESC1 := '20 - E: THERE WAS AN ERROR IN CREATING RECEIPIENTS. ';
96.
97. FOR L_RECIPIENT_CUR_REC IN RECIPIENT_CUR /** LOOP FOR MULTIPLE RECEIPEINTS **/
98. LOOP
99.
100. L_MSG_TO := L_RECIPIENT_CUR_REC.AM_PAR_VAL;
101. UTL_SMTP.RCPT( CONN, L_MSG_TO );
102.
103. END LOOP;
104.
105.
106. UTL_SMTP.OPEN_DATA ( CONN );
107.
108./*** GENERATE THE MIME HEADER ***/
109.
110. RETURN_DESC1 := '30 - E: THERE WAS AN ERROR IN GENERATING MIME HEADER. ';
111.
112. L_MESG:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CRLF ||
113. 'From: ' || L_SENDER_NAME || CRLF ||
114. 'Subject: ' || SUBJECT || CRLF ||
115. 'To: ' || L_MSG_TO || CRLF ||
116. 'Mime-Version: 1.0' || CRLF ||
117. 'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || CRLF ||
118. '' || CRLF ||
119. 'This is a Mime message, which your current mail reader may not' || CRLF ||
120. 'understand. Parts of the message will appear as text. If the remainder' || CRLF ||
121. 'appears as random characters in the message body, instead of as' || CRLF ||
122. 'attachments, then you''ll have to extract these parts and decode them' || CRLF ||
123. 'manually.' || CRLF ||
124. '' || CRLF ||
125. '--DMW.Boundary.605592468' || CRLF ||
126. 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || CRLF ||
127. 'Content-Disposition: inline; filename="message.txt"' || CRLF ||
128. 'Content-Transfer-Encoding: 7bit' || CRLF ||
129. '' || CRLF ||
130. MESSAGE || CRLF || CRLF || CRLF ;
131.
132. L_MESG_LEN := LENGTH(L_MESG);
133.
134. IF L_MESG_LEN > MAX_SIZE THEN
135.
136. MESG_LENGTH_EXCEEDED := TRUE;
137.
138. END IF;
139.
140. RETURN_DESC1 := '40 - E: THERE WAS AN ERROR IN WRITING MESSAGE TO CONNECTION. ';
141.
142. UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
143.
144. /*** START ATTACHING THE FILES ***/
145.
146. FOR I IN 1..3 LOOP
147.
148. EXIT WHEN MESG_LENGTH_EXCEEDED;
149.
150. IF FILE_ARRAY(I) IS NOT NULL THEN
151.
152. BEGIN
153.
154. L_SLASH_POS := INSTR(FILE_ARRAY(I), '/', -1 );
155.
156. IF L_SLASH_POS = 0 THEN
157.
158. L_SLASH_POS := INSTR(FILE_ARRAY(I), '\', -1 );
159.
160. END IF;
161.
162. L_DIRECTORY_NAME := SUBSTR(FILE_ARRAY(I), 1, L_SLASH_POS - 1 );
163.
164. L_FILE_NAME := SUBSTR(FILE_ARRAY(I), L_SLASH_POS + 1 );
165.
166. RETURN_DESC1 := '50 - E: THERE WAS AN ERROR IN OPENING FILE. ';
167.
168. L_FILE_HANDLE := UTL_FILE.FOPEN(L_DIRECTORY_NAME, L_FILE_NAME, 'R' );
169.
170. L_MESG := CRLF || '--DMW.Boundary.605592468' || CRLF ||
171. 'Content-Type: application/octet-stream; name="' || L_FILE_NAME || '"' || CRLF ||
172. 'Content-Disposition: attachment; filename="' || L_FILE_NAME || '"' || CRLF ||
173. 'Content-Transfer-Encoding: 7bit' || CRLF || CRLF ;
174.
175. L_MESG_LEN := L_MESG_LEN + LENGTH(L_MESG);
176.
177. UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
178.
179. LOOP
180.
181. RETURN_DESC1 := '60 - E: THERE WAS AN ERROR IN READING FILE. ';
182.
183. UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);
184.
185. IF L_MESG_LEN + LENGTH(L_LINE) > MAX_SIZE THEN
186.
187. L_MESG := '*** truncated ***' || CRLF;
188.
189. UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
190.
191. MESG_LENGTH_EXCEEDED := TRUE;
192.
193. EXIT;
194.
195. END IF;
196.
197. L_MESG := L_LINE || CRLF;
198.
199. UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
200.
201. L_MESG_LEN := L_MESG_LEN + LENGTH(L_MESG);
202.
203. END LOOP;
204.
205. EXCEPTION
206. WHEN NO_DATA_FOUND THEN
207. NULL;
208.
209. WHEN UTL_FILE.INVALID_PATH THEN
210. RAISE ABORT_PROGRAM;
211.
212. WHEN OTHERS THEN
213. RAISE ABORT_PROGRAM;
214.
215. END;
216.
217. L_MESG := CRLF;
218.
219. UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
220.
221. UTL_FILE.FCLOSE(L_FILE_HANDLE);
222.
223. END IF;
224.
225. END LOOP;
226.
227. RETURN_DESC1 := '70 - E: THERE WAS AN ERROR IN CLOSING MIME BOUNDARY. ';
228.
229. L_MESG := CRLF || '--DMW.Boundary.605592468--' || CRLF;
230.
231. UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
232.
233. UTL_SMTP.CLOSE_DATA( CONN );
234.
235. UTL_SMTP.QUIT( CONN );
236.
237.EXCEPTION
238. WHEN ABORT_PROGRAM THEN
239. RETURN_DESC := RETURN_DESC1;
240.
241. WHEN OTHERS THEN
242. RETURN_DESC := RETURN_DESC1;
243.
244.END;
