• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

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?
0
uomobello
Asked:
uomobello
  • 4
  • 4
1 Solution
 
sdstuberCommented:
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

0
 
uomobelloAuthor Commented:
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?
0
 
mohammadzahidCommented:
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;
    /
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
v_reply      := UTL_SMTP.ehlo(l_mail_conn, p_smtp_host);


sorry, that's my mistake I just cut and pasted the v_reply on all of the lines

you need the "replies" type not "reply" type for that command


DECLARE
    v_reply   UTL_SMTP.reply;
    v_replies UTL_SMTP.replies;
BEGIN
    l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
    v_replies := UTL_SMTP.ehlo(l_mail_conn, p_smtp_host);

    FOR i IN v_replies.first .. v_replies.last
    LOOP
        DBMS_OUTPUT.put_line(v_replies(i).code || ' - ' || v_replies(i).text);
    END LOOP;

    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;
0
 
uomobelloAuthor Commented:
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?
0
 
uomobelloAuthor Commented:
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??
0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
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.
0
 
uomobelloAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now