Solved

Sending email through Oracle

Posted on 2013-05-22
10
982 Views
Last Modified: 2013-05-23
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
Comment
Question by:uomobello
  • 4
  • 4
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39188855
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
 

Author Comment

by:uomobello
ID: 39189152
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
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 39189413
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39189479
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:uomobello
ID: 39191711
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
 

Author Comment

by:uomobello
ID: 39192158
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39192380
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39192408
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
 

Author Comment

by:uomobello
ID: 39192613
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
This video shows how to recover a database from a user managed backup
The viewer will learn how to dynamically set the form action using jQuery.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now