?
Solved

Sending email through Oracle

Posted on 2013-05-22
10
Medium Priority
?
1,035 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
[X]
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
  • 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
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
The viewer will learn how to count occurrences of each item in an array.

719 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