Enviar correo electrónico en base de datos de IA autónoma en una infraestructura de Exadata dedicada
Puede enviar un correo electrónico en Autonomous AI 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
Enviar correo electrónico con el servicio Email Delivery en Autonomous AI Database
Describe los pasos para enviar un correo electrónico mediante UTL_SMTP
en Autonomous AI 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 26ai PL/SQL Packages and Types Reference para obtener más información.
Consulte Notas sobre la utilización de paquetes PL/SQL de bases de datos para ver más información sobre la utilización de UTL_SMTP
con Autonomous AI 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 base de datos de IA autónoma.
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;
/