ORACLE emails, oracle Stored Procedure emails

ORACLE emails, oracle Stored Procedure emails

-- ===================================================== ==========================================================
-- Main mail sending process ---------------------------------------------------------------
-- Xuyinghuai 20150603 ------------------------------------------------------------
Procedure send_mail _ (p_From Varchar2, -- mail recipient
P_Fromuser Varchar2, -- Sender nickname
P_Touser Varchar2, -- recipient nickname
P_To Varchar2, -- email recipient
P_Cc Varchar2, -- email CC
P_Subject Varchar2, -- mail title
P_Message Varchar2, -- mail content
P_User Varchar2, -- email verification user
P_Mailhost VARCHAR2, -- mail service address
P_Psd Varchar2 -- email verification Password
) IS
V_Conn Utl_Smtp.Connection; -- connection to the email 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); -- use the ehlo () instead of the helo () function; otherwise, the following error is reported: ORA-29279: SMTP permanent error: 503 5.5.2 Send hello first.
Utl_Smtp.Command (v_Conn, 'auth login'); -- smtp server logon Verification
Utl_Smtp.Command (v_Conn, Utl_Raw.Cast_To_Varchar2 (Utl_Encode.Base64_Encode (Utl_Raw.Cast_To_Raw (p_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 the sender
-- Set the 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 the recipient
V_Touser1: = v_Touser1 | p_Touser | '<' | p_To | '> ';
End if;
-- Set CC ----------------
I: = 1;
J: = 1;
IF nvl (p_Cc, 'A') <> 'A' THEN
Psrc: = Rtrim (Ltrim (p_Cc, ';'), ';') | ';';
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); -- sets the CC user.
End if;
Exit When I = 0;
End loop;
ELSE
IF nvl (p_Cc, '1') <> '1' THEN
Utl_Smtp.Rcpt (v_Conn, p_Cc); -- sets the CC user.
End if;
End if;
End if;
-- Create the mail content to be sent. Note that a blank line is required between the header information and the mail 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 -- header information
| P_Message; -- this is the body of the email.
Utl_Smtp.Open_Data (v_Conn); -- open the stream
Utl_Smtp.Write_Raw_Data (v_Conn, Utl_Raw.Cast_To_Raw (Convert ('content-Type: text/html; charset = UTF-8 '|
V_Msg, 'zhs16gbk'); -- in this way, both the title and content can be written in Chinese.
Utl_Smtp.Close_Data (v_Conn); -- close the stream
Utl_Smtp.Quit (v_Conn); -- close the connection
Exception
When Others THEN
Str_error: = Dbms_Utility.Format_Error_Stack | Dbms_Utility.Format_Call_Stack;

Dbms_Output.Put_Line (str_error );
END;