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 phil.robinson@royalfree.nhs.uk
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) := 'yellow@huts.co.uk';
l_to varchar2(50) := 'phil.robinson@royalfree.nhs.uk';
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 => 'phil.robinson@royalfree.nhs.uk',
p_from => 'yellow@huts.co.uk',
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;
/