Thanks for the question, Mudasser.
Asked: January 23, 2020 - 11:47 am UTC
Answered by: Chris Saxon - Last updated: February 05, 2020 - 11:02 am UTC
Category: Database Development - Version: 12.1
Viewed 100+ times
You Asked
Hi Tom,
I want to send email through PL/SQL by using utl_mail package, I have completed below steps
1. Installed scripts
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
2. granted execute to user
3. created ACL and granted access to user
4. Checked smtp server access through telnet
5. write below script
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''mysmtpserver.com:25''';
UTL_MAIL.send(sender => 'sender@mysmtpserver.com',
recipients => 'muftisb@gmail.com',
subject => 'Test Mail',
message => 'Hello World',
mime_type => 'text; charset=us-ascii');
END;
/
6. Getting below error now
ORA-29279: SMTP permanent error: 550 XXXsmtpXX: Host xxx.xxx.xxx.xxx: No unauthenticated relaying permitted
Regards,
Mudasser Mufti
and we said...
You're receiving the 550 error from the SMTP server.
No unauthenticated relaying permitted means you have to be authenticated with the server to send email. There's no option to do this in UTL_mail at the moment. So you'll have to use UTL_SMTP instead.
So you need to start the process like this:
DECLARE
c utl_smtp.connection;
r utl_smtp.replies;
username varchar2(256) := 'guest';
password varchar2(256) := 'welcome';
BEGIN
c := utl_smtp.open_connection('rpang-pc2');
utl_smtp.ehlo(c, 'us.oracle.com');
utl_smtp.command(c, 'AUTH LOGIN');
utl_smtp.command(c, demo_base64.encode(utl_raw.cast_to_raw(username)));
utl_smtp.command(c, demo_base64.encode(utl_raw.cast_to_raw(password)));Then continue to use UTL_SMTP to send the mail. MOS note 885522.1 has a complete example if you want one.