asked on

Sending email through Oracle

We have a website that uses Oracle 8i (don't ask why) and I'm trying to automate the sending of emails to web customers who've forgotten their access codes.

As best as I can tell, my connection to the mail server (Network Solutions) is good and I'm being authenticated, but the email is not getting through.

I'm encoding all data sent to mail server to base-64

I checked the email address I'm passing to the procedure (tested multiple addresses) and they're correct. Nothing is being caught in junk/spam folders.

My code:

l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.ehlo(l_mail_conn, p_smtp_host);
Utl_Smtp.command(l_mail_conn,'AUTH', 'LOGIN');
utl_smtp.command(l_mail_conn,'encoded-username');
utl_smtp.command(l_mail_conn,'encoded-password');
UTL_SMTP.mail(l_mail_conn, encoded-from);
UTL_SMTP.rcpt(l_mail_conn, encoded-to);
UTL_SMTP.data(l_mail_conn, encoded-message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);

I've tried sending the "from," "to," and "message" as encoded and un-encoded without success. Any ideas what could be wrong?

Sean Stuber

you don't need (or want) to encode the from, to or message portions, only the username/password for the AUTH LOGIN responses.

what are the errors and/or response messages from each line?

use the function version of the utl_smtp commands instead of the procedures and check each of the smtp responses

if you get an smtp response in the 4xx or 5xx range then the procedures will raise an exceptions and you'll see the response. if you get something in the 1xx, 2xx or 3xx range then the responses are dropped by the procedures

using the function versions is almost identical to the procedural versions except you can do something with all of the replies.

DECLARE
    v_reply   UTL_SMTP.reply;
BEGIN
    l_mail_conn  := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
    v_reply      := UTL_SMTP.ehlo(l_mail_conn, p_smtp_host);
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.command(l_mail_conn, 'AUTH', 'LOGIN');
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.command(l_mail_conn, 'encoded-username');
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.command(l_mail_conn, 'encoded-password');
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.mail(l_mail_conn, 'from');
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.rcpt(l_mail_conn, 'to');
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.data(l_mail_conn, 'MESSAGE' || UTL_TCP.crlf || UTL_TCP.crlf);
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
    v_reply      := UTL_SMTP.quit(l_mail_conn);
    DBMS_OUTPUT.put_line(v_reply.code || ' - ' || v_reply.text);
END;

Open in new window

uomobello

ASKER

I'm getting the error:

v_reply := UTL_SMTP.ehlo(l_mail_conn, 'smtp.mydomain.com');
*
ERROR at line 23:
ORA-06550: line 23, column 21:
PLS-00382: expression is of wrong type

Seems to me Oracle is not recognizing utl_smtp as a function. Forgive me if I'm being thick here but I'm basically a web developer who has had to teach myself the ins and outs of working with an Oracle DB.

Am I forgetting to do something first before I can use utl_smtp as a function?

mohammadzahid

Try this sample code:

create or replace
PROCEDURE send_mail (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := 'aria.us.oracle.com';
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn, p_message);
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;
/

SQL> begin
send_mail( 'hello@oracle.com',
'myname@oracle.com',
'Hello World!' );
end;
/

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

uomobello

ASKER

Thanks, the new code worked fine.

Here's the output:

250 - mailpod1.hostingplatform.com
250 - STARTTLS
250 - PIPELINING
250 - 8BITMIME
250 - SIZE 65000000
250 - AUTH LOGIN PLAIN CRAM-MD5
334 - xxxxxxxxxxxxxxx
334 - xxxxxxxxxxxxxxx
235 - ok, go ahead (#2.0.0)
250 - ok
250 - ok
250 - ok 1369324451 qp 15430
221 - mailpod1.hostingplatform.com

From what I've read on the web this looks perfect and the email should have been sent on its merry way....but still no email received.

I've tried sending to my work email and to my home....same response codes, no email. I've tried sending from my office computer and from our web server....same response codes, no email.

I've checked the email account from which the emails should be sent. No replies that any sent emails were undeliverable.

Any ideas?

uomobello

ASKER

Update to all of this.

I just tried sending this email using my own personal email account through my local ISP here in NY
......AND IT WORKED!

Seems to be an issue with the Network Solutions email account I was using earlier. Maybe they're system is blocking the outgoing mail somehow??

Sean Stuber

it is definitely looks like a problem on the smtp server.

the 250, 334 and 235 responses all indicate success
221 indicates closing of the connection.

so, you connected, everything you tried worked and then you exited.
couldn't ask for better responses.

if it still failed, it's the server, not your code

and, if you've validated against another server that just doubles up the evidence

Sean Stuber

just noticed this

250 - STARTTLS

if your server is expecting you to authenticate over TLS, you can't do that in your version.
You'll need version 11.2.0.2 or higher to get support for that functionality.

If that is the case, the SMTP protocol does not require error messages, it can simply accept and ignore your commands. However it is expected (but not required) for a 530 code to be returned if STARTTLS is needed and you don't use it.

uomobello

ASKER

Ahhh, that makes a lot of sense. When I ran it through my personal email account there wasn't any STARTTLS response.

I'll bet that's what's happening.

Thanks for all your help. I learned a lot. We'll have to figure out a way to use another email account to send out these automated emails.