Solved

Oracle Email

Posted on 2013-05-21
14
949 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 73

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
 
LVL 73

Expert Comment

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

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 73

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 73

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 73

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 73

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

20 Experts available now in Live!

Get 1:1 Help Now