How to send an Email through Oracle

In Procedure, p_receiver is the recipient address. Multiple recipients are separated by commas,
P_sub is the mail title, p_txt is the mail content HTML ). The Code is as follows:

 
 

  
  
Createorreplaceprocedurebsd_sendemail (p_receivervarchar2, p_subvarchar2, p_txtvarchar2)
  
  
Is 
  
  
P_uservarchar2 (30): ='';
  
  
P_passvarchar2 (30): ='';
  
  
P_sendorvarchar2 (20): ='Gni';
  
  
P_servervarchar2 (20): ='Mail _ Server_IP';
  
  
P_portnumber: = 25;
  
  
P_need_smtpnumber: = 0;
  
  
P_subjectvarchar2 (4000 );
  
  
Rochelle crlfvarchar2 (2): = utl_tcp.crlf;
  
  
Rochelle sendoraddressvarchar2 (4000 );
  
  
Rochelle splitevarchar2 (10): ='+ +';
  
  
Boundaryconstantvarchar2 (256): ='----- Bysuk';
  
  
First_boundaryconstantvarchar2 (256): ='--'| Boundary | l_crlf;
  
  
Last_boundaryconstantvarchar2 (256): ='--'| Boundary |'--'| L_crlf;
  
  
Multipart_mime_typeconstantvarchar2 (256): ='Multipart/mixed; boundary = "'| Boundary |'"';
  
  
 
  
  
Typeaddress_lististableofvarchar2 (100) indexbybinary_integer;
  
  
My_address_listaddress_list;
  
  
--------------------------------------- Separate email address ---------------------------------------------- 
  
  
Procedurep_splite_str (p_strvarchar2, p_splite_flagintdefault1)Is 
  
  
Rochelle addrvarchar2 (254): ='';
  
  
Rochelle lenint;
  
  
Rochelle strvarchar2 (4000 );
  
  
Jint: = 0;-- Indicates the number of email addresses or attachments 
  
  
Begin 
  
  
/* Process the list of received mail addresses, including removing spaces, converting; and so on */
  
  
Rochelle STR: = trim (rtrim (Replace(Replace(P_str,';',','),'',''),','));
  
  
Rochelle Len: = length (l_str );
  
  
Foriin1.. l_lenloop
  
  
Ifsubstr (l_str, I, 1) <>','Then 
  
  
L_addr: = l_addr | substr (l_str, I, 1 );
  
  
Else 
  
  
J: = j + 1;
  
  
Ifp_splite_flag = 1 then-- Process the email address 
  
  
-- '<>' Must be added before and after the email address; otherwise, many mailboxes cannot send emails. 
  
  
Rochelle ADDR: ='<'| L_addr |'>';
  
  
-- Call the mail sending Process 
  
  
My_address_list (j): = l_addr;
  
  
Endif;
  
  
Rochelle ADDR: ='';
  
  
Endif;
  
  
Ifi = l_lenthen
  
  
J: = j + 1;
  
  
Ifp_splite_flag = 1 then
  
  
-- Call the mail sending Process 
  
  
Rochelle ADDR: ='<'| L_addr |'>';
  
  
My_address_list (j): = l_addr;
  
  
Endif;
  
  
Endif;
  
  
Endloop;
  
  
End;
  
  
------------------------------------------------ Write the mail header and content ------------------------------------------ 
  
  
Procedurewrite_data (p_conninoutnocopyutl_smtp.Connection,
  
  
P_nameinvarchar2,
  
  
P_valueinvarchar2,
  
  
P_splitevarchar2default':',
  
  
P_crlfvarchar2defaultl_crlf)Is 
  
  
Begin 
  
  
/* Utl_raw.cast_to_raw is very important for Solving Chinese garbled characters */
  
  
Utl_smtp.write_raw_data (p_conn, utl_raw.cast_to_raw (Convert(P_name | p_splite | p_value | p_crlf,'Zhs16cgb231280')));
  
  
End;
  
  
---------------------------------------- Write the mime mail tail ----------------------------------------------------- 
  
  
Procedureend_boundary (conninoutnocopyutl_smtp.Connection, Lastinbooleandefaultfalse)Is 
  
  
Begin 
  
  
Utl_smtp.write_data (conn, utl_tcp.crlf );
  
  
If (Last)Then 
  
  
Utl_smtp.write_data (conn, last_boundary );
  
  
Endif;
  
  
End;
  
  
--------------------------------------------- The process of actually sending emails -------------------------------------------- 
  
  
Procedurep_email (p_sendoraddress2varchar2,-- Sending Address 
  
  
P_receiveraddress2varchar2)-- Accept address 
  
  
Is 
  
  
Rochelle connutl_smtp.Connection;-- Define a connection 
  
  
Begin 
  
  
/* Initialize the email server information and connect to the email server */
  
  
Rochelle Conn: = utl_smtp.open_connection (p_server, p_port );
  
  
Utl_smtp.helo (l_conn, p_server );
  
  
/* Smtp server logon check */
  
  
Ifp_need_smtp = 1 then
  
  
Utl_smtp.command (l_conn,'Authlogin','');
  
  
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 ))));
  
  
Endif;
  
  
 
  
  
/* Set the Sending address and Receiving address */
  
  
Utl_smtp.mail (l_conn, p_sendoraddress2 );
  
  
Utl_smtp.rcpt (l_conn, p_receiveraddress2 );
  
  
 
  
  
/* Set the mail header */
  
  
Utl_smtp.open_data (l_conn );
  
  
/* Set the date */
  
  
-- Write_data (l_conn, 'date', to_char (sysdate-1/3, 'ddmonyyhh24: mi: ss ')); 
  
  
/* Set the sender */
  
  
Write_data (l_conn,'From', P_sendor );
  
  
/* Set the recipient */
  
  
Write_data (l_conn,'To', P_receiver );
  
  
/* Set the Email Subject */
  
  
Selectreplace ('=? GB2312? B? '| Utl_raw.cast_to_varchar2 (utl_encode.base64_encode (rawtohex (p_sub) |'? =', Utl_tcp.crlf,'') Intop_subjectfromdual;
  
  
Write_data (l_conn,'Subobject', P_subject );
  
  
 
  
  
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/html; charset = gb2312');
  
  
-- Empty one line. Otherwise, the body content is not displayed. 
  
  
Utl_smtp.write_data (l_conn, utl_tcp.crlf );
  
  
/* Set the mail body
  
  
Returns the separator to chr (10 ). This is mainly used to call this process in shell. If there are multiple rows, merge the contents of multiple rows into one row and separate them with l_splite.
  
  
Then replace chr (10) with l_crlf ). This step is required. Otherwise, you cannot send emails with multiple lines in the mail body.
  
  
*/
  
  
Write_data (l_conn,'',Replace(Replace(P_txt, l_splite, chr (10), chr (10), l_crlf ),'','');
  
  
End_boundary (l_conn );
  
  
 
  
  
/* Close Data Writing */
  
  
Utl_smtp.close_data (l_conn );
  
  
/* Close the connection */
  
  
Utl_smtp.quit (l_conn );
  
  
 
  
  
End;
  
  
--------------------------------------------- Main process ----------------------------------------------------- 
  
  
Begin 
  
  
Rochelle sendoraddress: ='<'| P_sendor |'>';
  
  
P_splite_str (p_receiver );-- Process the email address 
  
  
Forkin1.. my_address_list.countloop
  
  
P_email (l_sendoraddress, my_address_list (k ));
  
  
Endloop;
  
  
/* Process the email address and separate the email by commas */
  
  
End;
 
 

Test the performance of PL/SQL:

Go to Outlook and check if Oracle has sent an Email:

Haha! Receive the data in time. You can send related data at the underlying layer, hoping it will be useful to you!

In addition, I do not know if the 11G function has been used to send emails directly using Oracle ~

1. OOP concepts in Oracle databases
2. Prospective role in Oracle Database Maintenance
3. Use Resource Manager to optimize Oracle Performance
4. Oracle retrieval data consistency and transaction recovery
5. Design Method of super large Oracle database application system