Database Software

Hi
I am working on the following oracle database version

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

To send email, I have created the following procedure (I got the code from this forum only).
Untill few days back, it was working appropriately, but now I am getting the follwoing errors when I am executing the procedure.
Could you please tell me how to fix this?

when I log into the solaris server and executing the following command, it's working fine. That means the SMTP server is properly configured in the server.

bash-3.00$ /usr/sbin/sendmail 'rina@abc.com'
mail from me ^D
hello
bash-3.00$




SQL> BEGIN
2 mail_files( 'localhost',
3 'from_name',
4 'rina@abc.com',
5 'subject',
6 'message',
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 "CLTY_API.MAIL_FILES", line 238
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 2


----------------------------------------------------------------------------------------------------


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)

eg.

mail_files( from_name => 'oracle' ,
to_name => 'someone@somewhere.com' ,
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 ...
--


--------------------------------------------------------------------------------
conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );

-- 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 );

-- Append the files ...
--
--------------------------------------------------------------------------------

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);

END LOOP;

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);

END IF;

END LOOP;

-- 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 );

EXCEPTION

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;
/