UTL_MAIL no está instalado de forma predeterminada debido al requisito de configuración SMTP_OUT_SERVER y la exposición de la seguridad que ello implica.
Con la instalación de UTL_MAIL, usted deberá configurar el sistema para agregar la salida a los puertos definidos en SMTP_OUT_SERVER .
sqlplus sys/
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
y dar los permisos al esquema que nos necesite
GRANT EXECUTE ON SYS.UTL_MAIL TO esquema;
GRANT EXECUTE ON SYS.UTL_SMTP TO esquema;
DECLARE
P_SENDER VARCHAR2 (100) := 'email_origen@dominio.local';
P_RECIPIENT VARCHAR2 (100) := 'email_destino@dominio.local';
P_RECIPIENT_CC VARCHAR2 (100) := NULL;
P_SUBJECT VARCHAR2 (200) := 'Prueba';
P_MESSAGE VARCHAR2 (2000) := 'Esto es una prueba';
mailhost VARCHAR2 (30) := 'correo.dominio.local';-------------------o la IP
mail_conn UTL_SMTP.connection;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
mesg VARCHAR2 (4000);
BEGIN
mail_conn := UTL_SMTP.open_connection (mailhost, 25);
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'FROM: '
|| P_SENDER
|| crlf
|| 'Subject: '
|| P_SUBJECT
|| crlf
|| 'To: '
|| P_RECIPIENT
|| crlf
|| 'Cc: '
|| P_RECIPIENT_CC
|| crlf
|| ''
|| crlf
|| P_MESSAGE;
UTL_SMTP.helo (mail_conn, mailhost);
UTL_SMTP.mail (mail_conn, P_SENDER);
UTL_SMTP.rcpt (mail_conn, P_RECIPIENT);
-- utl_smtp.rcpt(mail_conn,P_RECIPIENT_CC);
UTL_SMTP.data (
mail_conn,
'MIME-Version: 1.0'
|| CHR (13)
|| CHR (10)
|| 'Content-type: text/html'
|| CHR (13)
|| CHR (10)
|| mesg);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
UTL_SMTP.quit (mail_conn);
DBMS_OUTPUT.put_line (SQLERRM);
WHEN OTHERS
THEN
UTL_SMTP.quit (mail_conn);
DBMS_OUTPUT.put_line (SQLERRM);
END;
CONSULTAR ACLS
select * from DBA_NETWORK_ACLS;
SELECT *FROM dba_network_acl_privileges;
-------------------------ACLS------SMTP-------------
begin
--dbms_network_acl_admin.drop_acl('SRHCorreo.xml'); -----Si ya existe quite los comentarios para borrarlo.
dbms_network_acl_admin.create_acl (
acl => 'SRHCorreo.xml',
description => 'Para el envio de correos electronicos via SMTP',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
dbms_network_acl_admin.add_privilege (
acl => 'SRHCorreo.xml',
principal => 'USER',---------------> usuario Esquema BD SI utilicen ACL la función-->--> dbms_network_acl_admin.add_privilege();
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
dbms_network_acl_admin.assign_acl (
acl => 'SRHCorreo.xml',
host => 'correo.dominio.local',
lower_port => 25,
upper_port => 25);
end;
COMMIT;
De ser necesario Puedes agregar el resolve sobre la ACL
dbms_network_acl_admin.add_privilege (
acl => 'SRHCorreo.xml',
principal => 'USER',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null);
----------------------ACLS archivos Adjuntos----------------------
BEGIN
--dbms_network_acl_admin.drop_acl('SRHsalhttp.xml'); --Si ya existe. Quite los comentarios para borrarlo.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'SRHsalhttp.xml',
description => 'Para ubicar los adjuntos (http) para el envio de correos electronicos',
principal => 'USER',----------------------------> usuario Esquema BD
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'SRHsalhttp.xml',
principal => 'USER',--> usuario Esquema BD SI utilicen ACL la función-->DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE();
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'SRHsalhttp.xml',
host => '10.0.0.XX/backup');-----------------> directorio de los archivos adjuntos
END;
commit;
Si requiero eliminar privilegios a un usuario
begin
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(acl=> 'SRHCorreo.xml',
principal => 'USER',--> usuario Esquema BD No utilicen ACL la función-->DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE();
is_grant => true,
privilege => 'connect');
end;
select * from DBA_NETWORK_ACLS;
############################################################################################
PROCEDIMIENTOS
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_clob IN CLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
END LOOP;
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
/
##################### UTILIZAR FUNCION ############################
DECLARE
l_clob CLOB := 'This is a very small CLOB!';
BEGIN
send_mail(
p_to => 'me@mycompany.com',
p_from => 'admin@mycompany.com',
p_subject => 'Test Message',
p_text_msg => 'This is a test message.',
p_attach_name => 'test.txt',
p_attach_mime => 'text/plain',
p_attach_clob => l_clob,
p_smtp_host => 'smtp.mycompany.com');
END;
/