E-MAIL

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;

E-MAIL

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;

/