Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Sending email through Oracle

Posted on 2013-05-22
10
1,005 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 74

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 74

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
 

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 74

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 74

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

829 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