smtp auth login
Jackie, September 23, 2003 - 7:22 am UTC
Also I want to know whethere there is a function in utl_smtp to authentic the related mail account and password?
if there is, can you give me a example?
Tks a lot!
September 23, 2003 - 7:29 am UTC
you would have to look up the SMTP protocol and either use the utl_smtp command call or just "write_data" to talk to smtp directly.
I don't have an smtp server that requires authentication, so I cannot test.
but smtp is really "simple" as the name suggests. you can use TELNET to talk to your server. for example, in the following -- the bold is what I typed in:
[tkyte@localhost tkyte]$ telnet aria.us.oracle.com 25
Trying 138.2.5.51...
Connected to aria.us.oracle.com.
Escape character is '^]'.
220 aria.us.oracle.com ESMTP Sendmail 8.10.2+Sun/8.10.2; Tue, 23 Sep 2003 07:32:51 -0400 (EDT)
HELO aria
250 aria.us.oracle.com Hello dhcp-amer-vpn-gw2-west-141-144-79-157.vpn.oracle.com [141.144.79.157], pleased to meet you
HELP
214-2.0.0 This is sendmail version 8.10.2+Sun
214-2.0.0 Topics:
214-2.0.0 HELO EHLO MAIL RCPT DATA
214-2.0.0 RSET NOOP QUIT HELP VRFY
214-2.0.0 EXPN VERB ETRN DSN
214-2.0.0 For more info use "HELP <topic>".
214-2.0.0 To report bugs in the implementation contact Sun Microsystems
214-2.0.0 Technical Support.
214-2.0.0 For local information send email to Postmaster at your site.
214 2.0.0 End of HELP info
HELP EXPN
214-2.0.0 EXPN <recipient>
214-2.0.0 Expand an address. If the address indicates a mailing
214-2.0.0 list, return the contents of that list.
214 2.0.0 End of HELP info
QUIT
221 2.0.0 aria.us.oracle.com closing connection
Connection closed by foreign host.
[tkyte@localhost tkyte]$
You can telnet in and see what commands your server accepts and use the command api accordingly.
tks a lot!
Jackie, September 23, 2003 - 8:15 am UTC
Tks a lot, so I must adjust my smtp server to make it has the relay function to assure that I can send mail through oracle db to outside mailbox!
use of utl_smtp with user/password authentication
SHUBIN_DU, November 20, 2005 - 10:35 am UTC
if your server uses SASL AUTH LOGIN then you can use this calls of utl_smtp:
loc_reply := utl_smtp.open_connection( 'smtp.mysmtp.com', 25, loc_connection );
utl_smtp.ehlo( loc_connection, 'mydomain' );
utl_smtp.command( loc_connection, 'AUTH LOGIN' );
utl_smtp.command( loc_connection, 'my_user_in_BASE64' );
utl_smtp.command( loc_connection, 'my_password_in_BASE64' );
loc_recipient := 'myfriend@hissmtp.com';
loc_sender := 'me@mysmtp.com';
utl_smtp.rcpt ( loc_connection, loc_recipient );
utl_smtp.mail ( loc_connection, loc_sender );
and other smtp calls that you need
my_user_in_BASE64 is your user name encoded with BASE64
my_password_in_BASE64 is your password encoded with BASE64
you can get BASE64 encoder here: </code>
http://makcoder.sourceforge.net/demo/base64.php <code>
November 21, 2005 - 8:21 am UTC
ops$tkyte@ORA9IR2> desc utl_encode<b>
FUNCTION BASE64_DECODE RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
R RAW IN
FUNCTION BASE64_ENCODE RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
R RAW IN</b>
FUNCTION QUOTED_PRINTABLE_DECODE RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
R RAW IN
FUNCTION QUOTED_PRINTABLE_ENCODE RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
R RAW IN
FUNCTION UUDECODE RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
R RAW IN
FUNCTION UUENCODE RETURNS RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
R RAW IN
TYPE BINARY_INTEGER IN DEFAULT
FILENAME VARCHAR2 IN DEFAULT
PERMISSION VARCHAR2 IN DEFAULT
UTL_SMTP
A reader, September 13, 2006 - 7:48 am UTC
Hi Sir,
I have implemented the same procedure send_mail, I have found in your book (concerning UTL_SMTP). Unfortunately when I tried to test it I got the following error:
ORA-20001 : Client was not authenticated.
Then, I have tried to enrich send_mail procedure by commands like:
utl_smtp.helo(l_mail_conn, l_mailhost); -- in send_mail
utl_smtp.command(l_mail_conn, 'AUTH LOGIN'); -- added
utl_smtp.command(l_mail_conn, 'user'); -- added
utl_smtp.command(l_mail_conn, 'pwd'); -- added
and this time I got the following error
ORA-20002 : 503 55 2 Send hello first
Could you please help me to fix this problem
Thanks in advance
September 13, 2006 - 2:40 pm UTC
send helo first maybe??
Issue with secure connection in utl_smtp
tanweer, September 19, 2006 - 5:40 am UTC
This helpful while u r performing secure connection with ult_smtp with public smtp server
Send Helo first
A reader, December 21, 2006 - 10:50 am UTC
Dear TOM,
What do you mean by send helo first.
I have tried all things possible and still I am faced with this strange Error : ORA-20002 : 503 55 2 Send hello first
Do you know what could be the reason of this error?
Thanks a lot
December 22, 2006 - 6:00 am UTC
maybe your smtp server wants to be friendly and exchange greetings first.
see the calls to helo above?
SMTP_UTL and GMT time
Abdul, January 04, 2007 - 3:19 pm UTC
Dear Mr. Tom,
We are using following procedure to send email from a database trigger, using UTL_SMTP. It works as indented but has a small problem. When sending messages, it seems that Oracle is sending GMT time to mail server rather than time from sysdate (Server is set to ¿US Central time¿). We don't want to hardcode the time difference (sysdate +/- GMT difference) in procedure because We have to change it when daylight saving time comes in effect and vise versa.
It will be highly appreciated if you can suggest any solution to overcome this issue.
Oracle Version 9.2.0.6
O/S. Windows 2003
Mail Server: Lotus Notes 6.5
-----------------------
CREATE OR REPLACE
PROCEDURE "SEND_EMAIL" (
p_recipient IN VARCHAR2,
p_message IN VARCHAR2 default ' ',
p_subject IN VARCHAR2 default ' ')
AS
p_sender varchar2(60) := 'myaddress@our_server.com';
crlf varchar2(2) := chr(13) || chr(10);
v_mailhost varchar2(30) := 'our_mailhost.com';
mail_conn utl_smtp.connection;
msg varchar2(5000):= 'Date: ' ||
to_char (sysdate, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: ' || p_sender || '<' || p_sender || '>' || crlf ||
'Subject: ' || p_subject || crlf ||
'To: ' || p_recipient || '<' || p_recipient || '>' || crlf ||
chr(10) || p_message;
BEGIN
mail_conn := utl_smtp.open_connection(v_mailhost, 25);
utl_smtp.helo(mail_conn, V_mailhost);
utl_smtp.mail(mail_conn, p_sender);
utl_smtp.rcpt(mail_conn, p_recipient);
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn, msg);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line(dbms_utility.format_call_stack);
END;
One particular pattern is giving same issue
Bhavesh Gaur, April 08, 2009 - 4:37 am UTC
Hi Tom,
When I am using attached PL/SQL code, I am able to send the email except when my message string has the combination as shown below. If string has any other combination the mail goes off without any problem.
declare
mailhost VARCHAR2(100) := 'smtp.mail.com';
l_name VARCHAR2(100) := 'ABC';
l_email VARCHAR2(100) := 'abc@mail.com';
c_name VARCHAR2(100) := 'ABC';
c_email VARCHAR2(100) := 'abc@mail.com';
l_err_msg VARCHAR2(1000) := '';
mail_conn utl_smtp.connection;
l_crlf Varchar2(2) := CHR(13)||CHR(10);
l_start number := 1;
message varchar2(2000);
countdown number:=0;
BEGIN
message :='.'||l_crlf||'.'||l_crlf||'NCC022';
mail_conn :=utl_smtp.open_connection(mailhost,25);
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,c_email);
-- sender
utl_smtp.rcpt(mail_conn,l_email);
-- recipient
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'To: "'||l_name||'"
<'||l_email||'>'||utl_tcp.CRLF);
utl_smtp.write_data(mail_conn,'Date: '|| TO_CHAR( SYSDATE, 'dd Mon yy
hh24:mi:ss' ) ||utl_tcp.CRLF);
utl_smtp.write_data(mail_conn,'From: "'||c_name||'"
<'||c_email||'>'||utl_tcp.CRLF);
UTL_smtp.write_data(mail_conn, 'MIME-Version: ' || '1.0' ||
UTL_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'Content-Type: text/html; charset=utf-8'
|| utl_tcp.CRLF);
utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: 8bit' ||
UTL_tcp.CRLF);
utl_smtp.write_raw_data(mail_conn,
utl_raw.cast_to_raw(utl_tcp.CRLF||message||utl_tcp.CRLF) );
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error
THEN
utl_smtp.quit(mail_conn);
NRIUTIL.ERROR_LOG ('NRIUTIL-012b', 'anbu', '', sqlerrm);
WHEN OTHERS THEN
NRIUTIL.ERROR_LOG ('NRIUTIL-013', 'anbu', '', sqlerrm);
END;
If message has '.'||l_crlf||'.'||l_crlf combination I get following error :
ERROR at line 1:
ORA-20001: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 356
ORA-06512: at line 58
Any idea why this is so bizzare ? Thanking you in advance.
Regards
April 13, 2009 - 1:21 pm UTC
WHEN OTHERS THEN
NRIUTIL.ERROR_LOG ('NRIUTIL-013', 'anbu', '', sqlerrm);
http://asktom.oracle.com/pls/ask/search?p_string=%22i+hate+your+code%22 I hate your code
(but the smtp spec says "." on a line by itself is sufficient to terminate the conversation. look at this telnet example, for example
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2118740358226 and try sending your email using telnet directly with the .\r\n.\r\n and see what happens....
[tkyte@dellpe ~]$ <b>telnet localhost 25</b>
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 dellpe.localdomain ESMTP Sendmail 8.13.1/8.13.1; Mon, 13 Apr 2009 13:05:18 -0400<b>
HELO localhost</b>
250 dellpe.localdomain Hello localhost.localdomain [127.0.0.1], pleased to meet you<b>
MAIL FROM: tkyte@localhost</b>
250 2.1.0 tkyte@localhost... Sender ok<b>
RCPT TO: tkyte@localhost</b>
250 2.1.5 tkyte@localhost... Recipient ok<b>
DATA</b>
354 Enter mail, end with "." on a line by itself
hello tom
.
250 2.0.0 n3DH5Iw8023610 Message accepted for delivery
.
500 5.5.1 Command unrecognized: "."
See how after DATA - it (smtp) says back to us "end with a '.'"
that is SMTP - not UTL_SMTP. You need to send standard emails conforming to the rules that SMTP imposes.
Error ora-29278
andry, September 03, 2016 - 12:31 pm UTC
when I run following snippets return " ora-29278 SMTP transient error : 421 Service not available ".
declare
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection('smtp@silverdb.net', 465);
UTL_SMTP.helo(l_mail_conn, 'smtp@silverdb.net');
UTL_SMTP.mail(l_mail_conn, 'andry@silverdb.net');
UTL_SMTP.rcpt(l_mail_conn, 'smith@silverdb.net');
UTL_SMTP.data(l_mail_conn, 'msg' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
Error:-
ora-29278 SMTP transient error : 421 Service not available
September 04, 2016 - 1:20 pm UTC
"421 Service not available" is a OS level error (google it - you'll see heaps of examples). Basically either something at the network layer or software layer blocked or killed your access to your mail server.
Error compilation
A reader, October 17, 2019 - 1:29 pm UTC
Hello, i have error in compliation code
October 21, 2019 - 6:25 am UTC
OK
Unable to send email from 18xe PDB
Srinivas, April 21, 2023 - 4:08 pm UTC
1) Connected to DB
2) Changed session to XEPDB1
3) Created ACLs like below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'APPUSER', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APPUSER',TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APPUSER',TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml','<Server IP>',587);
4) Code snippet
BEGIN
execute immediate 'alter session set smtp_out_server=''smtp.office365.com:587''';
UTL_MAIL.SEND(sender=>'srinivas.p@company.com',
recipients=>'srinivas.p@company.com',
subject=>'Test Mail',
message=>'This is just a test mail');
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error:'||sqlerrm);
END;
/
May 01, 2023 - 3:24 pm UTC
You might have missed out some critical piece of information for us ....