Envío de correo electrónico en Autonomous Database on Dedicated Exadata Infrastructure
Puede enviar correos electrónicos en Autonomous Database on Dedicated Exadata Infrastructure mediante el servicio Email Delivery o los procedimientos
DBMS_CLOUD_NOTIFICATION. En este artículo se describe cómo enviar correos electrónicos con estas dos opciones.Temas relacionados
Envío de correo electrónico con el servicio Email Delivery en Autonomous Database
Se describen los pasos para enviar correo electrónico mediante UTL_SMTP en Autonomous Database on Dedicated Exadata Infrastructure.
Para enviar un correo electrónico mediante
UTL_SMTP:
Consulte UTL_SMTP en Oracle Database 19c PL/SQL Packages and Types Reference o Oracle Database 23ai PL/SQL Packages and Types Reference para obtener más referencia.
Consulte Notas sobre el uso de paquetes PL/SQL de base de datos para obtener más información sobre el uso de UTL_SMTP con Autonomous Database.
Código de ejemplo de envío de correo electrónico SMTP
Muestra el código de ejemplo para enviar correo electrónico con UTL_SMTP en una instancia de Autonomous Database.
Código de ejemplo
-- 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 solo