Instance code for sending messages using an Oracle database _oracle

-the main process for sending a message is as follows:

Procedure Send_mail_ (P_from Varchar2,--mail Sender P_fromuser Varchar2,--Sender nickname P_touser Varchar2,--recipient nickname P_to Varchar2,--Postal Recipient P_CC VARCHAR2,--CC p_subject VARCHAR2--Message header P_message Varchar2,--mail content P_user Varchar2,--Message authentication user P_mailhost VAR CHAR2,--mail service address P_PSD Varchar2--mail authentication password) is V_conn utl_smtp.connection; --The connection to the mail server v_msg Varchar2 (32700);
--Mail content psrc Varchar2 (4000);
PSRC1 Varchar2 (4000);
V_touser1 VARCHAR2 (4000);
V_touser VARCHAR2 (500);
V_to VARCHAR2 (500);
I number: = 1;
J Number: = 1;
M Number: = 1;
N Number: = 1;
Str_error VARCHAR2 (20000);
BEGIN v_touser1: = ';
V_conn: = Utl_smtp.open_connection (P_mailhost, 25); Utl_smtp.ehlo (V_conn, p_mailhost);
-EHLO () instead of the helo () function will report: ORA-29279:SMTP permanent error: 503 5.5.2 Send Hello. Utl_smtp.command (V_conn, ' AUTH LOGIN '); --SMTP Server logon checksum Utl_smtp.command (V_conn, Utl_raw.cast_to_varchar2 (Utl_raw.cast_to_raw
User))); Utl_smtp.command (V_conn, Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.casT_to_raw (P_PSD))); Utl_smtp.mail (V_conn, ' < ' | | p_from | | ' > '); --Set sender--Set recipient----------------PSRC: = Rtrim (Ltrim (p_to, '; '), '; ') | |
';'; PSRC1: = Rtrim (Ltrim (P_touser, '; '), '; ') | |
';';
IF NVL (Instr (psrc, '; ', j), 0) >0 THEN LOOP I: = NVL (Instr (psrc, '; ', j), 0);
M: = NVL (Instr (Psrc1, '; ', n), 0);
If i > 0 THEN v_to: = Trim (Substr (Psrc, J, I-j));
V_touser: = NVL (Trim (Substr (PSRC1, N, m-n)), ' 1 '); UTL_SMTP.RCPT (V_conn, v_to);
--Set the recipient IF V_touser = ' 1 ' THEN v_touser: = Trim (SUBSTR (V_to,1,instr (v_to, ' @ ')-1));
End IF; IF j = 1 THEN v_touser1: = V_touser1 | | V_touser | | ' < ' | | v_to | |
' > '; ELSE v_touser1: = V_touser1 | | ';'|| V_touser | | ' < ' | | v_to | |
' > ';
End IF;
J: = i + 1;
N: = m + 1;
End IF;
Exit when i = 0;
End LOOP; ELSE utl_smtp.rcpt (V_conn, p_to); --Set recipient V_touser1: = V_touser1 | | P_touser | | ' < ' | | p_to | |
' > ';
End IF;
--Set CC person----------------i: = 1;
J: = 1; IF NVL (p_cc, ' AA ') <> ' AA ' THEN psrc: = Rtrim (Ltrim, '; '), '; ') | |
';';IF NVL (Instr (psrc, '; ', j), 0) >0 THEN LOOP I: = NVL (Instr (psrc, '; ', j), 0);
If i > 0 THEN v_to: = Trim (Substr (Psrc, J, I-j));
J: = i + 1; UTL_SMTP.RCPT (V_conn, v_to);
--Set CC person end IF;
Exit when i = 0;
End LOOP; ELSE IF NVL (p_cc, ' 1 ') <> ' 1 ' THEN utl_smtp.rcpt (V_conn, P_CC);
--Set CC person end IF;
End IF;
End IF; --Create message content to send note the header message is blank with a line between the message body v_msg: = ' Date: ' | | To_char (sysdate, ' dd Mon yy hh24:mi:ss ') | |
Utl_tcp.crlf | | ' From: ' | | P_fromuser | | ' < ' | | P_from | | ' > ' | |
Utl_tcp.crlf | | ' To: ' | | V_touser1 | |
Utl_tcp.crlf | | ' Cc: ' | | P_CC | |
Utl_tcp.crlf | | ' Subject: ' | | P_subject | |
Utl_tcp.crlf | | UTL_TCP.CRLF--This is preceded by header information | | P_message; --This is the message body utl_smtp.open_data (v_conn); --Open stream Utl_smtp.write_raw_data (V_conn,utl_raw.cast_to_raw (' Content-type:text/html;charset=utf-8 ' | | | V_MSG, ' ZHS16GBK ')); Write the title and content in Chinese utl_smtp.close_data (v_conn); --Close the flow utl_smtp.quit (V_conn); --Close Connection Exception when others THEN str_error: = Dbms_utility.format_error_stack | | Dbms_utility.forMat_call_stack;
Dbms_output.put_line (Str_error); End;

The above is a small series to bring you the use of Oracle database to send mail instance code, I hope to help you, if you have any questions welcome to my message, small series will promptly reply to everyone, here also thank you for your support cloud Habitat community site!