utl_smtp - Cómo enviar correos electrónicos usando Oracle 10 g Forms

enviar email oracle

Question

hago un formulario que quiero cuando hago clic en el botón enviar correo electrónico obtengo un código para correo electrónico de internet

 CREATE OR REPLACE FUNCTION FSC.SEND_MAIL
(pIssuer IN VARCHAR2,
pReceiver IN VARCHAR2,
pSender IN VARCHAR2,
pSubject IN VARCHAR2,
pMessage IN VARCHAR2) RETURN VARCHAR2 IS
c utl_smtp.connection;
respuesta utl_smtp.reply;
pServer VARCHAR2(50) := ''192.168.0.6'';
BEGIN
-- Open the connection to the mail server
c := utl_smtp.open_connection(pServer);
respuesta := utl_smtp.helo(c, pServer);
-- Start the Issuer mail.
respuesta := utl_smtp.mail(c, pSender);
-- Starts Receiver
respuesta := utl_smtp.rcpt(c, pReceiver);
respuesta := utl_smtp.open_data(c);
-- Enter the email header
utl_smtp.write_data(c, ''From: '' || pIssuer || utl_tcp.CRLF);
utl_smtp.write_data(c, ''To: '' || pReceiver || utl_tcp.CRLF);
-- Enter the Subject
utl_smtp.write_data(c, ''Subject: '' || pSubject || utl_tcp.CRLF);
-- Write the message text.
utl_smtp.write_data(c, utl_tcp.CRLF || pMessage);
utl_smtp.write_data(c, utl_tcp.CRLF || ''.'');
respuesta := utl_smtp.close_data(c);
-- Close connection
respuesta := utl_smtp.quit(c);
RETURN ''0'';
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(c);
RETURN sqlerrm;
--raise_application_error(-20000,
-- ''The sending of the email has failed by returning the following error: '' || sqlerrm);
WHEN OTHERS THEN
RETURN sqlerrm;
END;
/

Hago esta función en sql it y se ejecutó con éxito, pero cuando ejecuto, no se envía ningún correo electrónico a mi dirección deseada

declare
begin
dbms_output.put_line(SEND_MAIL(''[email protected]'',''[email protected]'',''[email protected]'',''Testing'',''email message''));
end;

uso mi intercambio local para el envío eamil y la dirección IP de ese servidor es 192.168.0.6

el segundo problema en este código cuando hago la misma función en formularios Oracle 10 g, entonces me da este error

utl_tcp.CRLF no puede acceder directamente al paquete remoto

Answer 1

Utilizo este procedimiento general para enviar correos. También admite archivos adjuntos (solo texto sin formato) y los correos no están limitados a 32767 caracteres.

Si no necesita ningún archivo adjunto, no debería ser un problema eliminarlo.

PRIORITY_HIGH CONSTANT INTEGER := 1;
PRIORITY_NORMAL CONSTANT INTEGER := 3;
PRIORITY_LOW CONSTANT INTEGER := 5;
PROCEDURE SendMail(
Subject IN VARCHAR2,
Message IN OUT CLOB,
ToMail IN VARCHAR2,
FromMail IN VARCHAR2, FromName IN VARCHAR2,
Attachment IN OUT CLOB, FileName IN VARCHAR2,
Priority IN INTEGER DEFAULT PRIORITY_NORMAL) IS
MIME_BOUNDARY CONSTANT VARCHAR2(50) := ''====Multipart.Boundary.689464861147414354===='';
MIME_MIXED CONSTANT VARCHAR2(50) := ''multipart/mixed;'';
MIME_TEXT CONSTANT VARCHAR2(50) := ''text/plain;'';
MIME_HTML CONSTANT VARCHAR2(50) := ''text/html;'';
MAIL_HOST CONSTANT VARCHAR2(50) := ''192.168.0.6''; -- try also ''mailhost''
con UTL_SMTP.connection;
ret UTL_SMTP.reply;
Charset VARCHAR2(20);
Footer VARCHAR2(1000);
ClobLen PLS_INTEGER;
amount BINARY_INTEGER := 8192;
buffer VARCHAR2(16384);
offset PLS_INTEGER := 1;
isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), ''<(html)|(body)'', ''i'');
BEGIN
SELECT UTL_I18N.MAP_CHARSET(VALUE)
INTO Charset
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ''NLS_CHARACTERSET'';
-- setup mail header
con := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST, 25);
ret := UTL_SMTP.helo(con, SYS_CONTEXT(''USERENV'', ''DB_DOMAIN'')); -- assuming your database is in the same domain as your mail server
ret := UTL_SMTP.Mail(con, FromMail);
ret := UTL_SMTP.rcpt(con, ToMail);
-- simply call "UTL_SMTP.rcpt(con, ...);" again in order to add further recipient
ret := UTL_SMTP.open_data(con);
IF FromName IS NOT NULL THEN
UTL_SMTP.write_data(con, ''From: "''||FromName||''" <''||FromMail||''>''||Utl_Tcp.CRLF);
ELSE
UTL_SMTP.write_data(con, ''From: <''||FromMail||''>''||Utl_Tcp.CRLF);
END IF;
UTL_SMTP.write_data(con, ''To: <''||ToMail||''>''||Utl_Tcp.CRLF);
-- UTL_SMTP.write_data(con, ''Cc: <''||CcMail||''>''||Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''Subject: ''||Subject||Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''X-Priority: ''||Priority||Utl_Tcp.CRLF);
IF Attachment IS NOT NULL AND FileName IS NOT NULL THEN
UTL_SMTP.write_data(con, ''Mime-Version: 1.0'' || Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''Content-Type: ''||MIME_MIXED||'' boundary="''||MIME_BOUNDARY||''"'' || Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''This is a multipart message in MIME format.'' || Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''--''||MIME_BOUNDARY || Utl_Tcp.CRLF);
END IF;
Footer := ''Message from ''||SYS_CONTEXT(''USERENV'', ''DB_NAME'')||'' sent at ''||TO_CHAR(SYSDATE,''yyyy-mm-dd hh24:mi:ss'');
IF isHTML THEN
UTL_SMTP.write_data(con, ''Content-type: ''||MIME_HTML||'' charset=''||Charset || Utl_Tcp.CRLF);
Message := REPLACE(message, ''</body>'', ''<p>''||Footer||''</p></body>'');
ELSE
UTL_SMTP.write_data(con, ''Content-type: ''||MIME_TEXT||'' charset=''||Charset || Utl_Tcp.CRLF);
END IF;
-- Mail Body
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
ClobLen := DBMS_LOB.GETLENGTH(Message);
LOOP
EXIT WHEN offset > ClobLen;
DBMS_LOB.READ(Message, amount, offset, BUFFER);
UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(BUFFER));
offset := offset + amount;
END LOOP;
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
IF NOT isHTML THEN
UTL_SMTP.write_data(con, Utl_Tcp.CRLF || Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, Footer);
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
END IF;
IF Attachment IS NOT NULL AND FileName IS NOT NULL THEN
-- Mail Attachment
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''--''||MIME_BOUNDARY || Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''Content-Type: ''||MIME_TEXT||'' name="''||Filename||''"''|| Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''Content-Disposition: attachment; filename="''||Filename||''"''|| Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
offset := 1;
ClobLen := DBMS_LOB.GETLENGTH(Attachment);
LOOP
EXIT WHEN offset > ClobLen;
DBMS_LOB.READ(Attachment, amount, offset, BUFFER);
UTL_SMTP.write_raw_data(con, Utl_Raw.cast_to_raw(BUFFER));
offset := offset + amount;
END LOOP;
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''--''||MIME_BOUNDARY||''--'' || Utl_Tcp.CRLF);
END IF;
-- finish mail
ret := UTL_SMTP.close_data(con);
ret := UTL_SMTP.quit(con);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
UTL_SMTP.quit(con);
RAISE;
END SendMail;

Solo una nota, no te pierdas las UTL_SMTP.write_data(con, UTL_TCP.CRLF) . ¡Parecen redundantes, sin embargo, la mayoría de ellos son obligatorios! También mensaje de cortesía como "Este es un mensaje multiparte en formato MIME". es necesario para mostrar correctamente su correo en caso de archivos adjuntos.

Si aún enfrenta problemas, haga un dbms_output.put_line(ret.code||'': ''||ret.text); después de cada llamada a la función en UTL_SMTP.

Aquí el mismo procedimiento pero reducido a sus necesidades:

FUNCTION SendMail(
Subject IN VARCHAR2,
Message IN VARCHAR2,
FromMail IN VARCHAR2, FromName IN VARCHAR2,
ToMail IN VARCHAR2) RETURN VARCHAR2 IS
MIME_TEXT CONSTANT VARCHAR2(50) := ''text/plain;'';
MIME_HTML CONSTANT VARCHAR2(50) := ''text/html;'';
MAIL_HOST CONSTANT VARCHAR2(50) := ''192.168.0.6''; -- try also ''mailhost''
con UTL_SMTP.connection;
ret UTL_SMTP.reply;
Charset VARCHAR2(20);
isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), ''<(html)|(body)'', ''i'');
BEGIN
SELECT UTL_I18N.MAP_CHARSET(VALUE)
INTO Charset
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ''NLS_CHARACTERSET'';
-- setup mail header
con := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST, 25);
ret := UTL_SMTP.helo(con, SYS_CONTEXT(''USERENV'', ''DB_DOMAIN'')); -- assuming your database is in the same domain as your mail server
ret := UTL_SMTP.Mail(con, FromMail);
ret := UTL_SMTP.rcpt(con, ToMail);
ret := UTL_SMTP.open_data(con);
UTL_SMTP.write_data(con, ''From: "''||FromName||''" <''||FromMail||''>''||Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''To: <''||ToMail||''>''||Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''Subject: ''||Subject||Utl_Tcp.CRLF);
UTL_SMTP.write_data(con, ''X-Priority: 3''||Utl_Tcp.CRLF);
IF isHTML THEN
UTL_SMTP.write_data(con, ''Content-type: ''||MIME_HTML||'' charset=''||Charset || Utl_Tcp.CRLF);
ELSE
UTL_SMTP.write_data(con, ''Content-type: ''||MIME_TEXT||'' charset=''||Charset || Utl_Tcp.CRLF);
END IF;
-- Mail Body
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(Message));
UTL_SMTP.write_data(con, Utl_Tcp.CRLF);
-- finish mail
ret := UTL_SMTP.close_data(con);
ret := UTL_SMTP.quit(con);
RETURN ''0'';
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
UTL_SMTP.quit(con);
RETURN SQLERRM;
END SendMail;