Forum Stats

  • 3,853,446 Users
  • 2,264,223 Discussions
  • 7,905,362 Comments

Discussions

Problem sending emails

Here’s a puzzler for anyone who is interested. I have Oracle XE and Apex 3.1 installed on my PC at work, (the PC is part of a local network which includes a mail server), recently there have been problems sending e-mails from this PC. Currently there is a situation where: if UTL_SMTP is used, emailing works fine but if I try using HTMLDB_MAIL or APEX_MAIL, the following exception is raised:

ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for [email protected]

The Oracle user sending/trying to send the e-mails has execute access on all three of the above-named packages. It’s all very frustrating because APEX_MAIL used to work fine and only stopped working after I had to re-install Oracle and APEX. Results are the same if the procedures are run via SQL*Server or pressing a button on an APEX application. All help gratefully appreciated.

regards, Phil

Source code follows................

CREATE OR REPLACE PROCEDURE PROC_MAIL1 AS

c utl_smtp.connection;
l_from varchar2(50) := '[email protected]';
l_to varchar2(50) := '[email protected]';
l_subject varchar2(50) := 'Fix this email problem 3';
l_body varchar2(50) := 'There have been problems sending emails from this PC;
l_mail_server varchar2(50) := '123.123.123.123';
l_module EXCEPTIONS.module_name%TYPE;
l_section EXCEPTIONS.module_section%TYPE;
l_exception EXCEPTIONS.exception_message%TYPE;

BEGIN

c := utl_smtp.open_connection(l_mail_server, 25); -- SMTP on port 25
utl_smtp.helo(c, l_mail_server);

utl_smtp.mail(c, l_from);

utl_smtp.rcpt(c, l_to);

utl_smtp.data(c,'From: '||l_from || utl_tcp.crlf ||
'To: ' || l_to || utl_tcp.crlf ||
'Subject: ' || l_subject ||
utl_tcp.crlf || l_body);

utl_smtp.quit(c);
DBMS_OUTPUT.PUT_LINE('finished OK');

EXCEPTION
WHEN OTHERS THEN
l_module := 'PROC_SEND_LATEST_POSTINGS';
l_section := 'EMAIL';
l_exception := SQLERRM;
DBMS_OUTPUT.PUT_LINE(l_exception);
INSERT INTO EXCEPTIONS (id, module_name, module_section, exception_message)
VALUES (seq_exceptions.NEXTVAL, l_module, l_section, l_exception);
COMMIT;

END PROC_MAIL1;
/


CREATE OR REPLACE PROCEDURE PROC_MAIL2 AS

l_body CLOB;
l_module EXCEPTIONS.module_name%TYPE;
l_section EXCEPTIONS.module_section%TYPE;
l_exception EXCEPTIONS.exception_message%TYPE;

BEGIN

l_body := 'Problem sending emails from this PC'||utl_tcp.crlf||utl_tcp.crlf;

l_body := l_body ||'See how we can fix this at 16:35'||utl_tcp.crlf||utl_tcp.crlf;

l_body := l_body || '++++ End of message ++++';

HTMLDB_MAIL.send(
p_to => '[email protected]',
p_from => '[email protected]',
p_body => l_body,
p_subj => 'Fix this email problem at 16:35');

HTMLDB_MAIL.PUSH_QUEUE(123.123.123.123', '25');

EXCEPTION
WHEN OTHERS THEN
l_module := 'PROC_SEND_LATEST_POSTINGS';
l_section := 'EMAIL';
l_exception := SQLERRM;
INSERT INTO EXCEPTIONS (id, module_name, module_section, exception_message)
VALUES (seq_exceptions.NEXTVAL, l_module, l_section, l_exception);
COMMIT;
END PROC_MAIL2;
/
Tagged:
This discussion has been closed.