在 Dedicated Exadata Infrastructure 上的 Autonomous Database 傳送電子郵件
您可以使用電子郵件傳遞服務或
DBMS_CLOUD_NOTIFICATION 程序，在 Autonomous Database on Dedicated Exadata Infrastructure 上傳送電子郵件。本文將說明如何使用這兩個選項傳送電子郵件。在 Autonomous Database 上透過電子郵件傳遞服務傳送電子郵件
描述在 Autonomous Database on Dedicated Exadata Infrastructure 上使用 UTL_SMTP 傳送電子郵件的步驟。
使用
UTL_SMTP 傳送電子郵件：
請參閱 Oracle Database 19c PL/SQL Packages and Types Reference 中的 UTL_SMTP 或 Oracle Database 23ai PL/SQL Packages and Types Reference ，瞭解進一步參照。
請參閱使用資料庫 PL/SQL 套裝程式注意事項，瞭解有關將 UTL_SMTP 與 Autonomous Database 搭配使用的詳細資訊。
SMTP 傳送電子郵件範例代碼
顯示在 Autonomous Database 上以 UTL_SMTP 傳送電子郵件的範例程式碼。
範例程式碼
-- As ADMIN user, set up ACE to access SMTP host
connect admin/[password]@[ADBD-host]/[pdbname]_tp.atp.oraclecloud.com
begin
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
host => '[SMTP_Server_address]',
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(
privilege_list => xs$name_list('smtp'),
principal_name => '[database_user]',
principal_type => xs_acl.ptype_db));
end;
/
-- If using secure SMTP, set up ACE to wallet for certificate-based secure (TLS) email
declare
m_wallet_path VARCHAR2(512);
begin
select property_value into m_wallet_path
from database_properties
where property_name like '%SSL%';
m_wallet_path := 'file:' || m_wallet_path;
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
wallet_path => m_wallet_path,
ace => xs$ace_type(
privilege_list => xs$name_list('use_client_certificates'),
principal_name => '[database_user]',
principal_type => xs_acl.ptype_db));
end;
/
-- As ADMIN or regular database user with privileges set up above, use UTL_SMTP to send email
connect [database_user]/[password]@[ADBD-host]/[pdbname]_tp.atp.oraclecloud.com
-- Example of normal (non-secure) email
DECLARE
c UTL_SMTP.CONNECTION;
reply UTL_SMTP.REPLY;
host varchar2(64) := '[SMTP_Server_address]',
port pls_integer := 25;
sender varchar2(64) := '[sender-address@domain.com]';
recipient varchar2(64) := '[recipient-address@domain.com]';
BEGIN
c := UTL_SMTP.OPEN_CONNECTION (host, port);
if (reply.CODE != 220) then
raise_application_error(-20000,'UTL_SMTP.CONNECTION: '
|| reply.CODE || ' - ' || reply.TEXT);
end if;
UTL_SMTP.HELO(c, host);
UTL_SMTP.MAIL(c, sender);
UTL_SMTP.RCPT(c, recipient);
UTL_SMTP.OPEN_DATA(c);
UTL_SMTP.WRITE_DATA(c,'From:[Sender] <' || sender || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c,'To:[Recipient] <' || recipient || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c,'Subject: Email from UTL_SMTP App' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c,'Mail Content');
UTL_SMTP.CLOSE_DATA(c);
dbms_output.put_line('Close Data: ' || reply.code);
UTL_SMTP.QUIT(c);
END;
/
-- Example of secure email using OCI Mail Server as the SMTP server
DECLARE
m_wallet_path VARCHAR2(256);
c UTL_SMTP.CONNECTION;
reply UTL_SMTP.REPLY;
host varchar2(64) := 'smtp.email.[region].oci.oraclecloud.com';
port pls_integer := 25;
sender varchar2(64) := '[sender-address@domain.com]';
recipient varchar2(64) := '[recipient-address@domain.com]';
BEGIN
select property_value into m_wallet_path
from database_properties
where property_name like '%SSL%';
m_wallet_path := 'file:' || m_wallet_path;
-- secure connection with SSL/TLS made *before* SMTP communication
-- use hostname matched against CN or SMTP server's certificate
reply := UTL_SMTP.OPEN_CONNECTION (
host => host,
port => port,
c => c,
wallet_path => m_wallet_path,
wallet_password => NULL,
secure_connection_before_smtp => FALSE,
secure_host => NULL);
if (reply.CODE != 220) then
raise_application_error(-20000,'UTL_SMTP.OPEN_CONNECTION: '
|| reply.CODE ||' - ' || reply.TEXT);
end if;
UTL_SMTP.STARTTLS(c);
-- username is the 'SMTP credential' for the user's email address
-- obtained from OCI Identity -> Users -> email address.
-- Scheme refers to SASL (Simple Authentication and Security Layer)
-- Authentication Schemes for SMTP protocol - PLAIN, CRAM-MD5, LOGIN
-- https://docs.oracle.com/en-us/iaas/Content/Email/Concepts/overview.htm
UTL_SMTP.AUTH(
c => c,
username => '[ocid1.user.verylongaddresscreatedinOCIforuseraddress.com]',
password => '[password-provided-from-create]',
schemes => 'PLAIN');
UTL_SMTP.HELO(c, host);
UTL_SMTP.MAIL(c, sender);
UTL_SMTP.RCPT(c, recipient);
UTL_SMTP.OPEN_DATA(c);
UTL_SMTP.WRITE_DATA(c, 'From: Sender <' || sender || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'To: Recipient <' || recipient || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Subject: Email from UTL_SMTP App' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Mail Content');
UTL_SMTP.WRITE_DATA(c, 'Sender is a domain-owned approved sender in OCI Mail Service');
UTL_SMTP.CLOSE_DATA(c);
dbms_output.put_line('Close Data: ' || reply.code);
UTL_SMTP.QUIT(c);
END;
/
僅限 Oracle Public Cloud