Home » SQL & PL/SQL » SQL & PL/SQL » EMail via PL/SQL
EMail via PL/SQL [message #185569] Wed, 02 August 2006 06:42 Go to next message
harishoty
Messages: 2
Registered: August 2006
Location: India
Junior Member

Hi folks,

I tried using UTL_SMTP to send simple emails. My code is as follows:-

PROCEDURE prc_send

IS

-- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_smtp_host VARCHAR2(100) DEFAULT 'smtp.server.com';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;

BEGIN

-- establish the connection to the smtp server
v_smtp_connection := utl_smtp.open_connection(v_smtp_host, v_smtp_port);

-- perform a handshake with the smtp server
utl_smtp.helo(v_smtp_connection, v_smtp_host);

-- set the 'from' address of the message
utl_smtp.mail(v_smtp_connection, 'name@company.com');

-- add the recipient to the message
utl_smtp.rcpt(v_smtp_connection, 'anothername@company.com');

-- open the message for writing
utl_smtp.open_data(v_smtp_connection);

-- populate the message with the build up message header and body
utl_smtp.write_data(v_smtp_connection,'MESSAGE HERE');

-- close the message for writing
utl_smtp.close_data(v_smtp_connection);

-- end the connection to the smtp server
utl_smtp.quit(v_smtp_connection);

END prc_send;
When I execute this, It shows authentication error. Error looks like ORA-29278: SMTP transient error: 454 5.7.3 Client was not authenticated.
I know my smtp server requires password authentication. But I am not aware of how to do it with UTL_SMTP.
Can anybody update me on this?
TIA
Harish
Re: EMail via PL/SQL [message #185572 is a reply to message #185569] Wed, 02 August 2006 06:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you tried:

conn := utl_smtp.open_connection(<smtp_server>); 
utl_smtp.ehlo(conn,<smtp_server>); 
utl_smtp.command(conn, 'AUTH LOGIN'); 
utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw(<username>))); 
utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw(<password>))); 
Re: EMail via PL/SQL [message #185674 is a reply to message #185569] Thu, 03 August 2006 00:59 Go to previous messageGo to next message
harishoty
Messages: 2
Registered: August 2006
Location: India
Junior Member

Hi,

I tried your code. Though my username and password are correct, it is showing the following error.
ORA-29279: SMTP permanent error: 535 5.7.3 Authentication unsuccessful.
What would be the problem?

TIA
Harish
Re: EMail via PL/SQL [message #361662 is a reply to message #185674] Thu, 27 November 2008 06:06 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

We have recently shifted our mail server from exchange 2003 to exchange 2007, and now it is configured in AUTHENTICATION MODE ENABLED.

It gives error at the second line where p_password parameter is involved, i checked using OWA this user is getting authenticated normally

UTL_SMTP.helo(mail_conn, mailhost);
UTL_SMTP.command(mail_conn,'AUTH LOGIN');
UTL_SMTP.command(mail_conn, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.cast_to_raw('p_username')) );
UTL_SMTP.command(mail_conn, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.cast_to_raw('p_password')) );

ORA-29279: SMTP permanent error: 535 5.7.3 Authentication unsuccessful
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 159
ORA-06512: at "LSMSTEST.SP_SEND_AUTHENTICATED_MAIL", line 44
ORA-06512: at line 1

where line 44 is the line holding p_password.

I have the same issue,
Can anybody please guide us.
Re: EMail via PL/SQL [message #361668 is a reply to message #361662] Thu, 27 November 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
535 5.7.3 Authentication unsuccessful

You didn't authenticate against SMTP server.

Regards
Michel
Re: EMail via PL/SQL [message #361673 is a reply to message #361668] Thu, 27 November 2008 06:43 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

How can i authenticate against mail server?
Please guide.
Re: EMail via PL/SQL [message #361721 is a reply to message #361673] Thu, 27 November 2008 15:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Use the correct username and password, and set up SMTP and the appropriate user rights in Exchange.

In 95% of these cases it's a setup problem on the Exchange side.

I would suggest you install a "normal" standard-SMTP (NOT Outlook) mail program on a workstation somewhere and try with that if you can send SMTP-Mail over the Exchange server with the user you want to use in the database.
Re: EMail via PL/SQL [message #397056 is a reply to message #361721] Wed, 08 April 2009 13:55 Go to previous messageGo to next message
widipa
Messages: 1
Registered: April 2009
Junior Member

Hello.
I get the following error when adding lines authentication

ERROR: -29279 ORA -29279: permanent error of SMTP: 502 Command "AUTH LOGIN" not implemented

Use oracle 10g

this is my code

UTL_SMTP.command(v_conn,'AUTH LOGIN');
UTL_SMTP.command(v_conn, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.cast_to_raw('user')) );
UTL_SMTP.command(v_conn, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.cast_to_raw('pass')) );
Re: EMail via PL/SQL [message #397060 is a reply to message #185569] Wed, 08 April 2009 14:06 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I get the following error when adding lines authentication

>ERROR: -29279 ORA -29279: permanent error of SMTP: 502 Command "AUTH LOGIN" not implemented

The mailserver is not configured to accept the "AUTH LOGIN" command.
This is an issue between you the the postmaster who controls & manages the mailsever you are attempting to connect to.

Simply put, this is NOT an Oracle issue.
Re: EMail via PL/SQL [message #522878 is a reply to message #185569] Wed, 14 September 2011 05:48 Go to previous message
gdaffre
Messages: 1
Registered: September 2011
Junior Member
Hi

I had the same error.
I found out, that the output of the encoding was wrong. Or not as i expected...

I then used the function text_encode, instead of base64encode:

utl_encode.text_encode('username','WE8ISO8859P1',UTL_ENCODE.BASE64);


Or with the code above:

conn := utl_smtp.open_connection(<smtp_server>); 
utl_smtp.ehlo(conn,<smtp_server>); 
utl_smtp.command(conn, 'AUTH LOGIN'); 
utl_smtp.command(conn, utl_encode.text_encode(<username>,'WE8ISO8859P1',UTL_ENCODE.BASE64)); 
utl_smtp.command(conn, utl_encode.text_encode(<password>,'WE8ISO8859P1',UTL_ENCODE.BASE64)); 


For me this did work. Maybe for others too..

Previous Topic: arithmetric operations on tabular data having null values
Next Topic: Issues with loading data through Oracle External Table
Goto Forum:
  


Current Time: Sat Dec 03 12:24:28 CST 2016

Total time taken to generate the page: 0.20609 seconds