Hello everyone
I have a function for sending emails
create or replace FUNCTION EMAIL_ENVIA_PRUEBAS_SSL1
( Servidor_email IN VARCHAR2, -- Servidor de Correo , p ej : "smtp.gmail.com"
Dominio_Enviador IN VARCHAR2, -- Dominio desde donde se envíará el email, p ej: "cobrotech.com"
Nombre_Email_Envio IN VARCHAR2, -- Nombre a mostrar del enviador , p ej: "Cobrotech - No Responder"
Dir_Email_Envio IN VARCHAR2, -- Dirección de envío del email, p ej: enviosmail@cobrotech.com
Nombre_Email_Destino IN VARCHAR2, -- Nombre a mostrar del destino, p ej: "Cliente"
Dir_Email_Destino IN VARCHAR2, -- Dirección del detino del emaail, p ej : "nombre@cliente.com"
Asunto IN VARCHAR2, -- Asunto del email
Mensaje IN CLOB -- Cuerpo del mensaje, para HTML debe incluir "<html> <head> <body>
) RETURN VARCHAR2 AS
coneccion_email UTL_SMTP.CONNECTION;
resultado utl_smtp.reply;
resultados utl_smtp.replies;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(coneccion_email, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
--
-- Resultado Open_Connection DEBE ser = 220
--
--
-- Open_Connection-1 solo localhost
--
/*
resultado := UTL_SMTP.OPEN_CONNECTION( host => Servidor_email
, c => coneccion_email);
*/
--
-- Open_Connection-2 smtp.gmail.com
--
resultado := UTL_SMTP.OPEN_CONNECTION( host => Servidor_email
,port => 25
,c => coneccion_email
,tx_timeout => 10
,wallet_path => 'file:/home/oracle/ssl/smtp' -- solo debe apuntar al Directorio
,secure_connection_before_smtp => FALSE
);
dbms_output.put_line('OPEN_CONNECTION code = /' || resultado.code || '/ text = /' || resultado.text || '/');
resultado := UTL_SMTP.HELO(coneccion_email, Dominio_Enviador ); -- me identifico con el destino como del dominio "cobrotech.com"
dbms_output.put_line('HELO code = /' || resultado.code || '/ text = /' || resultado.text || '/');
resultados := UTL_SMTP.EHLO(coneccion_email, Dominio_Enviador ); -- me identifico con el destino como del dominio "cobrotech.com"
dbms_output.put_line('EHLO Cantidad = /' || resultados.count || '/');
dbms_output.put_line('EHLO code = /' || resultados(1).code || '/ text = /' || resultados(1).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(2).code || '/ text = /' || resultados(2).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(3).code || '/ text = /' || resultados(3).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(4).code || '/ text = /' || resultados(4).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(5).code || '/ text = /' || resultados(5).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(6).code || '/ text = /' || resultados(6).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(7).code || '/ text = /' || resultados(7).text || '/');
dbms_output.put_line('EHLO code = /' || resultados(8).code || '/ text = /' || resultados(8).text || '/');
resultado := UTL_SMTP.STARTTLS(coneccion_email );
dbms_output.put_line('STARTTLS code = /' || resultado.code || '/ text = /' || resultado.text || '/');
resultado := UTL_SMTP.MAIL(coneccion_email, Dir_Email_Envio ); -- dirección email de enviador "sender"
dbms_output.put_line('MAIL code = /' || resultado.code || '/ text = /' || resultado.text || '/');
resultado := UTL_SMTP.RCPT(coneccion_email, Dir_Email_Destino); -- Direccion email de destino
dbms_output.put_line('RCPT code = /' || resultado.code || '/ text = /' || resultado.text || '/');
resultado := UTL_SMTP.OPEN_DATA(coneccion_email);
dbms_output.put_line('OPEN_DATA code = /' || resultado.code || '/ text = /' || resultado.text || '/');
send_header('From', Nombre_Email_Envio || '<' || Dir_Email_Envio || '>');
send_header('To', Nombre_Email_Destino || '<' || Dir_Email_Destino || '>');
send_header('Subject', Asunto );
UTL_SMTP.WRITE_DATA(coneccion_email, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(coneccion_email, Mensaje || UTL_TCP.crlf || UTL_TCP.crlf);
resultado := UTL_SMTP.CLOSE_DATA(coneccion_email);
dbms_output.put_line('CLOSE_DATA code = /' || resultado.code || '/ text = /' || resultado.text || '/');
UTL_SMTP.QUIT(coneccion_email);
return null;
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(coneccion_email);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call raises an
-- exception that we can ignore.
END;
raise_application_error(-20000,'Falló el envío del email, debido al siguiente error: ' || sqlerrm);
END EMAIL_ENVIA_PRUEBAS_SSL1;
When I run it with Open_Connection-1 and Server_email => 'localhost', it sends the message correctly but it gets spammed.
Connecting to the database cobranza@exact.
OPEN_CONNECTION code = /220/ text = /exact-db.localdomain ESMTP Postfix/
HELO code = /250/ text = /exact-db.localdomain/
EHLO Cantidad = /8/
EHLO code = /250/ text = /exact-db.localdomain/
EHLO code = /250/ text = /PIPELINING/
EHLO code = /250/ text = /SIZE 10240000/
EHLO code = /250/ text = /VRFY/
EHLO code = /250/ text = /ETRN/
EHLO code = /250/ text = /ENHANCEDSTATUSCODES/
EHLO code = /250/ text = /8BITMIME/
EHLO code = /250/ text = /DSN/
STARTTLS code = /502/ text = /5.5.1 Error: command not implemented/
MAIL code = /250/ text = /2.1.0 Ok/
RCPT code = /250/ text = /2.1.5 Ok/
OPEN_DATA code = /354/ text = /End data with <CR><LF>.<CR><LF>/
CLOSE_DATA code = /250/ text = /2.0.0 Ok: queued as CC46811C1A2/
Process exited.
Disconnecting from the database cobranza@exact.
When I run it with Open_Connection-2 and Server_email = 'smtp.gmail.com' it gives the error ORA-29024: Certificate validation failure.
Connecting to the database cobranza@exact.
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_TCP", line 63
ORA-06512: at "SYS.UTL_TCP", line 297
ORA-06512: at "SYS.UTL_SMTP", line 286
ORA-06512: at "COBRANZA.EMAIL_ENVIA_PRUEBAS_SSL1", line 64
ORA-06512: at line 21
OPEN_CONNECTION code = /220/ text = /smtp.gmail.com ESMTP 55sm1394893qtt.12 - gsmtp/
HELO code = /250/ text = /smtp.gmail.com at your service/
EHLO Cantidad = /8/
EHLO code = /250/ text = /smtp.gmail.com at your service, [190.46.84.184]/
EHLO code = /250/ text = /SIZE 35882577/
EHLO code = /250/ text = /8BITMIME/
EHLO code = /250/ text = /STARTTLS/
EHLO code = /250/ text = /ENHANCEDSTATUSCODES/
EHLO code = /250/ text = /PIPELINING/
EHLO code = /250/ text = /CHUNKING/
EHLO code = /250/ text = /SMTPUTF8/
Process exited.
Disconnecting from the database cobranza@exact.
I just need to send some emails but not get spammed and make sure they were successfully sent.
Can someone help me solve this problem please.
Thanks
Carlos