Ora 29278 smtp transient error 421 service not available

Error: Ora-29278: Smtp Transient Error: 421 Service Not Available Ccg Integra: Alerts Test (Doc ID 1166196.1)

Last updated on JANUARY 30, 2022

Applies to:

Symptoms

On : 5.5.1 version, Install, Migration, Upgrade (installed in 11G)

When attempting to configure email server, the following error occurs :

ERROR
————————
Error: ORA-29278: SMTP transient error: 421 Service not available ORA-06512: at «SYS.UTL_SMTP», line 20 ORA-06512: at «SYS.UTL_SMTP», line 96 ORA-06512: at «SYS.UTL_SMTP», line 402 ORA-06512: at line 1 ORA-24247: network access denied by access control list (ACL).Possible cause: Please check your Alert settings. If the problem persists, contact Support.

STEPS
————————
The issue can be reproduced at will with the following steps:
1. Install ccg551 in 11g
2. Administrator > home configuration.
3. Test email

BUSINESS IMPACT
————————
The issue has the following business impact:
Due to this issue, users cannot receive notifications for any jobs.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-29278: SMTP transient error: 421 Service not available

OS Version:- SunOS 5.9
Database Version:- 9.2.0.6.0

I am trying to send a mail from the oracle database .
Below is the very basic procedure coded:-

CREATE OR REPLACE PROCEDURE «SYS».»SEND_MAIL_TEST» (p_sender IN VARCHAR2
,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := ‘smtp. .com’;
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn, p_message);
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;

This proc gets compiled without any error, but when execute this proc , i face
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at «SYS.UTL_SMTP», line 17

I tested following on server to ensure smtp is working on the server properly..

cat testmail | /usr/lib/sendmail -bm -t -v

This successfully sends the text in the testmail file.

ps -ef|grep «[m]ail»
root 342 1 0 Aug 12 ? 0:04 /usr/lib/sendmail -bd -q15m
smmsp 339 1 0 Aug 12 ? 0:01 /usr/lib/sendmail -Ac -q15m

what can be the issue for ORA-29278: SMTP transient error: 421 Service not available
?

Источник

How can I solve ORA-29278: SMTP transient error: 421 Service not available

I have two different Solaris Server (Server1 & server2).
In both the server SMTP server is configured.
In these two solaris server (Server1 & server2) we have installed Oracle 9i
I am using client machine to execute the following procedure.
When I connect to server1 (using SQL plus) and execute the following procedure, it works fine, and able to send emails properly.
But when I connect to server2 (using SQL plus) and execute the following procedure, I get the follwoing error.
Could you please help me to resolve this?

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 — 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 — Production

SQL> set define off
SQL> set serveroutput on size 1000000
SQL> BEGIN
2 mail_files( ‘localhost’,
3 ‘Frm’,
4 ‘[email protected]’,
5 ‘From production’,
6 ‘Test message from production’,
7 9999999999,
8 NULL,
9 NULL,
10 NULL,
11 0 );
12 END;
13 /
BEGIN
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at «SYS.UTL_SMTP», line 17
ORA-06512: at «SYS.UTL_SMTP», line 96
ORA-06512: at «SYS.UTL_SMTP», line 327
ORA-06512: at «PROD_L.MAIL_FILES», line 238
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 2

Answers

But when I log in both solaris servers (server1 & server2), and send email using mailx or sendmail, I am able to send emails from both the solaris servers (server1 & server2). That means SMTP server is configured properly in both the solaris servers (server1 & server2).

could you please suggest me any solution for this?

No response is possible as you don’t provide any relevant information
— mail_files is not an Oracle provided procedure
— no one can see how utl_smtp is called
— you don’t outline the differences between server1 and server2.
You probably even didn’t look at them
(In my experience 99.9 percent of all people posting here don’t do any thing to resolve their own problem)
With this lack of detail you would better go to a forum of fortunetellers.

—————
Sybrand Bakker
Senikor Oracle DBA

server 1 is
bash-3.00$ uname -a
SunOS FTRPB11 5.10 Generic_127111-03 sun4v sparc SUNW,Sun-Blade-T6300
bash-3.00$

Server 2 is
bash-3.00$ uname -a
SunOS FTRPB12 5.10 Generic_127111-11 sun4v sparc SUNW,Sun-Blade-T6320
bash-3.00$

I am not a UNIX guy, so if you need some more information about the UNIX setup, please tell me (it would be beneficial for me if you provide me the UNIX command)

My UNIX administrator has installed SMTP server in both the server

I am able to send email by executing UNIX commands mailx and sendmail from both the servers.

Buy by using the following procedure (I am adding the code in the last) I am able to send email from server1, but I am not able to send email from server2

BEGIN
mail_files( ‘localhost’,
‘l_from_name’,
‘[email protected]’,
‘subject’,
‘l_message’,
9999999999,
NULL,
NULL,
NULL,
0);
END;

Both the servers are solaris server. Oracle 9 i has been installed in both ther server

server1:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 — 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 — Production

server2:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 — 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 — Production

code of mail_files procedure:

CREATE OR REPLACE PROCEDURE mail_files( pSmtp_server VARCHAR2,
pfrom_name VARCHAR2,
pto_name VARCHAR2,
pSubject VARCHAR2,
pMessage VARCHAR2,
pMax_size NUMBER DEFAULT 9999999999,
pFilename1 VARCHAR2 DEFAULT NULL,
pFilename2 VARCHAR2 DEFAULT NULL,
pFilename3 VARCHAR2 DEFAULT NULL,
DEBUG NUMBER DEFAULT 0 ) IS
/*
This procedure uses the UTL_SMTP package to send an email message.
Up to three file names may be specified as attachments.
uuuu
Parameters are:

1) from_name (varchar2)
2) to_name (varchar2)
3) subject (varchar2)
4) message (varchar2)
5) max_size (number)
5) filename1 (varchar2)
6) filename2 (varchar2)
7) filename3 (varchar2)

mail_files( from_name => ‘oracle’ ,
to_name => ‘[email protected]’ ,
subject => ‘A test’,
message => ‘A test message’,
filename1 => ‘/data/oracle/dave_test1.txt’,
filename2 => ‘/data/oracle/dave_test2.txt’);

Most of the parameters are self-explanatory. «message» is a varchar2
parameter, up to 32767 bytes long which contains the text of the message
to be placed in the main body of the email.

filename <1,2,3>are the names of the files to be attached to the email.
The full pathname of each file must be specified. The files must exist
in one of the directories specified in the init.ora parameter
UTL_FILE_DIR. All filename parameters are optional: It is not necessary
to specify unused file parameters (eg. filename3 is missing in the above
example).

The max_size parameter enables you to place a constraint on the maximum
size of message, including all attachments, that the procedure will send.
If this limit is exceeded, the procedure will truncate the message at
that point with a ‘*** truncated ***’ message. The default is effectively
unlimited. However, the text of message body is still limited to 32Kb, as
it is passed in as a varchar2.

— v_smtp_server VARCHAR2(20) := ‘localhost’;
v_smtp_server VARCHAR2(32);
v_smtp_server_port NUMBER := ’25’;—cPort;
v_SentBy VARCHAR2(100);
v_directory_name VARCHAR2(100);
v_file_name VARCHAR2(100);
v_line VARCHAR2(1000);
crlf VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(32767);
conn UTL_SMTP.CONNECTION;

TYPE varchar2_table IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;

file_array varchar2_table;
i BINARY_INTEGER;
v_file_handle utl_file.file_type;
v_slash_pos NUMBER;
mesg_len NUMBER;
mesg_too_long EXCEPTION;
invalid_path EXCEPTION;
mesg_length_exceeded BOOLEAN := FALSE;
vcMensajeError VARCHAR2(1000);

l_a VARCHAR2(4000);
BEGIN

—Sino se tienen los valores del parametro usa los default del paquete
IF pSmtp_server IS NULL THEN
v_smtp_server := pSmtp_server;—cEmailServer;
ELSE
v_smtp_server := pSmtp_server;
END IF;

IF pfrom_name IS NULL THEN
v_SentBy := pfrom_name;—cSentBy;
ELSE
v_SentBy := pfrom_name;
END IF;

— first load the three filenames into an array for easier handling later .

file_array(1) := pfilename1;
file_array(2) := pfilename2;
file_array(3) := pfilename3;

— Open the SMTP connection .
—

— Initial handshaking .
—
———————————————————————————
utl_smtp.helo( conn, v_smtp_server );
utl_smtp.mail( conn, v_SentBy );
utl_smtp.rcpt( conn, pto_name );
utl_smtp.open_data ( conn );

— build the start of the mail message .
—
———————————————————————————
mesg:= ‘Date: ‘ || TO_CHAR( SYSDATE, ‘dd Mon yy hh24:mi:ss’ ) || crlf ||
‘From: ‘ || pfrom_name || crlf ||
‘Subject: ‘ || psubject || crlf ||
‘To: ‘ || pto_name || crlf ||
‘Mime-Version: 1.0’ || crlf ||
‘Content-Type: multipart/mixed; boundary=»DMW.Boundary.605592468″‘ || crlf ||
» || crlf ||
‘This is a Mime message, which your current mail reader may not’ || crlf ||
‘understand. Parts of the message will appear as text. If the remainder’ || crlf ||
‘appears as random characters in the message body, instead of as’ || crlf ||
‘attachments, then you»ll have to extract these parts and decode them’ || crlf ||
‘manually.’ || crlf ||
» || crlf ||
‘—DMW.Boundary.605592468’ || crlf ||
‘Content-Type: text/plain; name=»message.txt»; charset=US-ASCII’ || crlf ||
‘Content-Disposition: inline; filename=»message.txt»‘ || crlf ||
‘Content-Transfer-Encoding: 7bit’ || crlf ||
» || crlf ||
pmessage || crlf ;

mesg_len := LENGTH(mesg);
IF mesg_len > pmax_size THEN
mesg_length_exceeded := TRUE;
END IF;

utl_smtp.write_data ( conn, mesg );

FOR i IN 1..3 LOOP
— Exit if message length already exceeded .
EXIT WHEN mesg_length_exceeded;
— If the filename has been supplied .
IF file_array(i) IS NOT NULL THEN
BEGIN
— locate the final ‘/’ or ‘’ in the pathname .
v_slash_pos := INSTR(file_array(i), ‘/’, -1 );

IF v_slash_pos = 0 THEN
v_slash_pos := INSTR(file_array(i), ‘’, -1 );
END IF;

— separate the filename from the directory name .
v_directory_name := SUBSTR(file_array(i), 1, v_slash_pos — 1 );
v_file_name := SUBSTR(file_array(i), v_slash_pos + 1 );

dbms_output.put_line(‘directory name is’||v_directory_name);
dbms_output.put_line(‘File name is’||v_file_name);

— open the file .
v_file_handle := utl_file.fopen(v_directory_name, v_file_name, ‘r’ );

—utl_file.get_line(v_file_handle, v_line);
—dbms_output.put_line(‘line is’);

— generate the MIME boundary line .
mesg := crlf || ‘—DMW.Boundary.605592468’ || crlf ||
‘Content-Type: application/octet-stream; name=»‘ || v_file_name || ‘»‘ || crlf ||
‘Content-Disposition: attachment; filename=»‘ || v_file_name || ‘»‘ || crlf ||
‘Content-Transfer-Encoding: 7bit’ || crlf || crlf ;

mesg_len := mesg_len + LENGTH(mesg);
utl_smtp.write_data ( conn, mesg );

— and append the file contents to the end of the message .
LOOP
utl_file.get_line(v_file_handle, v_line);
IF mesg_len + LENGTH(v_line) > pmax_size THEN
mesg := ‘*** truncated ***’ || crlf;
utl_smtp.write_data ( conn, mesg );
mesg_length_exceeded := TRUE;
RAISE mesg_too_long;
END IF;

mesg := v_line || crlf;
utl_smtp.write_data ( conn, mesg );
mesg_len := mesg_len + LENGTH(mesg);

EXCEPTION
WHEN utl_file.invalid_path THEN
vcMensajeError := ‘SFT_K_BLOB.MAIL_FILES-> INVALID_PATH->’ || SQLERRM;
RAISE_APPLICATION_ERROR(-20000, vcMensajeError);
WHEN NO_DATA_FOUND THEN
NULL; —LLEGA AL FINAL DEL ARCHIVO
— All other exceptions are ignored .
—WHEN OTHERS THEN
— dbms_output.put_line(‘WHEN OTHERS->’ || SqlErrm );
— NULL;
END;

mesg := crlf;
utl_smtp.write_data ( conn, mesg );
— close the file .
utl_file.fclose(v_file_handle);

— append the final boundary line .

mesg := crlf || ‘—DMW.Boundary.605592468—‘ || crlf;
utl_smtp.write_data ( conn, mesg );

— and close the SMTP connection .

utl_smtp.close_data( conn );
utl_smtp.quit( conn );

WHEN OTHERS THEN
vcMensajeError := ‘SFT_K_BLOB.MAIL_FILES-> Error Desconocido->’ || SQLERRM;
utl_smtp.RSET(conn);
utl_smtp.quit(conn);

RAISE_APPLICATION_ERROR(-20000, vcMensajeError);
END mail_files;
/

Источник

Troubleshot ORA-29278: SMTP transient error: 421 Service not available.

Using utl_smtp:

The obselete utl_smtppackage was first introduced in Oracle 8i to give access to the SMTP protocol from PL/SQL.  The package is dependent on the JServer option  which can be loaded using the Database Configuration Assistant (DBCA) or by running the following scripts as the sys user if it is not already present.

Using the package to send an email requires some knowledge of the SMTP protocol, but for the purpose of this text, a simple send_mail procedure has been written that should be suitable for most error reporting.

Assume: 
>> My SMTP server IP is : 170.1.1.1
>> We will use ALERT user to send test mails.

— To create alert user:
SQL> create user ALERT identified by xwdkjdlc13ns default tablespace USERS temporary tablespace TEMP profile DEFAULT;
SQL> grant CONNECT to ALERT;
SQL> grant execute on UTL_MAIL to ALERT;
SQL> grant MGMT_USER to ALERT;
SQL> grant SELECT_CATALOG_ROLE to ALERT;

— Necessary configuration:
— in spfile set

SQL> alter system set smtp_out_server = ‘170.1.1.1’ scope=spfile;

Now, bounce the database;

— before set

SQL> show parameter smtp;
NAME                TYPE        VALUE
——————- ———— ——————————
smtp_out_server     string
SQL>

— after set
SQL> show parameter smtp;
NAME                TYPE        VALUE
——————- ———— ——————————
smtp_out_server     string      170.1.1.1
SQL>

— give grants

SQL> grant execute on UTL_MAIL to public;
OR
SQL> grant execute on UTL_MAIL to ALERT;
SQL> ALTER SESSION SET smtp_out_server = ‘170.1.1.1’;
SQL> exec UTL_MAIL.send(sender => ‘oracle.com’, recipients => ‘gourang_m@gmail.com’, subject => ‘Test Mail’, message => ‘Hello World’, mime_type => ‘text; charset=us-ascii’);

If you are facing «ORA-29278: SMTP transient error: 421 Service not available» error, the read the below steps.

The Problem:

You are trying to use the UTL_MAIL package to send email from your database. When attempting to send a mail, you recieve the following error:

SQL> exec utl_mail.send(‘oracle.com’,’gourang_m@gmail.com’,’test mail’,’Hello World’,mime_type => ‘text; charset=us-ascii’);


ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at «SYS.UTL_MAIL», line 654
ORA-06512: at «SYS.UTL_MAIL», line 671
ORA-06512: at line 2

Troubleshoot methods:

The Cause:

If this fails it could be a result of many things, so please check all of the items below:

1) SMTP_OUT_SERVER
Check the value for the initialisation parameter SMTP_OUT_SERVER. This should be set to the SMTP server IP. If not, run:

ALTER SYSTEM SET SMTP_OUT_SERVER=”<IP>” scope=both;

2) Recent Upgrade to Oracle 11g
Are you running Oracle database 11g? If so, you will need to have XMLDB & Java installed in order to configure fine grained auditing and enable it there for each user explicitly. You can run through these checks to confirm you have everything in place:

col COMP_NAME format a40;
col VERSION format a12;
col STATUS format a12;
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;


COMP_NAME                                VERSION         STATUS
—————————————- ————— ———
Oracle Enterprise Manager                11.2.0.3.0      VALID
Oracle XML Database                      11.2.0.3.0      VALID
Oracle Expression Filter                 11.2.0.3.0      VALID
Oracle Rules Manager                     11.2.0.3.0      VALID
Oracle Workspace Manager                 11.2.0.3.0      VALID
Oracle Database Catalog Views            11.2.0.3.0      VALID
Oracle Database Packages and Types       11.2.0.3.0      VALID
JServer JAVA Virtual Machine             11.2.0.3.0      VALID
Oracle XDK                               11.2.0.3.0      VALID
Oracle Database Java Packages            11.2.0.3.0      VALID


10 rows selected.

If the “JServer JAVA Virtual Machine” and “Oracle XML Database” components are not there then you need to install them to get this working.

The following scripts are what you would use to install Java and XML DB.

Note: These instructions are taken directly from Oracle Metalink, but I would recommend double checking the notes on there just to make sure there have been no updates to them since I wrote this.

3. UTL_MAIL Package & Grants:

You will need to ensure that the UTL_MAIL package exists and that the required users have permission to execute it. You can install it and grant execute privileges on it to a user with the following commands:

SQL/> connect as sysdba
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO USER;

e.g.,
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;

When executed:

SQL> @?/rdbms/admin/utlmail.sql
Package created.
Synonym created.


SQL> @?/rdbms/admin/prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.


No errors.
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;
Grant succeeded.

4. Access Control List (ACL) Configuration:

Have you configured your Access Control List (ACL)? If not, you can do it with the code below. This is required for any Oracle 11g database where you want to send email using Access Control Lists (ACLs) and is a very common error to encounter after upgrading your database from 10g to 11g.

SQL>
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => ‘UTL_SMTP.xml’,
    description => ‘Granting privs to required users for UTL_SMTP.xml’,
    principal => ‘ALERT’,
    is_grant => TRUE,
    privilege => ‘connect’);


  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl => ‘UTL_SMTP.xml’,
    principal => ‘ALERT’,
    is_grant => TRUE,
    privilege => ‘resolve’);


  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl => ‘UTL_SMTP.xml’,
    host => ‘170.1.1.1’);
END;
/


COMMIT;


SQL>
PL/SQL procedure successfully completed


Commit complete
SQL>

Now I am testing the above:

SQL> exec utl_mail.send(‘oracle.com’,’gourang_m@gmail.com’,’test mail’,’Hello World’,mime_type => ‘text; charset=us-ascii’);

PL/SQL procedure successfully completed
SQL>
Now it is succeeded.

Hopefully that has worked for you, but it may not…You might now be be getting a different error reporting ORA-24247: network access denied by access control list (ACL) if you have not configured your ACLs for the user running the package. if that’s the case, check out that article. It also covers the error whereby you have refreshed your environment from your production environment and receive the ORA-24247: network access denied by access control list (ACL).

Hopefully one of the suggestions above should help you to resolve the issue.

Note:
Limitations on sending e-mail in Oracle with utl_mail. There are several limitations in utl_mail for sending e-mail messages from inside Oracle. The utl_mail package can only handle a RAW datatype, and hence a maximum value of 32k for a 32k mail message.

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

2. Check SMTP access of database Linux server on mail server

– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

$ telnet smtp_server 25

If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:

helo
mail from: my_email@my_company.com # you should see "Sender OK'
rcpt to: my_email@my_company.com   # you should see "Recipient OK"
data                               # Start mail input
test email via SMTP and orcl DB
[Enter]
.                                  # mail should be sent
[Enter]
quit

3. Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example:

Please allow SMTP traffic at smtpgate.mycompany.com for the following new server:
Hostname: ..com
IP: 99.999.99.99
Function: Oracle production DB …
Outgoing email: my_app@my_company.com
Amount emails per month: around 50-100

After that your mail admins will add a sender IP address to a corporate SMTP mail server to allow the sender Linux server access the SMTP server. This will enable the SMTP server sending emails on behalf of the sender (your Oracle database Linux box).

Note that the mail server can check if the sender email exists. If not, you can send an email from a dummy email account.

4. Sending email from Oracle database via SMTP

-- Place twice in the below procedure your actual smtp_server name
create or replace procedure testmail
(fromm varchar2,too varchar2,sub varchar2,body varchar2,port number)
is
objConnection utl_smtp.connection;
vrData varchar2(32000);
BEGIN
objConnection := UTL_smtp.open_connection('<smtp_server>',port);
UTL_smtp.helo(objConnection, '<smtp_server>');
UTL_smtp.mail(objConnection, fromm);
UTL_smtp.rcpt(objConnection, too);
UTL_smtp.open_data(objConnection);
/* ** Sending the header information */
UTL_smtp.write_data(objConnection, 'From: '||fromm || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'To: '||too || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'Subject: ' || sub || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'Content-Type: ' || 'text/html;');
UTL_smtp.write_data(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' ||
UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<span style="color: red; font-family: Courier New;">'||body||'</span>');
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');
UTL_smtp.close_data(objConnection);
UTL_smtp.quit(objConnection);
EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
END testmail;
/

-- this should send an email from a dummy account to your email address
execute testmail ('dummy@my_company.com','reciever_email@my_company.com','test Subject','Test Body Text',25);

– In case of following ORA- error go to step 2 and 3:

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 21
ORA-06512: at “SYS.UTL_SMTP”, line 97
ORA-06512: at “SYS.UTL_SMTP”, line 139

The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem
is not with the UTL_SMTP package but the fact that your database server’s network configuration does
not allow it to contact an external SMTP server.

– In case a following Oracle error, go to next step and enable ACL in Oracle 11g database:

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 54
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_SMTP”, line 699
ORA-06512: at “me.TESTMAIL”, line 35
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

5. Enable access control list (ACL) for your network hosts on 11g+

The network utility family of PL/SQL packages, such as UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to make network callouts from the database using raw TCP or using higher-level protocols built on raw TCP. As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB. Below I show major steps that are required to enable ACL for user SCOTT. You have to run them as SYS or SYSDBA user. Read Oracle documentation for more details.

Create an ACL with one database user in Oracle database

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'smtp-gate-permissions.xml',
description => 'Permissions for smtp gate',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect'
);
COMMIT;
END;
/

Assign an ACL to one or more network hosts

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'smtp-gate-permissions.xml',
host => '<smtp_server>',
lower_port => 25,
upper_port => null);
COMMIT;
END;
/

Confirm the created ACL is correct

SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls
/

SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
/

So that was my action plan for providing your applications an ability to send email via SMTP protocol from an Oracle database Linux server using a real example with UTL_SMTP package including ORA- error handling and ACL creation on Oracle 11g.

Enjoyed this article? Please share it with others using the social site of your choice:

Tip of the week: When trying to send email using UTL_SMTP in RedHat Linux Server getting following error

ORA-29278: SMTP transient error: 421 Service not available

The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem is due to database server’s network configuration not allowing it to contact an external server.

Solution:

We found sendmail to use a smart host was not setup caused the error. Below steps should give you the needed  details to setup and as well as check the configuration.

In your /etc/mail/sendmail.mc file there should be a line as follows:

dnl define(`SMART_HOST’,`smtp.your.provider’)

Remove the “dnl” section and replace “smtp.your.provider” with the fully qualified domain name of the smart host you wish to use so that the line reads:

dnl define(`SMART_HOST’,`smtp.your.provider’)

Generate a new sendmail.cf file by running

# cd /etc/mail

# m4 sendmail.mc > sendmail.cf

This will generate the .cf upon restarting the sendmail by

# service sendmail restart

However you can manually do this with the following command: make -C /etc/mail

If you still experience the error, perform the following diagnostics steps.

1. The first step is to send a message from the server you made the changes on.

[root@rhbox mail]# echo test1|mail -s test1 sunthar@oracletechnologistblog.com

2. The next step can be done on the server.

Once the message is sent you will want to tail or grep /var/log/maillog for the relevant email that was just sent.

Nov 11 09:51:14 aloha sendmail[31900]: pABEpEiF031898: to=<sunthar@oracletechnologistblog.com>, ctladdr=<root@dsunthar@oracletechnologistblog.com> (0/0), delay=00:00:00, xdelay=00:00:00, mailer=relay, pri=120370, relay=smtp.host.com. [10.180.1.103], dsn=4.0.0, stat=Deferred: Connection refused by smtp.host.com.

3. From the error message, its imperative that you need to open up the firewall to allow smtp.

Once thats resolved, you should be good to go.

Useful MOS note.

ORA-29278: SMTP transient error: 421 Service not available” When Using UTL_SMTP to Send Email [ID 604763.1]