以下示例代码使用预定义的程序包UTL_SMTP发送邮件。
该软件包首先在版本8.1.7中可用。
create or replace procedure sendmail(sender varchar2,recipient varchar2,subject varchar2, text
varchar2)
IS
mailhost VARCHAR2(64) := '192.168.1.32';
--The name of the SMTP server host
port constant number(2):=25;
--The port number on which SMTP server is listening (usually 25).
timeout number :=180;
--The time in seconds that the UTL_SMTP package waits before giving up in a read or write
operation in this connection.
--In read operations, this package gives up if no data is available for reading immediately.
--In write operations, this package gives up if the output buffer is full and no data is to be
sent into the network without being blocked.
--Zero (0) indicates not to wait at all.
--NULL indicates to wait forever.
mail_conn utl_smtp.connection;
BEGIN
--dbms_output.put_line(UTL_SMTP.VRFY (mail_conn,recipient));
mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
--Helo performs initial handshaking with SMTP server after connecting
utl_smtp.helo(mail_conn, mailhost);
--Mail Initiates a mail transaction with the server
utl_smtp.mail(mail_conn, sender);
--Specifies the recipient of an e-mail message
utl_smtp.rcpt(mail_conn, recipient);
-- open_data(), write_data(), and close_data() into a single call to data().
--Sends the DATA command
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'From'||':'|| Sender || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'To'||':'|| recipient || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'Subject' ||':'|| subject || UTL_TCP.CRLF);
--Writes a portion of the e-mail message
utl_smtp.write_data(mail_conn, text);
--Closes the data session
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
--dbms_output.put_line('Your message has been sent...!');
EXCEPTION
WHEN UTL_SMTP.PERMANENT_ERROR THENBEGINutl_smtp.quit(mail_conn);END;
RAISE_APPLICATION_ERROR(-20101,'This id has Permanent Error');
WHEN UTL_SMTP.TRANSIENT_ERROR THENBEGINutl_smtp.quit(mail_conn);END;
RAISE_APPLICATION_ERROR(-20102,'SMTP transient error:');
WHEN UTL_SMTP.INVALID_OPERATION THENBEGINutl_smtp.quit(mail_conn);END;
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation in Mail using UTL_SMTP.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20104,'Some other Error ...!');
end;
/
要执行上述过程,请尝试以下代码。
exec sendmail('sender@sender.com','recipient@recipient.com','Hi','Test Mail');
From: https://bytes.com/topic/oracle/insights/771381-sending-mail-using-utl_smtp