Solved

Oracle Email

Posted on 2013-05-21
14
981 Views
Last Modified: 2013-05-22
I'm using Oracle 8i and trying to send an automated email using Oracle's UTL_SMTP utility. I'm attempting to connect to and send authentication to a Network Solutions email server.

Code:
      l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
      UTL_SMTP.helo(l_mail_conn, p_smtp_host);
      Utl_Smtp.command(l_mail_conn,'auth login');
      utl_smtp.command(l_mail_conn,'myinbox@mydomain.com');
      utl_smtp.command(l_mail_conn,'password');
      UTL_SMTP.mail(l_mail_conn, p_from);
      UTL_SMTP.rcpt(l_mail_conn, p_to);
      UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
      UTL_SMTP.quit(l_mail_conn);

I've checked with Network Solutions and my hostname and port are correct. The username and password I'm sending are also correct and they claim sending them as a simple character string is also okay.

I seem to be connecting to the email server properly but I get an error message:

ORA-20002: 535 authentication failed (#5.7.1)
ORA-06512: at "SYS.UTL_SMTP", line 86
ORA-06512: at "SYS.UTL_SMTP", line 141
...plus line references to my procedure...

What am I doing wrong?
0
Comment
Question by:uomobello
  • 7
  • 5
14 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 39185311
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 39185314
auth login is referred to as a "plain text" authentication because there is no encryption of the values.

they are, however,  encoded


http://www.experts-exchange.com/Database/Oracle/A_5915-Extending-Oracle%27s-Email-functionality-with-PL-SQL-Authentication.html
0
 

Author Comment

by:uomobello
ID: 39185853
Thanks to both experts...my username and password are now accepted - encoding to base-64 did the trick - but now the resulting email is not being sent.

I checked the email address I'm passing to the procedure and it's correct. Here's the code again:

      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 now?
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39185899
do you need the question reopened?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39187251
you closed with a penalty grade but at the same time followed up with a request for additional information.

what is the status here?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39187261
just as a note for future readers.

This question was stated as being for version 8i.
First, we have to recommend upgrading if possible.  8i has been desupported for a long time.

Second,  the utl_encode package referenced in the article wasn't introduced until 9i.

In it's absence you'll have to write your own encoding function.


v_username_b64 :=
        UTL_RAW.cast_to_varchar2(
            UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_username))
        );

would become...

v_username_b64 :=  sds_base64_encode(UTL_RAW.cast_to_raw(:v_username));


where sds_base64_encode is something like this...


CREATE OR REPLACE FUNCTION sds_base64_encode(p_raw IN RAW)
    RETURN VARCHAR2
IS
    c_encoding_set   CONSTANT CHAR(64)
        := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/' ;
    v_length                  PLS_INTEGER;
    v_3byte_int               PLS_INTEGER;
    v_result                  VARCHAR2(32767);
BEGIN
    v_length  := UTL_RAW.LENGTH(p_raw);

    FOR i IN 0 .. FLOOR(v_length / 3) - 1
    LOOP
        v_3byte_int  := TO_NUMBER(RAWTOHEX(UTL_RAW.SUBSTR(p_raw, (3 * i) + 1, 3)), 'xxxxxx');

        v_result      :=
               v_result
            || SUBSTR(c_encoding_set, FLOOR(v_3byte_int / 2 ** 18) + 1, 1)
            || SUBSTR(c_encoding_set, FLOOR(MOD(v_3byte_int, 2 ** 18) / 2 ** 12) + 1, 1)
            || SUBSTR(c_encoding_set, FLOOR(MOD(v_3byte_int, 2 ** 12) / 2 ** 6) + 1, 1)
            || SUBSTR(c_encoding_set, MOD(v_3byte_int, 2 ** 6) + 1, 1);
    END LOOP;

    IF MOD(v_length, 3) = 1
    THEN
        v_3byte_int      :=
            TO_NUMBER(RAWTOHEX(UTL_RAW.SUBSTR(p_raw, -1, 1)) || HEXTORAW('0000'), 'xxxxxx');

        v_result      :=
               v_result
            || SUBSTR(c_encoding_set, FLOOR(v_3byte_int / 2 ** 18) + 1, 1)
            || SUBSTR(c_encoding_set, FLOOR(MOD(v_3byte_int, 2 ** 18) / 2 ** 12) + 1, 1)
            || '==';
    ELSIF MOD(v_length, 3) = 2
    THEN
        v_3byte_int      :=
            TO_NUMBER(RAWTOHEX(UTL_RAW.SUBSTR(p_raw, -2, 2)) || HEXTORAW('00'), 'xxxxxx');

        v_result      :=
               v_result
            || SUBSTR(c_encoding_set, FLOOR(v_3byte_int / 2 ** 18) + 1, 1)
            || SUBSTR(c_encoding_set, FLOOR(MOD(v_3byte_int, 2 ** 18) / 2 ** 12) + 1, 1)
            || SUBSTR(c_encoding_set, FLOOR(MOD(v_3byte_int, 2 ** 12) / 2 ** 6) + 1, 1)
            || '=';
    END IF;

    RETURN v_result;
END;

Open in new window


based on the asker's comment that they got the email working, I assume they already had a similar encoding routine available.
0
 

Author Comment

by:uomobello
ID: 39188146
Thanks for the decoding procedure. I have yet to convince the execs here that an Oracle upgrade is worth the money. We'll be forced to sooner or later with Win OS compatibility issues.

My original question dealt with email authentication which was answered so I guess I need to open a new question because the email has not been delivered successfully yet.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39188192
can you explain why the penalty grade was awarded?
0
 

Author Comment

by:uomobello
ID: 39188223
I'm not sure what a penalty grade is. Because you and another expert gave me basically the same answer I split the 500 pts between you. I gave you a little more because you mentioned the necessity of base64 encoding even with "plain text" which helped me further along.

Should I have awarded 500 pts to each expert who gave a correct answer or do I award the points to whomever answered first?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39188274
The split isn't in question,  it's the B grade

Anything less than an A means the answers were deficient in some way and nobody was willing or able to correct the deficiencies.

Kind of like a grade in school, a "B" means you screwed something up.
When that happens, the asker is supposed to explain what the errors were or what information was missing.

Since you gave a B, you're saying we got something wrong or we gave you insufficient information to continue.  I would like to know what was wrong or missing.
0
 

Author Comment

by:uomobello
ID: 39188327
I see. Well at the time I was thinking that my problem wasn't completely resolved because the email still wasn't getting through.

However, I see now that my original question dealt with authentication and that issue WAS resolved so I'd be happy to change my grade to A for this question and post another question regarding what else is wrong with my code and preventing it from working.
0
 

Author Closing Comment

by:uomobello
ID: 39188364
I feel better....thanks again!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39188372
Thank you for returning to the question and regrading.

For your next question, or others.  If you think the problem isn't solved, then ask for more info and wait for responses before closing.  Only close when you have your answer, or if nobody is willing/able to answer.

If you come up with something like this where the question is answered but there is something different beyond the scope of the original question, then close and open a new question as you're doing now.

If you're ever not sure; simply click the "request attention" link
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

856 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