Copy Code code as follows:
CREATE OR REPLACE PROCEDURE procsendemail (P_txt VARCHAR2,
P_sub VARCHAR2,
P_sendor VARCHAR2,
P_receiver VARCHAR2,
P_server VARCHAR2,
P_port number DEFAULT 25,
P_NEED_SMTP INT DEFAULT 0,
P_user VARCHAR2 DEFAULT NULL,
P_pass VARCHAR2 DEFAULT NULL,
P_filename VARCHAR2 DEFAULT NULL,
P_encode VARCHAR2 DEFAULT ' bit 7 ')
Authid Current_User is
Copy Code code as follows:
/*
Role: Send mail with Oracle
Main function: 1, support multiple recipients.
2. Support Chinese
3, Support cc person
4. Support for attachments larger than 32K
5. Support multiple lines of text
6. Support Multiple Attachments
7, supporting text attachments and binary accessories
8. Support HTML format
8. Support
Author: Suk
Parameter description:
P_txt: Message body
P_SUB: Message headers
P_sendoraddress: Sender Email Address
P_receiveraddress: Receive address that can be sent to multiple addresses at the same time, between addresses with "," or ";" Separated
P_emailserver: Mail server address, can be a domain name or IP
P_port: Mail server port
P_NEED_SMTP: SMTP authentication required, 0 means not required, 1 means required
P_USER:SMTP Authentication Required User Name
P_PASS:SMTP Authentication Required Password
P_filename: The attachment name must contain the full path, such as "D:tempa.txt".
You can have multiple attachments, and the attachment names are separated by commas or semicolons.
P_encode: Attachment encoding conversion format, where p_encode= ' bit 7 ' indicates text type attachment
P_encode= ' base64 ' represents binary type attachments
Attention:
1, for the text type of attachment, can not be sent in base64 way, or error
2, for multiple attachments can only be sent in the same format
*/
L_crlf VARCHAR2 (2): = Utl_tcp. CRLF;
L_sendoraddress VARCHAR2 (4000);
L_splite VARCHAR2 (10): = ' + + ';
Boundary CONSTANT VARCHAR2 (256): = '-----Bysuk ';
First_boundary CONSTANT VARCHAR2 (256): = '--' | | Boundary | | L_crlf;
Last_boundary CONSTANT VARCHAR2 (256): = '--' | | Boundary | | '--' ||
L_crlf;
Multipart_mime_type CONSTANT VARCHAR2 (256): = ' multipart/mixed; boundary= "' | |
Boundary | | '"';
/* The following sections are used to send a sophomore into the attachment of the variable/
L_fil BFILE;
L_file_len number;
L_modulo number;
L_pieces number;
L_file_handle Utl_file. File_type;
L_amt Binary_integer: = 672 * 3; /* ensures proper format; 2016 */
L_filepos Pls_integer: = 1; /* pointer for the file */
L_chunks number;
L_buf RAW (2100);
L_data RAW (2100);
L_max_line_width number: = 54;
L_directory_base_name VARCHAR2 (MB): = ' dir_for_send_mail ';
L_line VARCHAR2 (1000);
L_MESG VARCHAR2 (32767);
/* The above part is to send a sophomore into the accessories used in the variable * *
TYPE Address_list is TABLE of VARCHAR2 (m) INDEX by Binary_integer;
My_address_list address_list;
TYPE Acct_list is TABLE of VARCHAR2 (m) INDEX by Binary_integer;
My_acct_list acct_list;
-------------------------------------returns the directory or name of the attachment source file--------------------------------------
Copy Code code as follows:
FUNCTION Get_file (p_file VARCHAR2, P_get INT) return VARCHAR2 is
--p_get=1 represents the return directory
--p_get=2 Return file name
L_file VARCHAR2 (1000);
BEGIN
IF INSTR (P_file, ') > 0 THEN
--windows
IF p_get = 1 THEN
L_file: = SUBSTR (P_file, 1, INSTR (P_file, ',-1)-1);
elsif p_get = 2 THEN
L_file: = SUBSTR (P_file,-(LENGTH (P_file)-INSTR (P_file, ',-1)));
End IF;
Elsif INSTR (P_file, '/') > 0 THEN
--linux/unix
IF p_get = 1 THEN
L_file: = SUBSTR (P_file, 1, INSTR (p_file, '/',-1)-1);
elsif p_get = 2 THEN
L_file: = SUBSTR (P_file,
-(LENGTH (P_file)-INSTR (P_file, '/',-1));
End IF;
End IF;
return l_file;
End;
---------------------------------------------Delete directory------------------------------------
Copy Code code as follows:
PROCEDURE drop_directory (P_directory_name VARCHAR2) is
BEGIN
EXECUTE IMMEDIATE ' Drop directory ' | | P_directory_name;
EXCEPTION
When others THEN
NULL;
End;
--------------------------------------------------Create directory-----------------------------------------
Copy Code code as follows:
PROCEDURE create_directory (p_directory_name VARCHAR2, P_dir VARCHAR2) is
BEGIN
EXECUTE IMMEDIATE ' Create directory ' | | P_directory_name | | ' as ' ' | |
P_dir | | '''';
EXECUTE IMMEDIATE ' Grant Read,write on directory ' | | P_directory_name | |
' to public ';
EXCEPTION
When others THEN
RAISE;
End;
--------------------------------------------split the e-mail address or the attachment address-----------------------------------
Copy Code code as follows:
PROCEDURE p_splite_str (p_str VARCHAR2, P_splite_flag INT DEFAULT 1) is
L_addr VARCHAR2 (254): = ';
L_len INT;
L_STR VARCHAR2 (4000);
J INT: = 0; --Represents the number of e-mail addresses or attachments
BEGIN
/* Processing receive mailing address list, including go to space, convert to, etc
L_STR: = TRIM (RTRIM (replace (p_str, '; ', ', ', ', ', ', '), ', '));
L_len: = LENGTH (L_STR);
For I in 1. L_len LOOP
IF SUBSTR (L_str, I, 1) <> ', ' THEN
L_ADDR: = L_addr | | SUBSTR (L_str, I, 1);
ELSE
J: = j + 1;
IF P_splite_flag = 1 THEN
--For processing mailing addresses
--before and after need to add ' <> ', otherwise many mailboxes will not be able to send mail
L_ADDR: = ' < ' | | l_addr | | ' > ';
--Invoke mail send process
My_address_list (J): = L_ADDR;
elsif P_splite_flag = 2 THEN
--Represents the processing attachment name
My_acct_list (J): = L_ADDR;
End IF;
L_ADDR: = ';
End IF;
IF I = L_len THEN
J: = j + 1;
IF P_splite_flag = 1 THEN
--Invoke mail send process
L_ADDR: = ' < ' | | l_addr | | ' > ';
My_address_list (J): = L_ADDR;
elsif P_splite_flag = 2 THEN
My_acct_list (J): = L_ADDR;
End IF;
End IF;
End LOOP;
End;
------------------------------------------------write headers and message content------------------------------------------
Copy Code code as follows:
PROCEDURE Write_data (P_conn in Out Nocopy utl_smtp. CONNECTION,
P_name in VARCHAR2,
P_value in VARCHAR2,
P_splite VARCHAR2 DEFAULT ': ',
P_crlf VARCHAR2 DEFAULT L_crlf) is
BEGIN
/* Utl_raw.cast_to_raw is very important to solve the problem of garbled Chinese * *
Utl_smtp. Write_raw_data (P_conn,
Utl_raw. Cast_to_raw (CONVERT (P_name | | P_splite | |
P_value | | P_crlf,
' ZHS16GBK '));
End;
----------------------------------------write MIME message tail-----------------------------------------------------
Copy Code code as follows:
PROCEDURE End_boundary (CONN in Out Nocopy utl_smtp. CONNECTION,
Last in BOOLEAN DEFAULT FALSE) is
BEGIN
Utl_smtp. Write_data (CONN, utl_tcp. CRLF);
IF (last) THEN
Utl_smtp. Write_data (CONN, last_boundary);
End IF;
End;
----------------------------------------------Send an attachment----------------------------------------------------
Copy Code code as follows:
PROCEDURE attachment (CONN in Out Nocopy utl_smtp. CONNECTION,
Mime_type in VARCHAR2 DEFAULT ' Text/plain ',
INLINE in BOOLEAN DEFAULT TRUE,
FILENAME in VARCHAR2 DEFAULT ' T.txt ',
Transfer_enc in VARCHAR2 DEFAULT ' 7 bit ',
Dt_name in VARCHAR2 DEFAULT ' 0 ') is
L_filename VARCHAR2 (1000);
BEGIN
--Write Attachment head
Utl_smtp. Write_data (CONN, first_boundary);
--Formatting attachments
Write_data (CONN, ' Content-type ', mime_type);
--If the file name is not empty, the attachment is indicated
Drop_directory (Dt_name);
--Create Directory
Create_directory (Dt_name, Get_file (FILENAME, 1));
--Get the name of the attachment file
L_filename: = Get_file (FILENAME, 2);
IF (INLINE) THEN
Write_data (CONN,
' Content-disposition ',
' Inline; Filename= "' | | L_filename | | '"');
ELSE
Write_data (CONN,
' Content-disposition ',
' Attachment; Filename= "' | | L_filename | | '"');
End IF;
--Set the conversion format for the attachment
IF (Transfer_enc is not NULL) THEN
Write_data (CONN, ' content-transfer-encoding ', transfer_enc);
End IF;
Utl_smtp. Write_data (CONN, utl_tcp. CRLF);
--begin attached content
IF Transfer_enc = ' bit 7 ' THEN
--If the attachment is a text type
BEGIN
L_file_handle: = Utl_file. FOPEN (Dt_name, L_filename, ' R '); --Open File
--Divide the attachment into multiple parts so that you can send more than 32K of accessories
LOOP
Utl_file. Get_line (L_file_handle, l_line);
L_MESG: = L_line | | L_crlf;
Write_data (CONN, ', L_MESG, ', ');
End LOOP;
Utl_file. FCLOSE (L_file_handle);
End_boundary (CONN);
EXCEPTION
When others THEN
Utl_file. FCLOSE (L_file_handle);
End_boundary (CONN);
NULL;
End; --Ending processing of text type attachments
elsif transfer_enc = ' base64 ' THEN
--If the binary type is an attachment
BEGIN
--Divide the attachment into multiple parts so that you can send more than 32K of accessories
L_filepos: = 1; --Reset offset and must reset when multiple attachments are sent
L_fil: = Bfilename (Dt_name, l_filename);
L_file_len: = Dbms_lob. GetLength (L_fil);
L_modulo: = MOD (L_file_len, L_amt);
L_pieces: = TRUNC (L_file_len/l_amt);
IF (l_modulo <> 0) THEN
L_pieces: = l_pieces + 1;
End IF;
Dbms_lob. FILEOPEN (L_fil, Dbms_lob. FILE_READONLY);
Dbms_lob. READ (L_fil, L_amt, L_filepos, l_buf);
L_data: = NULL;
For I in 1. L_pieces LOOP
L_filepos: = I * L_amt + 1;
L_file_len: = L_file_len-l_amt;
L_data: = Utl_raw. CONCAT (L_data, l_buf);
L_chunks: = TRUNC (Utl_raw. LENGTH (l_data)/l_max_line_width);
IF (I <> l_pieces) THEN
L_chunks: = l_chunks-1;
End IF;
Utl_smtp. Write_raw_data (CONN, Utl_encode. Base64_encode (L_data));
L_data: = NULL;
IF (L_file_len < L_amt and L_file_len > 0) THEN
L_amt: = L_file_len;
End IF;
Dbms_lob. READ (L_fil, L_amt, L_filepos, l_buf);
End LOOP;
Dbms_lob. FileClose (L_fil);
End_boundary (CONN);
EXCEPTION
When others THEN
Dbms_lob. FileClose (L_fil);
End_boundary (CONN);
RAISE;
End; --end processing of binary attachments
End IF; --End processing attachment content
Drop_directory (Dt_name);
End; --End Process attachment
---------------------------------------------the process of actually sending a message--------------------------------------------
Copy Code code as follows:
PROCEDURE P_email (P_sendoraddress2 VARCHAR2,--Send address
P_receiveraddress2 VARCHAR2)--Accept Address
Is
L_conn UTL_SMTP. CONNECTION; --Defining connections
BEGIN
/* Initialize mail server information, connect to mail server * *
L_conn: = Utl_smtp. Open_connection (P_server, P_port);
Utl_smtp. HELO (L_conn, p_server);
/* SMTP Server login Check/*
IF P_NEED_SMTP = 1 THEN
Utl_smtp.command (L_conn, ' AUTH LOGIN ', ');
Utl_smtp.command (L_conn,
Utl_raw. Cast_to_varchar2 (Utl_encode. Base64_encode (Utl_raw. Cast_to_raw (P_user)));
Utl_smtp.command (L_conn,
Utl_raw. Cast_to_varchar2 (Utl_encode. Base64_encode (Utl_raw. Cast_to_raw (P_pass)));
End IF;
/* Set send address and receive address * *
Utl_smtp. MAIL (L_conn, P_SENDORADDRESS2);
Utl_smtp. RCPT (L_conn, P_RECEIVERADDRESS2);
/* Set the message header * *
Utl_smtp. Open_data (L_conn);
Write_data (L_conn, ' Date ', To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '));
/* Set Send person/*
Write_data (L_conn, ' from ', P_sendor);
/* Set receiver/*.
Write_data (L_conn, ' to ', p_receiver);
/* Set the message Theme * *
Write_data (L_conn, ' Subject ', p_sub);
Write_data (L_conn, ' Content-type ', multipart_mime_type);
Utl_smtp. Write_data (L_conn, utl_tcp. CRLF);
Utl_smtp. Write_data (L_conn, first_boundary);
Write_data (L_conn, ' content-type ', ' text/plain;charset=gb2312 ');
--Blank line alone, otherwise, body content does not appear
Utl_smtp. Write_data (L_conn, utl_tcp. CRLF);
/* Set message body
Restore the Separator to Chr (10). This is primarily to invoke the procedure in the shell, and if there are more than one row, combine the contents of the multiple rows into a single line, separated by L_splite
Then replace Chr (10) with L_crlf. This step is necessary, otherwise you will not be able to send messages with multiple lines in the body of the message
*/
Write_data (L_conn,
'',
Replace (replace (P_txt, L_splite, CHR), CHR (a), L_CRLF),
'',
'');
End_boundary (L_conn);
--Send an attachment if the file name is not empty
IF (P_filename is not NULL) THEN
--Split attachment address by comma or semicolon
P_splite_str (P_filename, 2);
--Loops send attachments (in same message)
For K in 1. My_acct_list. COUNT LOOP
Attachment (CONN => l_conn,
FILENAME => my_acct_list (K),
Transfer_enc => P_encode,
Dt_name => L_directory_base_name | | To_char (K));
End LOOP;
End IF;
/* Close Data Write/*
Utl_smtp. Close_data (L_conn);
/* Close Connection * *
Utl_smtp. QUIT (L_conn);
/* Exception Handling * *
EXCEPTION
When others THEN
NULL;
RAISE;
End;
---------------------------------------------------the main process-----------------------------------------------------
Copy Code code as follows:
BEGIN
L_sendoraddress: = ' < ' | | P_sendor | | ' > ';
P_splite_str (P_receiver); --Processing mail addresses
For K in 1. My_address_list. COUNT LOOP
P_email (L_sendoraddress, My_address_list (K));
End LOOP;
/* Process email address, split message according to Comma * *
EXCEPTION
When others THEN
RAISE;
End;
Procsendemail (' Mail content ', ' mail subject ', ' sender ', ' addressee ', ' Mail server IP address ', 25, 1, ' username ', ' password ', ' ', ' base64 ');
Note that the mail server must use IP, the domain name does not work.
