Oracle mail storage process:

Oracle mail storage process:

Create or replace procedure bsd_sendemail (
P_receiver varchar2,
P_sub varchar2,
P_txt varchar2)
Is
P_user varchar2 (30): = '';
P_pass varchar2 (30): = '';
P_sendor varchar2 (20): = 'gni ';
P_server varchar2 (20): = 'mail _ server_ip ';
P_port number: = 25;
P_need_smtp number: = 0;
P_subject varchar2 (4000 );
Rochelle CRLF varchar2 (2): = utl_tcp.crlf;
Rochelle sendoraddress varchar2 (4000 );
Rochelle 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 | '"';
Type address_list is table of varchar2 (100) index by binary_integer;
My_address_list address_list;
--------------------------------------- Separate email address ----------------------------------------------
Procedure p_splite_str (p_str varchar2, p_splite_flag int default 1)
Is
Rochelle ADDR varchar2 (254): = '';
Rochelle Len int;
Rochelle STR varchar2 (4000 );
J INT: = 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 (p_str ,';',','),'',''),','));
Rochelle 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 -- indicates the email address to be processed.
-- '<>' 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;
End if;
Rochelle ADDR: = '';
End if;

If I = l_len then
J: = J + 1;

If p_splite_flag = 1 then
-- Call the mail sending Process
Rochelle ADDR: = '<' | l_addr | '> ';
My_address_list (j): = l_addr;
End if;
End if;
End loop;
End;

------------------------------------------------ Write the mail header and 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 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 -----------------------------------------------------
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;

--------------------------------------------- The process of actually sending emails --------------------------------------------
Procedure p_email (p_sendoraddress2 varchar2, -- sending Address
P_receiveraddress2 varchar2) -- accept address
Is
Rochelle conn utl_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 */
If p_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 ))));
End if;

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

Select Replace ('=? Gb2312? B? '| Utl_raw.cast_to_varchar2 (utl_encode.base64_encode (rawtohex (p_sub) | '? = ', Utl_tcp.crlf, '') into p_subject from dual;
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 to restore the separator to CHR (10 ).
This is mainly used to call the process in shell,
If there are multiple rows, merge the contents of the multiple rows into one row,
Use l_splite to separate and 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 (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
For k in 1 .. my_address_list.count Loop
P_email (l_sendoraddress, my_address_list (k ));
End loop;
/* Process the email address and separate the email by commas */
End;