Solved

Oracle Email

Posted on 2013-05-21
14
977 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

778 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