oracle10g Send Email Sample _oracle

In procedure P_receiver is the recipient address, multiple recipients are separated by "," (English comma),
P_sub is the message header, P_txt is the message content (HTML is supported). The code is as follows:

Copy Code code as follows:

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: = ' Gnie ';
P_server varchar2: = ' mail_server_ip ';
P_port number: = 25;
P_NEED_SMTP number: = 0;
P_subject varchar2 (4000);
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 | | '"';

Type address_list is Table of VARCHAR2 (m) index by Binary_integer;
My_address_list address_list;
---------------------------------------Split mail 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;
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;
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, ' zhs16cgb231280 '));
End
----------------------------------------write MIME message 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 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 headers */
    utl_smtp.open_data (l_conn);
   /* Set Date */
   --write_data (l_conn, ' Date ', To_char (SYSDATE-1/3, ' dd Mon yy Hh24:mi : SS '));
    * Set Sender */
    write_data (L_conn, ' from ', P_sendor);
   /* Set receiver */
    write_data (l_conn, ' to ', p_receiver);
   /* Set mail 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, ' Subject ', 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 ');
--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 row, combine the contents of the multiple rows into a single line, separated by L_splite
Then replace Chr (10) with L_crlf. 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);

/* Close Data Write/*
Utl_smtp.close_data (L_conn);
/* Close Connection * *
Utl_smtp.quit (L_conn);

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

When you send an email, you can go to your mailbox and see if you've received it.