UTL_SMTP PACKAGE - ORA-20001-421 SERVICE NOT AVAILABLE

Hi

When I try to send mails from DB using UTL_SMTP package, I am getting the follwoing error:

Declare
*
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 121
ORA-06512: at line 12

Can any one had this kind of problem before, please help me to resolve this problem.

Thanks in advance.

Regards
Nagesh

Do you have Java installed in the DB?

-amar

R U using the correct email server name in the UTL_SMTP.OPEN_CONNECTION() call.

Ensure that there is SMTP server and the process is up and
running.

Hi,

I apologize for late reply. Yes, Java is loaded. and Server is up and running.

Nagesh.

Quote:

---

Originally posted by gnagesh
Hi,

I apologize for late reply. Yes, Java is loaded. and Server is up and running.

Nagesh.

---

Did you run something like:

loadjava -force -verbose -user sys/password@DB c:\ora817\plsql\jlib\plsql.jar

Is there an SMTP server and is the process up and running?

Hi Julian

Thanks for your attention. Now it is been solved. I had wrongly mentioned the IP address of mail server. Now it is working. Thank you once again.

One more query. How can I send oracle error messages to my mail id, how can i automate it. like whenever oracle writes a messages to alert files/log/trace files, It should be mailed to dba mail id's.

Thanks once again.

Regards
Nagesh

You will have to schedule a job which reads, say, the alert file(s) (for example) ones in an hour. You read files in Oracle with UTL_FILE.

Here is an example from Metalink on how to use UTL_FILE for reading.

CREATE OR REPLACE PROCEDURE read_file AS

id UTL_FILE.FILE_TYPE;
name VARCHAR2(20) := 'my_file';
err VARCHAR2(100);
num NUMBER;
filedata VARCHAR2(2000);

BEGIN
id := UTL_FILE.FOPEN('/u05/home/tsupport/asoracco/utl_file',name,'r');

LOOP
BEGIN
UTL_FILE.GET_LINE(id,filedata);
DBMS_OUTPUT.PUT_LINE(filedata);

EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
END LOOP;

UTL_FILE.FCLOSE(id);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(ID);
WHEN OTHERS THEN
err := SQLERRM;
num := SQLCODE;
DBMS_OUTPUT.PUT_LINE(err);
DBMS_OUTPUT.PUT_LINE(num);
DBMS_OUTPUT.PUT_LINE('Error trying to read file');
END;
/

Thank You very much. I will try and let you know.

Thanks
Nagesh