EMail via PL/SQL [message #185569] |
Wed, 02 August 2006 06:42 |
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 |
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 #361662 is a reply to message #185674] |
Thu, 27 November 2008 06:06 |
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 #361721 is a reply to message #361673] |
Thu, 27 November 2008 15:29 |
ThomasG
Messages: 3211 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 |
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 #522878 is a reply to message #185569] |
Wed, 14 September 2011 05:48 |
|
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..
|
|
|