Hi. I'm currently trying to send e-mail via the Mailgun SMTP Server. For doing this, I'm using this procedure in a package :
create or replace package body ko_mailing_pkg
is
procedure mail(p_from varchar2,
p_to varchar2,
p_sub varchar2,
p_body varchar)
is
objConnection utl_smtp.connection;
username varchar2(100):= 'oracle_database@mailgun.koust.net';
password varchar2(1000):= '6699ee8fb62154c30444a7ef626cd220-2b0eef4c-XXXXXXXX';
vrData varchar2(32000);
BEGIN
objConnection := UTL_smtp.open_connection('smtp.mailgun.org',25);
UTL_smtp.helo(objConnection, 'mailgun.koust.net');
utl_smtp.command(objConnection, 'AUTH LOGIN');
utl_smtp.command(objConnection, username);
utl_smtp.command(objConnection, password);
UTL_smtp.mail(objConnection, p_from);
UTL_smtp.rcpt(objConnection, p_to);
UTL_smtp.open_data(objConnection);
UTL_smtp.write_data(objConnection, 'From: '||p_from || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'To: '||p_to || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'Subject: ' || p_sub || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'Content-Type: ' || 'text/html;');
UTL_smtp.write_data(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' ||UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<HTML>');
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<BODY>');
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<FONT COLOR="red" FACE="Courier New">'||p_body||'</FONT>');
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'</BODY>');
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'</HTML>');
UTL_smtp.close_data(objConnection);
UTL_smtp.quit(objConnection);
EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
END mail;
end;
And I execute this with this in the SQL command :
DECLARE
Vdate Varchar2(1000);
BEGIN
Vdate := to_char(sysdate,'dd-mon-yyyy HH:MI:SS AM');
ko_mailing_pkg.mail('devel01@koust.fr', 'contact@godaltristan.fr', 'TESTMAIL','This is a UTL_SMTP-generated email at '|| Vdate);
END;
/
I got an error response (with statement process but, it don't work). Here is the error :
ORA-29279: SMTP permanent error: 500 5.5.1 Invalid command
As you can see, ORA DB can't recognize utl_smtp.command. By the way, I have tested out and it seems to work, do you think the error come from my database ? Thanks and sorry to disturb.
I don't have any line for the error but I think it come from : utl_smtp.command(objConnection, 'AUTH LOGIN');
Does somebody know the problem origin ? Thanks.