Oracle Send mail Implementation method _oracle

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


/*


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--------------------------------------


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------------------------------------


PROCEDURE drop_directory (P_directory_name VARCHAR2) is


BEGIN


EXECUTE IMMEDIATE ' Drop directory ' | | P_directory_name;


EXCEPTION


When others THEN


NULL;


End;


--------------------------------------------------Create directory-----------------------------------------


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-----------------------------------


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--Represents processing mail address


--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--Representing 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------------------------------------------


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-----------------------------------------------------


PROCEDUREEnd_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----------------------------------------------------


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, 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--------------------------------------------


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 line, the contents of the multiple rows are merged into a single line.

Separated by L_splite and replaced with L_crlf Chr (10). 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 (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

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-----------------------------------------------------


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;

 
  usages: 
    
   
 
     
    
 
     
    
  code:  
 
            
  
 
 SQL   set serverout on 
 
 
 SQL  >  exec procsendemail  ( ' Chinese test mail ' ,  ' Chinese theme ' ,  ' space6212@163.com ' ,  ' space6212@163.com                
   
 
     
    
    , susk@souchang.com ' ,  ' 202.108.5.85 ' ,  ,  1 ,  ' xxxx ' ,  ' XXXX ' ,  '/tmp/a.jpg,/tmp/b.jpg ' ,  ' base64 ' ; 
 
 
  PL / SQL procedure successfully completed