Emails sent through Oracle)

Linux as3 + Oracle 9.2 | 10.20.1 successfully sent an email from Oracle...

Test environment: Linux as3, Oracle 9.0.2.4
SQL> select * from V $ version;

Banner
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
PL/SQL release 9.2.0.4.0-Production
Core 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0-Production
Nlsrtl version 9.2.0.4.0-Production

SQL> select * from V $ version;
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL release 10.2.0.1.0-Production
Core 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-Production
Nlsrtl version 10.2.0.1.0-Production

The mail server is win2003 and winmail.

1. Ensure that the network from the Oracle server to the mail server is smooth and port 25 is enabled

2. Create a procedure for sending the mail as follows:
---------------------------------------
Create or replace procedure send_mail
(As_sender in varchar2, -- email sender
As_recp in varchar2, -- email recipient
As_subject in varchar2, -- mail title
As_msg_body in varchar2) -- mail content
Is
Ls_mailhost varchar2 (30): = 'mail Server'; -- Address or IP
Lc_mail_conn utl_smtp.connection;
Ls_subject varchar2 (100 );
Ls_msg_body varchar2 (20000 );
Ls_username varchar2 (256): = 'usercode ';
Ls_password varchar2 (256): = 'Password ';
Begin
Lc_mail_conn: = utl_smtp.open_connection (ls_mailhost, 25 );
Utl_smtp.helo (lc_mail_conn, ls_mailhost );
Utl_smtp.command (lc_mail_conn, 'auth login ');
Utl_smtp.command (lc_mail_conn, demo_base64.encode (utl_raw.cast_to_raw (ls_username )));
Utl_smtp.command (lc_mail_conn, demo_base64.encode (utl_raw.cast_to_raw (ls_password )));
Ls_subject: = 'subject: ['| Upper (sys_context ('userenv', 'db _ name') |']-'| as_subject;
Ls_msg_body: = as_msg_body;
Utl_smtp.mail (lc_mail_conn, '<' | as_sender | '>'); -- the '<' here must be written; otherwise, a permanent error occurs.
Utl_smtp.rcpt (lc_mail_conn, '<' | as_recp | '>'); -- the '<' here must be written; otherwise, a permanent error occurs.
Utl_smtp.open_data (lc_mail_conn );
Ls_msg_body: = 'from: '| as_sender | CHR (13) | CHR (10) | 'to:' | as_recp | CHR (13) | CHR (10) | ls_subject |

CHR (13) | CHR (10) | CHR (13) | CHR (10) | ls_msg_body;
Utl_smtp.write_raw_data (lc_mail_conn, utl_raw.cast_to_raw (ls_msg_body); -- in this way, subject writing supports Chinese, but the body content does not support Chinese;
-- Utl_smtp.write_data (lc_mail_conn, ls_msg_body); -- writing subject does not support Chinese
Utl_smtp.close_data (lc_mail_conn );
Utl_smtp.quit (lc_mail_conn );
Exception
When utl_smtp.invalid_operation then
Dbms_output.put_line ('invalid operation ');
When utl_smtp.transient_error then
Dbms_output.put_line ('transient error ');
When utl_smtp.permanent_error then
Dbms_output.put_line ('permanent error ');
When others then
Dbms_output.put_line ('others ');
End send_mail;

3. Run the following email:
Exec send_mail ('heyu @ 163.net', 'admin @ 163.net', 'my', 'This is a Oracle test mail ');

NOTE: If demo_base64.encode must be declared appears during compilation, create the following package and package body;
---------------------------------
Create or replace package demo_base64 is

-- Base64-encode a piece of binary data.
--
-- Note that this encode function does not split the encoded text
-- Multiple lines with no more than 76 bytes each as required
-- The MIME standard.
--
Function encode (R in raw) return varchar2;

End;
------------------------------------
Create or replace package body demo_base64 is

Type vc2_table is table of varchar2 (1) index by binary_integer;
Map vc2_table;

-- Initialize the base64 Mapping
Procedure init_map is
Begin
Map (0): = 'a'; map (1): = 'B'; Map (2): = 'C'; Map (3): = 'D '; map (4): = 'E ';
Map (5): = 'F'; Map (6): = 'G'; Map (7): = 'H'; Map (8): = 'I '; map (9): = 'J ';
Map (10): = 'K'; Map (11): = 'l'; Map (12): = 'M'; Map (13): = 'n '; map (14): = 'O ';
Map (15): = 'P'; Map (16): = 'q'; Map (17): = 'R'; Map (18): ='s '; map (19): = 'T ';
Map (20): = 'U'; Map (21): = 'V'; Map (22): = 'W'; Map (23): = 'X '; map (24): = 'y ';
Map (25): = 'Z'; Map (26): = 'a'; Map (27): = 'B'; Map (28): = 'C '; map (29): = 'D ';
Map (30): = 'E'; Map (31): = 'F'; Map (32): = 'G'; Map (33): = 'H '; map (34): = 'I ';
Map (35): = 'J'; Map (36): = 'K'; Map (37): = 'l'; Map (38): = 'M '; map (39): = 'n ';
Map (40): = 'O'; Map (41): = 'P'; Map (42): = 'q'; Map (43): = 'R '; map (44): ='s ';
Map (45): = 'T'; Map (46): = 'U'; Map (47): = 'V'; Map (48): = 'W '; map (49): = 'X ';
Map (50): = 'y'; Map (51): = 'Z'; Map (52): = '0'; Map (53): = '1 '; map (54): = '2 ';
Map (55): = '3'; Map (56): = '4'; Map (57): = '5'; Map (58): = '6 '; map (59): = '7 ';
Map (60): = '8'; Map (61): = '9'; Map (62): = '+'; Map (63): = '/';
End;

Function encode (R in raw) return varchar2 is
I pls_integer;
X pls_integer;
Y pls_integer;
V varchar2 (32767 );
Begin

-- For every 3 bytes, split them into 4 6-bit units and map them
-- The base64 characters
I: = 1;
While (I + 2 <= utl_raw.length (R) loop
X: = to_number (utl_raw.substr (R, I, 1), '0x ') * 65536 +
To_number (utl_raw.substr (R, I + 1, 1), '0x ') * 256 +
To_number (utl_raw.substr (R, I + 2, 1), '0x ');
Y: = floor (x/262144); V: = v | map (y); X: = x-y * 262144;
Y: = floor (x/4096); V: = v | map (y); X: = x-y * 4096;
Y: = floor (x/64); V: = v | map (y); X: = x-y * 64;
V: = v | map (X );
I: = I + 3;
End loop;

-- Process the remaining bytes that has fewer than 3 bytes.
If (utl_raw.length (R)-I = 0) then
X: = to_number (utl_raw.substr (R, I, 1), '0x ');
Y: = floor (X/4); V: = v | map (y); X: = x-y * 4;
X: = x * 16; V: = v | map (X );
V: = v | '= ';
Elsif (utl_raw.length (R)-I = 1) then
X: = to_number (utl_raw.substr (R, I, 1), '0x ') * 256 +
To_number (utl_raw.substr (R, I + 1, 1), '0x ');
Y: = floor (x/1024); V: = v | map (y); X: = x-y * 1024;
Y: = floor (x/16); V: = v | map (y); X: = x-y * 16;
X: = x * 4; V: = v | map (X );
V: = v | '= ';
End if;

Return V;

End;

Begin
Init_map;
End;

-- End.